Migrating SQL Server 2005 between hardware nodes

Question

I am currently in the process of consolidating a dedicated server serving MS SQL Server 2005 with a dedicated web/application server that hosts ~ 700 very low-traffic websites. The target system houses a quad-core 5148 Xeon CPU and 12 GB of RAM. The memory and CPU load on both servers is relatively low (hence the consolidation).

What I have no creative idea of doing, after hours of Googling, however, is migrating the SQL Server data and settings verbatim to the new server with minimal downtime. Would shutting down the services on both servers and actually copying the whole MSSQL.1 directory to the new server work or is it a crime against humanity (considering I'll have the exact same MSSQL version and SP level installed)? Bare in mind that after the data is migrated, I'll add the old server's IP to the new server (in order to save the customers of the trouble of updating their web.config files to 127.0.0.1).

Any help would be appreciated!

Answer 1

It will work if all paths are the same. I've done it myself several times.

Registry entries etc reflect the binaries' location and master db location. Everything else is self contained (eg logins in master).

You'll gave two issues at least:

• @@SERVERNAME will show the old server name because it comes from sys.servers. Follow this guide to fix

• Any SQL Server Agent jobs will be "owned" by the old server. I'd script them out (in SSMS) and apply them to the new box, but not copy msdb

If you have any other clients that connect (monitoring etc) then you can DNS alias the old server name. See these links (SO). One and Two and Three

Answer 2

1. Set up the new SQL Server and have it running.
2. Use the following KB article to transfer logins from the old to thew new.
3. From the old database server, script out the CREATE DATABASE ... FOR ATTACH using the paths for the new SQL Server. Save the script and put it where it can be accessed from the new one.
4. Shut down the old SQL Server. Copy the data and log files to the appropriate location on the new server.
5. Run the CREATE DATABASE ... FOR ATTACH script to bring all the databases on-line.

This is leaving out a couple of things because I'm making a few assumptions:

• You're not dealing with domain membership. If you are, you're going to want to make sure you have the local admin password on the old server (and that the account is enabled and you can log in with it - test this!). Drop the old server out of the domain and rename it. Drop the new server out of the domain. Rename it to the same name as the old server. Add it back to the domain. Then follow the steps for changing the @@SERVERNAME (as gbn linked to) to the correct name.
• None of the databases have any special configuration options like FILESTREAM, cross-database ownership chaining, etc. If so, you'll want to script out those options before taking the old server off-line. You'll need to run that script on the new server after you bring the databases on-line.