SQL Server Upgrade/Migration

Last week I was feeling burnt out. We’d been having numerous issues pop up at work and it was taking its toll on me. I felt constantly mentally fatigued and knew I needed some time to myself. My wife could sense I needed some relaxation, so she took the kids to her aunt’s house and let me have some solo time. I considered going down to Pewaukee to try and catch some muskies, but the water temp is peaking over 80 degrees, which is the max you should fish for muskies. Any warmer than that and they are unable to properly recover from the fight and could end up dying, even if it isn’t for a couple hours after they are released. Besides, I needed to just relax, and while muskie fishing is an awesome sport it really isn’t very relaxing. So I headed over to Little Cedar Lake to shoot for some walleye, largemouth bass, or northern pike. While I only managed one medium sized bass, it was great to just sit back and sip a few beers while enjoying the lake air and beautiful scenery. Since that night I have felt much more refreshed and ready to get back to work.


Evening Fishing
We have been doing some server upgrade/migrations the last several weeks. We purchased a new server and needed to move our production instance to it. We don’t have any kind of high availability, and were able to tolerate a system outage of four or less hours. The first thing I did was to practice extensively using our Test environment. I did the cutover in Test several times. I was confident going into the upgrade that I would be able to complete it successfully.  This upgrade was slightly more complicated in that in involved moving some databases to different drives during the upgrade process.  Not only was I going to move the system files from the C: drive to the data file and log file drives, but I also needed to move tempdb from the G: to the F: drive and move data and log files into differently named folders on the D: and E: drives.
I used the following procedure list to perform the upgrade:

  1. Stop the application services.  This will close existing connections to the user databases.
  2. Stop the data warehouse transfer process.  We use a third party tool to move data from our Production SQL Server instance to our Oracle data warehouse.
  3. Take full copy-only backups of each user database.  After taking each backup, take the database offline to prevent new connections and changing data.
  4. Take full backups of all system databases.  It is important to do them after the user databases so that msdb has records of the latest user database backups.
  5. Move all system database using SQL Script below.
  6. Create and restore all user databases using SQL Script demonstrated in other blog post.
  7. Shutdown new and old server.  System Admin changes the name of the old server to whatever, then changes the name of the new server to what the old server used to be.  He also updates the IP of the new to be what the old was.  This way we don’t have to change any application settings.
  8. Bring up Reporting Services on new server.  I will explain the intricacies of this step in a later post.
  9. Begin testing everything – application, SSIS, SSRS, and SQL Agent jobs.

Here are the SQL Scripts used to make the changes:

USE [master]
RESTORE DATABASE [model] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\model\FULL\OldServer_model_FULL_20150724_201857.bak' 
WITH  FILE = 1,  
--MOVE N'modeldev' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf',  
--MOVE N'modellog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\modellog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')

GO

--Stop SQL Server Agent
USE [master]
RESTORE DATABASE [msdb] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\msdb\FULL\OldServer_msdb_FULL_20150724_201858.bak' 
WITH  FILE = 1,  
--MOVE N'MSDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf',  
--MOVE N'MSDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\MSDBLog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')

GO
--Restart the SQL Service

--Stop the service
--Add -m to SQL Server startup parameters to start in single user mode
--Open command prompt
--sqlcmd
--Start the service
RESTORE DATABASE [master] 
FROM DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\master\FULL\OldServer_master_FULL_20150724_201856.bak' WITH REPLACE;

--Remove -m from SQL Server startup parameter
--Open command prompt
--Go to the SQL Server directory (on this machine, C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)
--run SQLServr.exe -T3608 -T3609
--		This will just open Master database.
--Run SQLCMD from a different command prompt and run these commands
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev01', FILENAME = 'F:\TempDB\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev02', FILENAME = 'F:\TempDB\tempdb2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev03', FILENAME = 'F:\TempDB\tempdb3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev04', FILENAME = 'F:\TempDB\tempdb4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev05', FILENAME = 'F:\TempDB\tempdb5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev06', FILENAME = 'F:\TempDB\tempdb6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev07', FILENAME = 'F:\TempDB\tempdb7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev08', FILENAME = 'F:\TempDB\tempdb8.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev09', FILENAME = 'F:\TempDB\tempdb9.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev10', FILENAME = 'F:\TempDB\tempdb10.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev11', FILENAME = 'F:\TempDB\tempdb11.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev12', FILENAME = 'F:\TempDB\tempdb12.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev13', FILENAME = 'F:\TempDB\tempdb13.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev14', FILENAME = 'F:\TempDB\tempdb14.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev15', FILENAME = 'F:\TempDB\tempdb15.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev16', FILENAME = 'F:\TempDB\tempdb16.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'F:\TempDB\templog.ldf')

--End SQL Service, then start the service normallyy.
--At this point all the system databases should be transfered to New Server.

As I said, the migration went pretty smoothly.  I did have a few hiccups that should have been tested weeks before the migration, but I missed them.  I was able to get them working within a day of the migration, and next time I’ll get them right away.  The first was the database mail, which took me an extra hour to get set up right after the migration.  The second was a linked server.  This was a bit tougher because I had to install the Oracle ODBC driver, and this required a SQL Server reboot.