I have two databases, db1 and db2, running on two different servers, server1 and server2, and db2 has some stored procedures that will modify the table columns on db1.
In these stored procs, we use Linked Servers and fully qualified names to refer to the tables on db1, like [server1].[database].[dbo].[table1].
Now, we decided to migrate db1 from server1 to server2. After migration, db1 and db2 will both run on server2.
Questions
-
Do I have to modify each single cross server reference to remove the
[server1]name in the stored procs? -
Could we define cross DB or cross server reference through relative path, so we don't need to modify them every time the two databases change server?
Thank you.
Best Answer
Assuming that, post migration, you have absolutely no other need for this Linked Server to
Server 1, then you should be able to drop and recreate it with the same name but pointing to the current / local instance (i.e.Server 2). This is known as a "loop back" and will allow the code to continue working unmodified.HOWEVER, that is a short-term solution as you don't want to go through a Linked Server if you don't need to. You should go through all of the code and remove the
[Server1].references. This loop back Linked Server is just a means of buying time to do these changes slowly rather than needing to have it all done up front.