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 
Useful; thanks
Thank you for the article! I've also used DNS CNAME in the past to get around this issue.
Ok, I don't really respond to many posts like this but I felt the need to do it in this case.
I feel the synonyms are a bad practice because if you are unfamiliar with the database environment and trying to troubleshoot an issue, how would you know whether a table is being referenced by a synonym or not. I had a case where I was troubleshooting a stored procedure that was using synonyms. I went down a huge rabbit hole thinking that there was security issues, network issues, issues with the SPN on SQL server, etc, etc. Only to find out that the tables involved were using synonyms. I think many developers would agree with me that the multiple part naming conventions are actually better coding practice because it's completely clear where those objects are coming from and they are self-documenting that way. If a linked server is being referenced it's clear in the code and you will know right away. When using synonyms if it not explicitly obvious and the errors returns are not going to tell you the code is using synonyms. Bad concept all together in my book.
Sorry Marty, but 100% disagree. If you didn't know that a table name was a synonym, that's just a naming issue or basic education issue. I'm guessing the most likely is a lack of appropriate use of schemas. If I see a table like PayrollDB.Employees or FinanceDB.Transactions, it's pretty obvious that the table isn't local, at least in any system with reasonable naming.
Littering code with actual server names is never a good idea, in any programming language, including T-SQL.
If you have multiple environments, using 4 part names means that as you move code from dev to test, to UAT, to prod, etc. you have to find and replace those names on each move. Every time you do that, you have the chance to introduce new bugs. It also means that code can't just be moved between environments without being changed. That's not going to be popular in DevOps based environments as part of CI/CD.
It also means that when you are using database comparison tools, all your objects that use different names in different places will end up failing comparison checks.
And as for documentation, I'd respectfully suggest that PayrollDB.Employees is way more self-documenting than SQL12342J.PAYMAIN.dbo.Employees. From the perspective of the code, the issue is that it's coming from the Payroll DB, not which copy of it, and on which server and database it happens to be deployed to right now.
My 2 cents worth is that I agree with Marty.
I found this article while Googling around for a best practice on when to use a synonym. Haven't found one yet that sways me from simply using a linked server that has an "alias" name. This way
4 part names do not have to change between environments and they spell out where the data is.
payrollserver.payrollDB.dbo.tableName
Use the built in stored procedure sp_addlinkedserver like below. The @server parameter is your alias, the @datasrc is the actual server you want to link to. @srvproduct and @provider as stating that it is a SQL Server on the other end.
EXEC master.dbo.sp_addlinkedserver @server = N'FriendlySQLServerName', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'ActualSQLServerName'
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15
I'm happy enough with aliases if you prefer to use those. My main issue is that I constantly see actual server names throughout code. That's what I don't want to see.
I still have a preference for External Tables now though, and given they were added in SQL Server 2019 (since this post back in 2017), I have a strong preference for those. They avoid a number of the issues that linked servers have.
FYI, I posted my comment twice as I was trying to get line feeds between my paragraphs. Didn't turn out 🙁