Excluding objects from script verification
Published 15 August 2019
In some situations, it can be desirable to exclude certain objects or T-SQL batches from the project verification process. In particular, if your project scripts make changes to server-level objects, such as linked-servers or SQL agent jobs. Having these execute during the shadow database deployment can result in those objects being overwritten. It can also be desirable to exclude certain cross-database operations from verification, such as an UPDATE statement that selects data from another database.
This can be achieved by gating your batches with the $(IsShadowDeployment) system variable:
IF '$(IsShadowDeployment)' = 0 BEGIN PRINT N'Creating linked-server object [LinkedServerAlias] because IsShadowDeployment=0...'; EXEC sp_addlinkedserver @server=N'LinkedServerAlias' , @srvproduct=N'sql_server' , @provider=N'SQLOLEDB' , @datasrc=N'ActualServerName' END ELSE BEGIN PRINT N'Skipping linked-server object [LinkedServerAlias] creation because IsShadowDeployment=1'; END GO
Although it is technically possible to use the above method to exclude DDL statements from the verification process, we would not recommend excluding database-level objects (such as CREATE/ALTER TABLE statements) within the shadow database deployment. Doing so can affect the integrity of the script generation process, as it can lead SQL Change Automation's comparison engine to make incorrect assumptions about the current state of your schema model.