3

I am trying to find some workaround for moving SQL Server 2005/2008 instances from one data center to another data center without changing database configuration settings at application end, because application team is not sure where server name and database name are hardcoded. I know this is bad practice but the application is developed by a third party and we don't have control of application code.

So the challenge here is to keep the server name, database name, and IP the same.

Note: OS does not support Image Backup so this option is ruled out.

Please let me know your thoughts.

2
  • How much work and money can you afford to throw at this problem, and how much downtime (seconds, minutes or hours?) is acceptable when the migration takes place? What edition of SQL Server are you running on? Does your hardware and load on the database allow for a two-way replication between the old and new server? May 7 '17 at 12:17
  • Have you seen this? May 7 '17 at 12:29
4

So the challenge here is to keep the server name, database name, and IP the same.

I've done this many times in the past without any issue. I always get another SQL Server built and get everything defined over on it as is currently on the SQL Server I'm migrating from such as linked servers, credentials, proxies, logins with matching sids and password hashes, SQL Agent jobs, and on and on.

Be sure the Server OS on the new SQL Server has a "different" host name before installing SQL Server like ServerNameNew and a different IP Address, get the SQL objects defined or copied over (e.g. first paragraph above), tested and confirmed as best you can.

When it's time for the transition you will:

  1. Change the hostname and IP Address on the original server to something different
  2. Rename the new SQL Server OS to the original server host name
  3. Assign the same IP Address(es) back to the NIC(s) on the new SQL Server
  4. Change SQL meta data database name

    To rename a computer that hosts a stand-alone instance of SQL Server

    • For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

    • sp_dropserver <old_name>;   
      GO   
      sp_addserver <new_name>, local;   
      GO
      

      Restart the instance of SQL Server.


    • For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

    • sp_dropserver <old_name\instancename>;  
      GO  
      sp_addserver <new_name\instancename>, local;  
      GO  
      

      Restart the instance of SQL Server.

    source

If you get the new SQL Server built and define all the objects to match what's defined on the old SQL Server, and then test as much as you can until you feel comfortable with the testing results, make the changes as mentioned and everything should work as expected assuming you do not overlook anything in your test setup.

Important: Have other people help test once you get the DB objects defined, etc. and run through your rounds of DBA testing or whatever. The more eyes and people testing the new setup, the better.


Further Resources

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