0

I will migrate an SQL server from 2000 to 2008 R2. It contains two instances. I have many stored procedures that are referencing the instance names like:

select ... from [old_server\instance1].db1.dbo.table1

What is the best way to deal with these stored procedures?

  1. Modify all stored procedures and change these occurrences from [old_server\instance1].db1.dbo.table1 to [new_server\instance1].db1.dbo.table1
  2. Do not change the stored procedures, but create a reference from the old server to the new one using sp_setnetname
  3. Rename the new server after the migration to keep the old name
1
  • 5
    No but you could create a synonym, say, dbo.remotetable1 for [new_server\instance1].db1.dbo.table1. Then the server name is just in the synonym for the object, and not littered throughout the code. When you need to change where it points, you just change the synonym once, and that can include consolidation (where the server prefix is no longer required). – Aaron Bertrand May 11 '18 at 14:13
1

If I have to make the choice, I'd rather alter the stored procedure to replace the hard-coded old server name with the new server name because it is pretty straight-forward doing this manually. Just script out all the stored procedures using SSMS and then find and replace the old server name and re-run the script.

Of course, you can use PowerShell to automatically script out each SP and alter them at the same time.

1

You could create a synonym, say:

CREATE SYNONYM dbo.remotetable1 FOR [new_server\instance1].db1.dbo.table1;

Then the server name is just in the synonym for the object, and not littered throughout the code - the code just references dbo.remotetable1.

When you need to change where it points, you just change the synonym once, and that can include consolidation (where the server prefix is no longer required).

I wrote up a little about synonyms here:

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.