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?