Ranga Babu

SQL Server Code simplification using synonyms

January 4, 2019 by

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:

  • QA1
  • QA2

Production Servers:

  • PROD1
  • PROD2

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

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.

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.

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.

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.

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.

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.

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.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with a good experience in SQL server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
181 Views