Create a synonym based on dynamic criteria
Published 23 September 2021
Notable points to remember about pre-deployment and post-deployment scripts
- Pre and post deployment scripts must be re-runnable as they will be executed as part of every deployment.
- Pre and post deployment scripts are deployed to every environment
- You need to provide your own error handling as desired in pre and post deployment scripts - the example below uses a TRY/CATCH pattern
- See the main page regarding ordering, transaction management, and more
Pre-deployment script to create a synonym based on dynamic criteria
Synonyms provide an abstraction layer which enables flexibility managing external dependencies.
More information on utilizing synonyms
For more information on this approach, see the troubleshooting article, 'Could not find server in sys.servers or linked server returned message "login timeout expired"'.
In this example, we use a pre-deployment script to create a synonym for an object external to the current database.
- We USE [$(DatabaseName)] to ensure we are working with the correct database (not the default database for the login)
- `@@SERVERNAME is used to determine the target environment
- Custom variables can be implemented to avoid hardcoding server names into the pre-deployment script itself
Pre-deployment script to enable Change Data Capture
USE [$(DatabaseName)];
GO
SET XACT_ABORT, NOCOUNT ON;
GO
BEGIN TRY
/*
For the specified @@SERVERNAME, we will configure the synonym to utilize a linked server.
In this example we assume that linked server already exists and has been configured.
*/
IF @@SERVERNAME='TheProductionServer' BEGIN
IF(SELECT COUNT(*)
FROM sys.synonyms AS s
WHERE name='ExampleSynonym' AND s.base_object_name='[SERVER\INSTANCE].[RemoteDatabase].[Schema].[Object]')=1 BEGIN
PRINT 'Synonym ExampleSynonym for [SERVER\INSTANCE].[RemoteDatabase].[Schema].[Object] exists, doing nothing';
END;
ELSE BEGIN
IF OBJECT_ID('dbo.ExampleSynonym', 'SN') IS NOT NULL BEGIN
PRINT 'Dropping synonym dbo.ExampleSynonym';
DROP SYNONYM dbo.ExampleSynonym;
END;
PRINT 'Creating synonym dbo.ExampleSynonym for [SERVER\INSTANCE].[RemoteDatabase].[Schema].[Object]';
CREATE SYNONYM dbo.ExampleSynonym
FOR [SERVER\INSTANCE].[RemoteDatabase].[Schema].[Object];
END;
END;
ELSE
/*
In this example, we point the synonym at a local database on the same instance in all other cases.
Note that we must have an external process which restores or clones those local databases, or alternatively create "stubs" of code for them.
*/
BEGIN
IF(SELECT COUNT(*)
FROM sys.synonyms AS s
WHERE name='ExampleSynonym' AND s.base_object_name='[RemoteDatabaseRestoredLocally].[Schema].[Object]')=1 BEGIN
PRINT 'Synonym dbo.ExampleSynonym for [RemoteDatabaseRestoredLocally].[Schema].[Object] exists, doing nothing';
END;
ELSE BEGIN
IF OBJECT_ID('dbo.ExampleSynonym', 'SN') IS NOT NULL BEGIN
PRINT 'Dropping synonym dbo.ExampleSynonym';
DROP SYNONYM dbo.ExampleSynonym;
END;
PRINT 'Creating synonym dbo.ExampleSynonym for [RemoteDatabaseRestoredLocally].[Schema].[Object]';
CREATE SYNONYM dbo.ExampleSynonym
FOR [RemoteDatabaseRestoredLocally].[Schema].[Object];
END;
END;
END TRY
BEGIN CATCH
DECLARE @msg NVARCHAR(2048) =N'';
IF @@trancount>0 ROLLBACK TRANSACTION;
SET @msg=ERROR_MESSAGE();
RAISERROR(@msg, 16, 1);
END CATCH;
GO
