Migrate SQL Server - server name in stored procedures references old server 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?


Modify all stored procedures and change these occurrences 
from [old_server\instance1].db1.dbo.table1 to [new_server\instance1].db1.dbo.table1
Do not change the stored procedures, but create a reference from the old server to the new one using sp_setnetname
Rename the new server after the migration to keep the old name
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:


#BackToBasics : The Beauty of the Synonym