SQL: Linked Servers - It's a matter of being compatible
The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.
Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables again soon.
Common performance issue with linked servers
With Linked Servers, I often hear people describing performance problems. There’s a configuration setting that commonly causes this, yet it seems to be almost unknown.
In Object Explorer below, you can see I had a Linked Server called PARTNER on one of my older servers:
Let’s right-click this server, and choose Properties, then go to the Server Options page:
Collation Compatibility
The critical setting that causes lots of Linked Server performance issues is this one:
When you are connecting from server to server, and running queries that use data from both servers, this setting lets SQL Server know if it can trust the collation at the other end. If so, it can often push predicates, etc. across to the other server to be resolved there. If not, the local server often has to pull all the data (often enormous amounts of it) across first, before applying the predicates, joins, etc.
Imagine I need to find the customer on the remote server with a CustomerCode of ABC123.
If I trust the collation at the remote server, the local server can send the WHERE clause across to the remote server to be resolved. However, if the local server doesn’t trust the collation on the remote server, it would need to retrieve the whole table from the remote server and then apply the WHERE clause locally.
Final thoughts
Now clearly, you don’t want to set this if the servers do not trust each others collation but you can imagine the impact that this sort of issue has, particularly when dealing with multiple tables, complex predicates, joins, etc.
Linked servers often get a poor reputation regarding performance, when they are just misconfigured.
2026-03-11