I seem to be in the minority when it comes to SQL Server linked servers. When it's another SQL Server instance on the other end, I quite like them for administrative purposes. But other SQL pros have some reservations and gripes. I've even seen the word "hate" thrown around freely. Most of the complaints seem to fall into one of these categories: poor performance, insufficient permissions, poorly configured security, and challenges related to remote execution of queries.
I recently read a couple of good articles on linked servers. One by Kendra Little, the other by Thomas LaRock. I'll be frank: they both taught me some things about linked servers that I didn't know previously. I can see why some of the issues they covered could cause some grief. I don't see any of the issues as a hindrance for me as a DBA, though. I have a CMS where I frequently run queries against a group of registered servers. In addition, I have a linked server on the CMS for every registered server, which is used for automated tasks. (Here's a more detailed post with several code samples.) For my use case as a DBA, I'll try to explain why the arguments against linked servers are bad ones.
Queries that reference objects on the local server and objects on the remote server (ie table joins) is a common reason for poor query performance. This makes sense. But for my purposes, the CMS/local server is not a pivotal piece. The sets of data I want to get at reside solely on the remote servers. I'll just leave it at that. There's nothing more to add.
For "older" versions of SQL Server (SQL 2012 RTM or prior) the login or associated database user on the remote linked server needs elevated permissions to make use of statistics (role membership in [sysadmin], [db_owner], or [db_ddladmin]). Without them, some pretty horrible execution plans are bound to happen. From what I've read, Microsoft fixed this starting with SQL 2012 SP1. As the DBA, it probably comes as no surprise that I have [sysadmin] role membership on the CMS, plus on each remote server. If you're in the same situation, the inability to use statistics is not an issue.
Poorly Configured Security
As DBAs, we should have a thorough understanding of SQL Server authentication and authorization. The security page on the "New Linked Server" dialog shouldn't be confusing at all. We should be capable of creating remote connections that are sensible, safe, and secure. When we create linked servers for our own administrative purposes, we have no one to blame for poor security but ourselves. The linked servers themselves are not at fault.
You're going to run into things that don't work as expected when run from the CMS/local server. @@VERSION, @@SERVERNAME, and @@CURRENT_TIMESTAMP are a few good examples. For those to return the correct value (relative to the remote server), you're going to have to pass queries through on the fly. That means plenty of dynamic TSql. But we are DBAs! We can double, triple, or quadruple our quotes. We can write queries that write queries. We can rise to the challenge, right?
Side Note: It can be challenging to write queries that are compliant across multiple versions of SQL Server. It's an issue you'll likely have to address if you use multiple linked servers. But let's be fair: you have the same challenge when working with registered servers. This isn't a linked server specific problem. It's one you'll probably be faced with regardless.
In my next post, I show a few queries that solve some of my administrative needs for reporting, inventory, and the like. For now, be mindful of the pitfalls Kendra and Thomas tell us about. And keep an open mind!