Difference between a linked server and a synonym?

Linked Servers and Synonyms are used for different purposes in SQL Server. One is not a substitute for another.

Linked Server is a way of establishing a connection to an external data source which could be another SQL Server, Oracle or any other type of data that has an OLE DB provider. More info here.

Synonyms are way of creating alias for database objects so as to simplify names in SQL queries and to provide a layer of abstraction to reduce impact on client queries when referenced object changes name or location. More info here.

Let us say you are on SQL Server ABC and would like to create a stored procedure that needs to access ProductCategory table in Adventureworks database on SQL Server XYZ.

1. You would first create a linked server with some name - usually same name as target - XYZ

2. You can now access the table as follows

   SELECT * FROM XYZ.dbo.Adventureworks.ProductCategory;

   Note: You can use the above 4-part name in your queries and stored procedures to access not just 'ProductCategory' but any other tables and views.

3. Such long names complicates your queries and if you have to point to a different server or table, you would have to change all the queries.

4. Instead, you could create a synonym that references above remote database object and it can have short name, say ProductCategoryRemote and then use it in your queries as

   SELECT * FROM ProductCategoryRemote;

   If you decide to use a different table or server (like when moving from UAT to PROD environments), all you need to do is drop and recreate the synonym referencing the new object.

   Note: Synonym can reference objects within the same database, other databases on the same server or another server through linked server as in this example.

In conclusion, Linked Server are required to access an external data source and synonyms are used to create alias for database objects.

You use a linked server to connect to a database on a different server. You use a synonym to specify the object (e.g. table) you want to access in SQL, it is like an alias. For more info see here.

For example, ServerName.DatabaseName.dbo.TableName is a synonym for the table TableName, owned by dbo, in the database DatabaseName on the server ServerName.

A linked server is the other server (or instance) you want to connect to.

Basically, you configure a linked server to access a different database instance. You use synonyms to specify the objects you want to use on the different instance in TSQL.

You can configure a linked server by using SQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) statement. Taken from here.