Migrating SQL servers

In the process of our SCOM rollout we used a temporary server for our SQL databases. Finally the new hardware came in, but now we have to migrate the databases with minimal downtime. This is compounded by the fact that the new server is 64bit OS and SQL, where the original was 32bit. For posterity, this is what I did to migrate the server to new hardware:

1. Bring up the new server with 64bit OS, named as server1a (instead of server1)
2. Setup the drives in the standard partitioning scheme (C: OS, D: SQL install, F: SQL data, G: SQL backup, L: SQL logs, T: SQL Temp)
3. Install SQL 2005 64bit on D:
4. Move the temp database to T:
5. Copy the SCOM databases from the old server to the new
6. Shutdown the original server1
7. Rename Windows from server1a to server1
8. Rename SQL from server1a to server1

Moving the tempDB
Using the informaiton from the article at http://www.databasejournal.com/features/mssql/article.php/3379901 I executed the following SQL script
use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘t:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtempdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘t:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtemplog.ldf’)
Go

I then stopped SQL and copied the tempdb.mdf and templog.mdf from their default locations to the new location on T.

Copying the databases
Stop SQL on the originating server
Use robocopy to copy the data and log files to the new server (robocopy \server1f$ f: /mir /r:0 /w:0)
In the new server, attach the databases

Renaming the server
Once the system was up and running with the databases attached, I then ran the following TSQL to rename the SQL instance to the new server
exec sp_dropserver ‘server1a’
GO
sp_addserver ‘server1’, localexec
GO
exec sp_dropremotelogin ‘server1a’
GO

use MSDB
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY(‘server1’) AS sysname)
UPDATE sysjobs SET originating_server = @srv

Finally I renamed the windows server (powering off the original server first) and rebooted. Reinstall the SCOM datawarehouse and reporting – Voila, everything works!

– PS –
Looking back on the process I probably would have been better off to shrink the database files prior to the robocopy. This process took an extended amount of time and could have been sped up significantly.