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.