First thing, Synonyms are not another way to link two servers together, that is bailiwick of Linked Servers.  Synonyms are database objects that can be used to reference other database objects, on remote servers or on the same server.  One of the areas where Synonyms come into their own is when they are used to abstract the four-part calls necessary when querying database objects via Linked Servers.

Syntax from Microsoft to create a Synonym for both on premise and in Azure

SQL Server Syntax

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>

 

<object> :: =

{

[ server_name.[ database_name ] . [ schema_name_2 ]. object_name

| database_name . [ schema_name_2 ].| schema_name_2. ] object_name

}

Azure SQL Database Syntax

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >

 

< object > :: =

{

[database_name. [ schema_name_2 ].| schema_name_2. ] object_name

}

 

Utilization scenario

In this instance we are going to use sample servers just to help illustrate what is occurring.  In this case all the ‘1’ servers are the source and the ‘2’ servers are the target servers.  As well the databases in use is one of the AdventureWorks databases from Microsoft.

Development Servers:

  • Development1
  • Development2

Testing Servers:

  • Testing1
  • Testing2

Production Servers:

  • Production1
  • Production2

Let us start with a few basic Stored Procedures using four-part calls, one for each environment.

Executed on Development1

CREATE PROCEDURE [dbo].[uspProduct]

AS

BEGIN

SELECT * FROM [Development2].[AdventureWorks2019].[Production].[Product]

END

GO

Executed on Testing1

CREATE PROCEDURE [dbo].[uspProduct]

AS

BEGIN

SELECT * FROM [Testing2].[AdventureWorks2019].[Production].[Product]

END

GO

Executed on Production1

CREATE PROCEDURE [dbo].[uspProduct]

AS

BEGIN

SELECT * FROM [Production2].[AdventureWorks2019].[Production].[Product]

END

GO

 

For simple code such as this, changing the four-part call is not a significant amount of work.  There is only one such call in this example, but in much larger procedures there can be dozens accessing different Tables, Views, Stored Procedures, Functions, etc.  Now we will implement a Synonym and see what the difference is on the code in the different environments.

First, we create the synonyms on the ‘1’ servers.  To create a Synonym, you will need to have CREATE SYNONYM rights on the local database.  If accessing another database server, you will need permissions on that database server as well.

 

Executed on Development1

CREATE SYNONYM [dbo].[synProduct]

FOR [Development1].[AdventureWorks2019].[Production].[Product]

GO

Executed on Testing1

CREATE SYNONYM [dbo].[synProduct]

FOR [Testing1].[AdventureWorks2019].[Production].[Product]

GO

Executed on Production1

CREATE SYNONYM [dbo].[synProduct]

FOR [Production1].[AdventureWorks2019].[Production].[Product]

GO

 

Now we can implement the code to create the stored procedures necessary on each environment.

 

To be executed on all ‘1’ servers.

CREATE PROCEDURE uspProduct

AS

BEGIN

SELECT * FROM synProduct

END

GO

 

This code will work on all of the ‘2’ servers

 

Adding some depth to Synonyms.  You can create a slightly more complicated Stored Procedure.  For this example, we are just going to use the Development servers.

 

First, we are going to create a Stored Procedure on the Development2 server.

CREATE PROCEDURE [dbo].[uspProdIDSearch]

@LowerBound INT, @UpperBound INT

AS

BEGIN

SELECT * FROM [AdventureWorks2019].[Production].[Product]

WHERE ProductID BETWEEN @LowerBound AND @UpperBound

ORDER BY Name

END

GO

 

Now back to the server Development1.  We execute the following statement:

EXECUTE [Development2].[AdventureWorks2019].[dbo].[uspProdIDSearch] 100, 350

 

Just a little wordy, it is exact but it easier to miss something when looking at code like this for a couple of hours.

 

So, let us create a Synonym to make it easier to work with.  This is executed on Development1

 

CREATE SYNONYM [dbo].[uspProdIDSearch] FOR [Development2].[AdventureWorks2019].[dbo].[uspProdIDSearch]

GO

 

To correctly use the Synonym, you must include the @LowerBound and @UpperBound variables or it will fail.  So, our execution code on Development1 would look like this:

 

EXECUTE uspProdIDSearch 100, 500

 

Summary

Benefits of using Synonyms

  • By using synonyms, you can greatly simplify your code by leveraging the abstraction of the referenced database object.
  • If there is a change to the data source, i.e., new database server only the Synonym needs to be recreated. Using the examples provided, the Stored Procedures will continue to function without any modification to them.  Without using a Synonym all the code that accesses the original database would have to be found and altered and that could take days.

Limitations of using Synonyms

  • Synonyms cannot be updated or modified. If changes are necessary, then it will have to be dropped and recreated.
  • The naming of a Synonym does use an object name; therefore, you cannot have two objects with the same name. For example, using synProduct for the Synonym you cannot create another database object with the same name.
  • Any reference errors will not be thrown until the Synonym is used. For example, [Production2].[AdventureWorks2019].[Production].[Products] does not exist but that will not apparent until the Synonym tries to access it.

 

Written By:

Robert Koros

Robert is a Senior Database Consultant with a track record of success as a Senior Database Administrator / Architect with proven ability to optimize performance of corporate databases, ensure full accessibility of data, and streamline dataflow. He possess over 25 years’ hands-on SQL Server database management experience in the Oil & Gas and Professional Services industries with clients in the Financial Services, Healthcare (Electronic Medical Records), Manufacturing and Public Sector.

More By This Author