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

Are there any huge performance issues or securi开发者_StackOverflow社区ty concerns?

Using SQL Server 2005 and higher

---

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

---

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.

---

Having one (or many) set up on the server isn't the issue - the performance hit will be when you come to actually query them.

I have a Linked SQLServer 2005 set up, which is in the same physical building (on the same network) and it's not a problem - fast as you like.

I also have another Linked (Oracle) server which is on the other side of the world that is like walking through treacle and times out, drops connections (see here!)

Sorry to be vague, but ...it depends!