SQL: Linked Servers: Don’t hard code server names

I’m not a great fan of linked servers in SQL Server but they are often necessary. I really wish the on-premises product supported External Data Sources and External Tables. But in the meantime, what I see all the time, is people hardcoding server names like this:

SDUPROD2016.WWIDB.Payroll.Employees

That makes your code really hard to manage. One option to get around that is to use synonyms.

Instead of sprinkling references to that table all through the code, you can create a synonym for it like this:

I created a local schema to match the remote one, and then created a synonym for the remote table. That way, the code can just reference Payroll.Employees instead of the four part name.

One aspect of this that I didn’t like is that if the server name or database name changes, that I needed to recreate all the synonyms, and I might have a bunch of them.

What fellow MVPs Simon Sabin and Erland Sommarskog pointed out to me today, is that you can use an alias name when creating a linked server. I’d always known you could do that for non-SQL Server linked servers, but didn’t realize that the underlying stored procedure would allow it for SQL Server linked servers too.

Notice that when you first go to create a linked server in SQL Server Management Studio, you can choose the name to be different to all the other properties (which include the server name):

But as soon as you choose the server type as SQL Server, that option can’t be used as the single textbox is used for both the name of the actual server and the alias for the linked server.

The underlying stored procedure sp_addlinkedserver quite happily lets you assign a different name.

This is important because it means that I can create a linked server called HRServer for my actual server SDUPROD2016, and use that name when creating the synonyms instead:

Then if I need to move the database to another server, I can just change the linked server definition, instead of all the synonyms that reference it.

That’s a great step forward but I still have to change them if the database name changes. And synonyms still don’t give me tooling support like Intellisense either.

So I’ll keep pushing to get External Tables and External Data Sources

2017-11-06