Best way to merge tightly coupled SQL Server Instances I´m looking for some recommendations or best practice for the following scenario:


I need to merge several SQL Server 2014 instances into one (we are switching from many instances to a single clustered instance).
The scope of this merge is of about 10 servers, and 400 databases.
The source instances are very tightly coupled. There are many linked servers, and a lot of stored procedures, views, and functions that use them.
Some SPs also perform operations in the file systems of other servers in the scope of this migration.
As far as I know, we may be forced to change some DB names because we would have name collisions between databases on the destination instance.


I have scripted all DB objects so I can do some text search to find all occurrences of queries that use a given linked server.

So far I believe my options are:

1- The long one. Change the queries that use linked servers so it will query to the same instance (just to another DB in the instance).

2- A short one. We believe we can create DNS aliases that would make the old machine names point to the new clustered instance.

Then we would create several linked servers in the clustered instance pointing to the same instance. The linked servers would have the same names as in the source servers. This should make the old SPs, functions, and views work without any change (except when we need to change a DB name).

Does anyone know a better option or have any recommendations?
I faced an analogous problem where we had different environments using their own linked servers so we could never guarantee that dev was like integration was like test was like production.

The "doh" moment was when we worked out that you can name a linked server whatever you want irrespective of its physical name.

I've not got access to a SQL Server instance to test whether you can create a linked server to itself but if possible then entire redirection would be self contained within your consolidated instance until such time as you were able to weed out the procs that rely on linked servers.