Someone in my project wrote a perl script which used to generated a SQL script to create synonyms to other databases.
Now this SQL Script to create synonym used to run after the required databases were deployed and it uses to work fine till we went into the pre production deployment phase of the project. And as soon as we reached to this environment the setup itself started failing. The only difference between this environment and the test environment was that the databases were hosted in different machines.
After some investigation we established that the generated SQL script didn't take this into consideration. It was working fine in dev environment since all the databases were hosted in a single machine. As soon as the databases were separated the names of databases we were referring to in the script became invalid.
Linked servers came to rescue in this situation. Basically we added a Linked server to the server we wanted to create synonyms in and qualifies the table names with the linked server names as well.
On digging further the Linked server is an important and useful feature in SQL Server since it can not only make one SQL server interact with another, it can actually make SQL Server interact with any other OLE Source.
We can even use it to do the distributed transactions across various data sources.
Quickest way to Add a linked server to the database is to use the wizard in SQL Server Management Studio. One can also use stored procedure sp_addlinkedserver in the master database. sp_serveroption can be used to configure the options on how to connect to this linked server.
More information can be found here.
No comments:
Post a Comment