What are the drawbacks of using linked servers in SQL Server?

sql-server linked-server
Gabe picture Gabe · Jun 21, 2011 · Viewed 11.1k times · Source

Are there any huge performance issues or security concerns?

Using SQL Server 2005 and higher

Answer

gbn picture gbn · Jun 21, 2011
  • Server migrations are more convoluted
  • Security can be tricky to set up for multi-hop
  • Non-SQL Server ones requires a local driver installed (Sybase, DB2 etc)
  • Clusters, off-site DR: registry entries + drivers
  • Non-SQL Server x64 woes. 'Nuff said
  • Non-SQL Server ones don't play well (how many places to enter the password?)
  • Performance (in other answers)

I've set up linked servers to Access, DB2, Oracle, Sybase and the odd proprietary ODBC driver. I'd prefer SSIS or .net code now...

Related questions

SQL Server Linked Server Example Query

While in Management Studio, I am trying to run a query/do a join between two linked servers. Is this a correct syntax using linked db servers: select foo.id from databaseserver1.db1.table1 foo, databaseserver2.db1.table1 bar where …

Read More
How to select data of a table from another database in SQL Server?

Suppose that I have a database which name is testdb in test server. I also have a database named proddb in prod server. Now I want to select data of a table of testdb database from proddb database. How can …

Read More
How to find a text inside SQL Server procedures / triggers?

I have a linkedserver that will change. Some procedures call the linked server like this: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. We have triggers also doing this kind of work. We need to find all places that uses [10.10.100.50] to change it. In SQL …

Read More