1

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
TheCleaner
  • 32,627
  • 26
  • 132
  • 191

2 Answers2

4

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
    GO
    sp_addserver MyOldServer, local
    GO

(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.

squillman
  • 37,883
  • 12
  • 92
  • 146
1

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.

uSlackr
  • 6,412
  • 21
  • 37
  • 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 02 '11 at 20:31
  • @TheCleaner His friend is right. See my answer. – squillman Sep 02 '11 at 20:34
  • I didn't recommend renaming the server, only pointing the old DNS entry to it – uSlackr Sep 02 '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 02 '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 03 '11 at 02:41