Replication versus Linked Server
-
We have a table that is located in a central SQL Server DB that is currently replicated to a bunch of application-specific SQL Server DB's across multiple servers. We are considering replacing SQL Server replication with Linked Servers, and I am looking for opinions/pros/cons. The plan would be to create linked servers on each box that contains a DB that needs to select from the (previously) replicated table. A view would then "hide" the 3-part name and the view would be named exactly the same as the table, so no code would need to change.
I have heard the argument that Linked tables are a potential security risk, but it seems that can be reduced by carefully limiting access granted to the logon used by the Linked Server.
Are there other reasons people can think of for NOT using Linked Servers - performance, etc?
-
kevin luke (6/27/2008)
We have a table that is located in a central SQL Server DB that is currently replicated to a bunch of application-specific SQL Server DB's across multiple servers. We are considering replacing SQL Server replication with Linked Servers, and I am looking for opinions/pros/cons. The plan would be to create linked servers on each box that contains a DB that needs to select from the (previously) replicated table. A view would then "hide" the 3-part name and the view would be named exactly the same as the table, so no code would need to change.I have heard the argument that Linked tables are a potential security risk, but it seems that can be reduced by carefully limiting access granted to the logon used by the Linked Server.
Are there other reasons people can think of for NOT using Linked Servers - performance, etc?
If the amount of data you will be retrieving from the Linked server query is small, you maybe fine. Otherwise you will be forcing (possibly) a distributed transaction and bringing large amount of data accross the network. This kind of traffic could impact not only the source but also the destination.
I have seen places in which OPENQUERY was used to setup "views" that retrieved very limited amount of data (with hardcoded where clauses) and it worked very well. The minute you start wanting more flexibility things could get hairy
* Noel -
If you are going to go with linked servers, you should use synonyms rather than views in most cases.
I am not a big fan of using linked servers extensively. They have a tendency to promote a lot of network traffic. They also cause memory issues on SQL Servers because the memory used by the drivers ends up in the non-MSSQL process memory allocation in SQL Server. Particularly with SQL 2000, I have found that the -e startup parameter becomes necessary very quickly.
Also, if you have tables being accessed through linked servers and then joined together, you have to pull all of the data from the tables onto the local server and the join ends up being done locally - if this does not yield all of the records, your query has often spent a lot of time pulling over, sorting, and filtering a lot of unnecessary data.
If you have a significant number of tables and your join to them a lot, the replication plan that was originally put in place was probably the better direction.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply