Are you sure linked servers are being deprecated? No, I had equated Linked Servers with Remote Servers when writing the editorial yesterday. Please accept my apologies for the confusion.
Linked Servers are not the same as Remote Servers, although they perform similar functions. It is not linked servers that are being deprecated. Remote Servers are being deprecated, which is something Microsoft has published since the release of SQL Server 2005 if I have my research correct.
In the SQL Server 2014 list of deprecated features, the command sp_addserver (used to add remote servers) they recommend using sp_addlinkedserver instead.
Based on the number of comments and emails today it is clear that linked servers are important to many companies today. Many use them for integrating non-sql server sources of data into their queries. Others were using them for heterogeneous queries joining data across multiple instances of SQL Server. There is a push in the development methodologies to move away from using linked servers for these functions.
For integrating remote data SQL Server Integration Services or some other data access tool are thought to be better tools. The data may be brought into SQL Server as it is found in the remote data source, or the joined results may be brought into tables such as data marts. Since the join is not performed in SQL Server the performance is often much better.
For performing heterogeneous queries, linked servers are not performant when accessing large tables. Because statistics are not available on linked servers, any join most often will perform a table scan on the remote tables. If you lift this kind of query out of SQL Server and place it into a data access layer you can easily take advantage of user defined table types.
As you may know, you cannot use the definition of a user defined table type from database b when defining a query in database a. The objects are not sharable, and may not be referenced from a fully qualified name. However, if you are using ADO.Net you can call a query and pass in a user defined table type as a parameter should the remote database need to be filtered on a set. If the remote data is not too large you can return all the records, and perform the join and data tables using Linq or other techniques.
As I suggested yesterday, these are techniques you will need to do if you move to SQL Azure where the size of a database is limited. That limitation may result in the need to shard data in order to allow growth. In this scenario you might need to perform a union of sharded results across databases or a join of different data from different shards.
Is this something we need to start working on now? Many responded yesterday with alarm that the loss of linked servers could result in broken applications. Do we need to move ahead now, or is there no compelling reason to make a change? Share your thoughts online or by email to btaylor@sswug.org.
Cheers,
Ben