2

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.

  • 1
    After db1 migrates over, do you have any other need for Server 2 to connect to Server 1? – Solomon Rutzky Sep 5 '17 at 16:01
  • I'd create yourself a loopback linked server to fix the immediate need, and then go through and retroactively fix all of the procedures to remove the 4 piece convention (and maybe add local database synonyms in case you want to move them apart again, so that you can just update the synonym references in the future) – Nic Sep 5 '17 at 16:13
  • 1
    Consider using synonyms for cross-db or cross server references. Proc code could then reference synonyms that resolve to 3 or 4 part names. – Dan Guzman Sep 5 '17 at 16:15
  • @SolomonRutzky, no, after migration, Server 1 will be abandoned – Tiancheng Li Sep 18 '17 at 14:18
2

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.

  • 1
    You can run into problems trying to use a Loopback server in a transaction - you can get the dreaded 'Transaction context in use by another session.' error - See the section on Loopback servers here - msdn.microsoft.com/en-us/library/ms188716.aspx – Scott Hodgin Sep 5 '17 at 18:26
  • Thank you guys for the nice advise, I think I'll go through all code to remove the [Server1] references. – Tiancheng Li Sep 18 '17 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.