We have a need to migrate from existing hardware that is running SQL 2005 to new hardware running SQL 2008 R2.

When all is said and done we want the same IP and hostname that was on the "old sql 2005" server to be on the new SQL 2008 R2 server hardware.

This is because we don't want to mess with client connection settings or things that are hardcoded in programming, etc.

We currently have the new 2008 R2 box built with a different name.

Is there a way to move the existing 2005 databases, SSIS jobs, SSAS cubes, SSRS reports, etc. (everything, security, etc.) over to the new hardware and then rename the new hardware to the same IP and hostname as the old.

BOTTOM LINE: we are wanting to move sql 2005 from old hardware to new hardware running SQL 2008 R2 without messing with the clients.


If (as uSlackr points out) you are comfortable moving the content then do this:

  1. Bring up your new server, call it MyNewServer (or whatever) with a new IP. Install SQL 2008 R2 on it.
  2. Migrate all of your content over (db's, jobs, logins, SSIS, SSRS, cubes, etc)
  3. When you're happy that it's good, shutdown the 2005 box
  4. Rename the new server with that of the old server, readdress to the old IP
  5. In SQL Server execute the following statements:

    sp_dropserver MyNewServer
    sp_addserver MyOldServer, local

(See this MSDN article for more information about renaming a host that runs SQL Server)

Note, there WILL be some anti-OCD remnants leftover in the local groups that get created by SQL Server installation. Some groups get created with the name of the server as part of the group name, so you'll have some local groups with MyNewServer as part of their name. This won't matter from a technical standpoint.

If you need some steps on how to do your content migration leave me a comment and I'll add some things you can do.

|improve this answer|||||
  • woah! i didnt know about that sp_addserver command. thanks. – djangofan Sep 2 '11 at 22:36
  • Thanks! I'll let my SQL guy see this and if we have more questions I'll post back. – TheCleaner Sep 3 '11 at 2:41

I can't speak to moving the content, but it sounds like you know how to do that. As for not messing with the names, name the new server whatever you'd like and give it a new IP. When it comes time for cut-over do the following:

  • Update the DNS record for the old servername to point to the new server's address
  • add the IP address of the old server to the NIC of the new server so it is serving both addresses.

Whether the client uses the server name or IP address, this will ensure the get to the new box.

|improve this answer|||||
  • The problem is that our SQL guy is being told from a friend that you cannot simply rename a SQL hostname and have everything just work on a reboot. – TheCleaner Sep 2 '11 at 20:31
  • @TheCleaner His friend is right. See my answer. – squillman Sep 2 '11 at 20:34
  • I didn't recommend renaming the server, only pointing the old DNS entry to it – uSlackr Sep 2 '11 at 20:59
  • @uSlackr Right, understood. My comment was in response to TheCleaner's. I personally prefer to keep DNS and hostnames 1-1. – squillman Sep 2 '11 at 23:53
  • Thanks for the help uSlackr. That was the original way we were thinking but Squillman's answer is "cleaner" (no pun intended). – TheCleaner Sep 3 '11 at 2:41

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.