Whilst working on a recent project which included quite a few Linked Server calls within several Stored Procedures, I looked at using SQL Server Synonyms. The problem with linked servers in SQL queries is that if the linked server changes, E.G from Development to Test then to Live, the SQL query must also be updated and you may end up with multiple versions of the same piece of code. You can of course use Dynamic SQL but I’m sure many of us don’t wish to do so. An easier way is to create a Synonym on each server (development, test, live etc) and reference the Synonym name (for example “OrdersTable”) in your SQL query. This way when you come to deploy your SQL code onto each server you will not have to change any of the actual SQL code. Of course you will have multiple SQL scripts to create Synonyms but since it’s a small piece of code the overhead is far less than multiple versions of your actual SQL code.
Creating a Synonym to a Linked Server table:
CREATE SYNONYM [dbo].[OrdersTable] FOR [LinkedServer].[YourDatabase].[dbo].[OrdersTable]