SQL Change Automation 4

Excluding objects from script verification

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.


Didn't find what you were looking for?