Wednesday, December 19, 2007

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 't:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.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 \\server1\f$ 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.

1 comment:

Rob said...

We are about to do a similar thing with our Prod Operations DB. Rather than moving from Dev to Prod, we are replacing our Operations DB with new hardware and propose to use the same process that you have used here.
One question, do you know if it is necessary to reinstall DW and Reporting of they are hosted on different servers?