I did another SQL Server migration over the weekend that dealt with linked servers. I’ve seen all kinds of odd naming schemes and there are a few I like and a few I suggest you avoid.
Don’t name your linked server for its IP address. At some point whatever is on the other end of that IP address will move. You’ll probably need to point your linked server to a new IP address but not change the name of the linked server. And then you’ve completely lost any context around this. Bonus points if a new SQL Server eventually ends up at the old IP address further adding confusion when you’re trying to troubleshoot.
Don’t name your linked server based on its instance name. This one is less obvious. It sounds nice to have a linked server named [VSRV1\SQLTRAN01]. You know what it is and it’s easy to use. It’s less nice when you’ve got 200 stored procedures that all reference this linked server but the database they reference has moved to a new instance. Now when you query this you’re actually querying a different instance.
(Please note: I’m not saying it’s a good idea to have 200 stored procedures that all reference a linked server. I’m just saying it’s not all that uncommon.)
Consider naming your linked server something that you can easily search on. See my note above. You can also get around this by always enclosing the name in brackets. That is harder to enforce unless you use some odd characters in it.
Consider naming your linked server based on the function. For example, I’ve had some luck having a linked server named [DW] that points to our data warehouse server. That server can change names or physically move and all I need to do is update the linked server to point to the new destination. The descriptive name of the linked server is still accurate. No code needs to change and people still know what it is just by looking at it.
Consider naming your linked server for the database. I’m still thinking through this one. It may mean you have multiple linked servers that point to the same instance. I’ve found that database names rarely change. It also makes it easier to move individual databases to new servers.
Consider pointing your linked servers to DNS entries and not IP addresses. I’ve done this for reporting databases and had some success. Especially for read-only snapshots that can get created on the main database or on the mirror.
What issues have you had with linked server names? What has worked for you? Where are the holes in my approach?