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

Question

Are there any huge performance issues or security concerns?

Using SQL Server 2005 and higher

Answer 1

• 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...

Answer 2

Yes - Queries which join two datasets in different physical databases perform poorly.

e.g. If you run a query between table A on the current server and B on a linked server.

 Select A.Field1, B.Field2 FROM A INNER JOIN B on A.Id = B.Id
 WHERE B.Id = @InputId

you may find that all the records for table B are retrieved - effectively

 Select * from Table B

into the working server.

What you'd want to do instead is have a usp on the linked server which takes an Id as a parameter and returns a filtered recordset from Table B

Then rewrite the query above to join Table A to the usp instead.