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

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.

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.

1 Thank you for this very useful bit of information and workaround. – Gabe Jun 21 at 20:14.

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!

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.