I've always had a love-hate relationship with linked servers. On one hand, they are very easy to use and query. On the other they can be a pain to configure, secure, and performance tune.
I wrote about naming linked servers in 2011. After living with various schemes, I'm happy with something like what's below. This would probably work well into the low hundreds of servers.
- Create a zone for static DNS entries. This should be separate from any other domains you have. Something simple like
- You can use either "A" records or CNAMEs depending on what you're pointing to. CNAMEs can point to availability group listeners. "A" records can point to IP addresses. We still use "A" records for the AGs and have tooling around the cross-data center fail over process to push changes to all DNS servers.
- All the hosts in this zone are named something like
db-apps. Something short, simple, and descriptive. So the full name would be
- We use prefixes for different types of "things". So all the DNS entries for databases get a
- All linked servers, applications, and users should point to these static DNS entries and NEVER the actual server name or IP address.
- All linked servers should be named for these DNS entries. For example, the linked server that points to
db-txn. I've also considered naming them with a prefix like
ls-db-txnbut haven't needed that level of indirection so far.
- The hyphen is the key part of this name. That forces the name to always be enclosed with brackets. So you have to run
SELECT [name] FROM [db-txn].master.sys.databases. That makes it MUCH easier to find TSQL code using the linked servers.
Most of these static DNS entries are named for a server (one DNS entry per server). Some are named for specific databases on a server. We try to decide what group of databases could logically migrate together. Many of our databases are on their third generation of server with the same static DNS name.
For example, packaged software from ACME Corporation that requires a database might get a DNS named
Hopefully this helps your management of linked servers.