Geek Answers Handbook

Difference between linked server and synonym?

Is there a difference between a t-sql-connected server and a synonym? If so, what are they, and in which cases would I choose one of them?

+8
sql-server
source share
2 answers

You are using a linked server to connect to a database on another server. You use a synonym to indicate the object (for example, a table) that you want to get in SQL, it looks like an alias. For more information see here .

For example, ServerName.DatabaseName.dbo.TableName is synonymous with the TableName table owned by dbo in the DatabaseName DatabaseName on ServerName .

A linked server is another server (or instance) that you want to connect to.

Basically, you are setting up a linked server to access another instance of the database. You use synonyms to indicate the objects that you want to use in different instances in TSQL.

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

+3
source share

Linked servers and synonyms are used for various purposes in SQL Server. One is not a substitute for the other.

A linked server is a way to establish a connection to an external data source, which may be another SQL Server, Oracle, or any other data type that has an OLE DB provider. More info here .

Synonyms are a way to create aliases for database objects to simplify names in SQL queries and provide a level of abstraction to reduce the impact on client queries when the reference object changes its name or location. More info here .

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

  1. First you must create a linked server with some name - usually the same name as the target - XYZ

  2. Now you can 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 only the "ProductCategory", but also any other tables and views.

  3. Such long names complicate your queries, and if you need to point to another server or table, you will have to change all the queries.

  4. Instead, you can create a synonym that refers to a remote database object and can have a short name, say ProductCategoryRemote and then use it in your queries as SELECT * FROM ProductCategoryRemote;

    If you decide to use another table or server (for example, when switching from UAT to PROD environments), all you have to do is delete and recreate a synonym that references the new object.

    Note. A synonym can refer to objects in the same database, to other databases on the same server or to another server through a linked server, as in this example.

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

0
source share

More articles:


All Articles