SQL Server Code simplification using synonyms

Problem

In our environment, we use different DB servers and these servers are linked via Linked servers for cross server transactions. We use these linked servers in our stored procedures. The server names are different in different environments and we had to change the server names manually when we promote the stored procedures to other environments. There is a chance of errors while changing the server names manually. In this article let us see how can we eliminate changing the code manually.

Solution

We started doing POC on making our day to day SQL Server deployments/fixes automated by using tool like Source control, Compare and few other build and deployment tools.

But before that, we want to make our database code uniform across environments. While comparing code in different environments, we found few procedures which are different. Some of them were actual code differences and some of them were different at linked servers only.

Our database code is tight coupled with inked servers. Linked servers are used to query the databases on different servers. These servers can be SQL Server or any other database engine.

To illustrate this, I am using sample server names. All the servers are SQL Servers and a linked server is created from server 1 to server 2 on all environments.

Development Servers:

DEV1
DEV2

QA Servers:

Production Servers:

PROD1
PROD2

Let us create a sample stored procedure which uses table Notes on Test database server at server DEV2.

CREATE PROCEDURE GetNotes

BEGIN

SET NOCOUNT ON

SELECT * FROM [DEV2].[Test].[dbo].Notes

END

Please note this is a sample stored procedure I created to illustrate the scenario.

Now I need to change the server’s names manually while deploying the stored procedures to QA or production servers.

Please refer to below stored procedure code in different environments.

CREATE PROCEDURE GetNotes

BEGIN

SET NOCOUNT ON

SELECT * FROM [QA2].[Test].[dbo].Notes

END

CREATE PROCEDURE GetNotes

BEGIN

SET NOCOUNT ON

SELECT * FROM [PROD2].[Test].[dbo].Notes

END

I can keep the server references in a table and use dynamic T-SQL to avoid changing the server names in stored procedures every time we deploy them. But I do not want to use dynamic t-sql code. While looking at other alternatives I found synonyms as the best alternative to this problem.

Synonym is a database object that used as an alternative name for another database object. The referenced object can be on same server or on remote server.

Now let us create synonyms in all environments for the table Notes. In dev environment on server DEV1, I Created a synonym with name as SYN_Notes which refers to Notes table on Test database at server DEV2.

Please refer to below sample script to create a synonym.

1 2	CREATE SYNONYM SYN_Notes FOR DEV2.Test.dbo.Notes;

Similarly, In QA environment on server QA1, I created a synonym with same name as above which refers to Notes table on Test database at server QA2. Please refer to sample code to create a synonym.

1 2	CREATE SYNONYM SYN_Notes FOR QA2.Test.dbo.Notes;

Similarly, In Production environment on server PROD1, I created a synonym with same name as above which refers to Notes table on Test database at server PROD2. Please refer to sample code to create synonym.

1 2	CREATE SYNONYM SYN_Notes FOR PROD2.Test.dbo.Notes;

We can also create synonym using SQL Server management studio. To create synonym using SQL Server management studio login to the server and Navigate to databases.

Now Navigate to SYNONYMS folder, right click and click on create synonym

Input the fields and click ok to create synonym.

Mandatory values are synonym name, Database name, schema and Object name.

If you supply the Server name the synonym will refer to object in specified server else the synonym will be created with reference to the object in local server.

Synonyms can be created without checking the existence of referenced object. They are checked at runtime and throws error if the referenced object does not exist.

After Creating the synonym with same name to base object in different environments, I will use the synonym name instead on four-part notation in stored procedure. Please refer to below code snippet.

CREATE PROCEDURE GetNotes

BEGIN

SET NOCOUNT ON

SELECT * FROM SYN_Notes

END

Now the code is uniform across environments and it is easy for us to deploy the stored procedures to other environments without changing the stored procedure code every time.

We need to make sure the synonym is created for the referenced object before we deploy the stored procedure.

Not only tables synonyms can be created for other objects as well.

We can create synonyms for the following database objects types

Function
SQL stored procedure
View
User defined table

Considerations:

Alter statement cannot be used on synonyms. We must drop and recreate them.

/****** Object: Synonym [dbo].[SYN_Notes] Script Date: 1/1/2019 3:12:07 AM ******/

DROP SYNONYM [dbo].[SYN_Notes]

/****** Object: Synonym [dbo].[SYN_Notes] Script Date: 1/1/2019 3:12:07 AM ******/

CREATE SYNONYM SYN_Notes

FOR DEV2.Test.dbo.Notes;

We cannot issue DDL statement to synonym to modify base object.

For example, like adding new column to base table. It throws an error

Msg 4909, Level 16, State 1, Line 16
Cannot alter ‘SYN_Notes’ because it is not a table.

The base table name is not exposed.

We must take care when the table location is changed like in case of server refresh or database moved to different server or any scenario that changes the location of base object. In this case we must drop the synonym and recreate them with new locations.

Using synonyms helps in comparing databases across environments. As this makes code uniform across servers, the stored procedures which uses synonyms does not come up in differences until unless if there is actual code difference.

Synonyms can be dropped without removing its references in code. So just make sure that the synonyms are not used in the code before dropping them.

Synonym can be created once and used in multiple stored procedures.

Multiple synonyms can be created with references to same object.

You can query sys.synonyms and group base_object_name to find the duplicate synonyms.

Truncate cannot be used on synonym like in case of table.

Below are the operations that can be used on synonyms.

SELECCT
INSERT
DELETE
UPDATE
EXECUTE

In this article I have explained below points on synonyms.

How to create synonym
How to drop and recreate them
How to use them in procedures
Benefits of using synonyms and Limitations of synonyms

Next Steps

Check your database code if you are using any linked servers and try synonyms instead of four-part notation to make code uniform and simple across environments.

Check the execution plans with synonyms and with four-part notation and to check if there is any impact on performance when synonyms are used.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies

Problem

Solution

Considerations:

Next Steps

Related posts: