Yesterday we talked about the sysdatabases system table and today’s entry is about the sysservers system table. As with other system tables, this one has the same disclaimer that this system table will be removed from a future version of SQL Server. So, yeah, you have been warned.
What is this table for?
From the BOL definition:
“Contains one row for each server that an instance of SQL Server can access as an OLE DB data source.”
However, I found that to be misleading at first, because I *just knew* I could create an ODBC data source that will be displayed in this table. Well, not exactly. I mean, I could create an ODBC data source, but if you look closely when creating the linked server you will see that you are actually using a provider with the name ‘Microsoft OLE DB Provider for ODBC Drivers”. So, yeah…OK then.
Let’s just say that this table will return one row for every linked server defined as well as one row for the instance itself (srvid = 0). There, that makes things easier to understand, right? Go ahead and try it for yourself:
SELECT * FROM master.dbo.sysservers
Why should you care?
Because you are a great DBA, that’s why! I would say you have a handful of reasons to care about this. First up, of course, is disaster recovery. If you should ever need to rebuild a server you will want to know exactly what linked servers had previously been defined. So saving the details from this table each night would be worth the trouble should disaster strike.
Of course if you save the details and you need to rebuild a box, then you will want to have a way to test the connections before handing it over to your end users. good thing I have already written about how to do that, so I don’t need to do that again here.
Another reason to care is for stability. It is a good thing to know if your environment has changed. All too often I would find that developers with too much access would go into a server and create or modify linked servers for their own use. This was a nightmare when modifications were done, as they would tinker with something in order to get one piece of code working, but it would end up breaking things for someone else. Good thing I have written about how to monitor for that as well.
Lastly, you should care about this table because of crappy vendor applications that like to create loopback servers, which can cause you problems when trying to check for a result using @@SERVERNAME. You can scrub the sysservers table to ensure that such changes have not been made (or just wait for your code relying on @@SERVERNAME to fail, either way is fine).
So, yeah, I’ve been digging through the sysservers table for quite some time now.
Where else is this information?
Remember how I said this system table will be removed from a future version of SQL Server? Then start using the sys.servers linked server catalog view instead. (BTW, the description on the catalog view page is much more clear than the description on the system table page, have a look and judge for yourself). And don’t forget to check out the differences between the columns returned between the older system table and the newer catalog view.
