Our team recently completed a project where we had to move multiple SQL Server instances from old hardware to new VMs. To minimize business impact, we were required to keep the same server name, IP address, instance name, and IP Port #. The servers involved were a mix of 2005, 2008, & 2008R2. Our goal was not to upgrade SQL, but to migrate off the old hardware to new VMs.
We considered different migration methods, each with their pros and cons. After deliberation and testing, we decided for our unique situation it would be best to back up the system databases (master & MSDB), restore them on the new VM, then copy over the user database files.
There are a lot of valid concerns regarding whether or not to restore master for a migration. Some feel it is best to install SQL, and bring over the logins, linked servers, etc. and never restore master unless you have to. I understand that position and for the most part, agree with it. This article is not to argue that point. The main purpose of this article is to help the SQL community if they find themselves in the position of needing to migrate in this manner. It may be a planned outage or it could be a production outage/rebuild. Here are the detailed steps we took. I hope you find it useful! : )
Pre-Migration Steps
Here are the items that are important to complete prior to actually migrating the SQL Server instances.
Step 1 - Build out the new VM
Ensure the new VM was on a VLAN that could retain the IP address from the old server. Also verify that drive sizing, CPU, RAM, etc. is sufficient for the new VM.
Install SQL Server on the new VM. Make sure the service pack/patch to match the exact build version of SQL on the old server. This is required in order to restore the master database from the old server. Click here for info on SQL Server versions.
Step 2 - Backup Master & MSDB
On the old server, back up the master and msdb databases. Once this is done, copy the backup files over to a safe location on the new VM.
Step 3 – Restore Master
This is where it can get tricky. Our new VMs had a requirement to have different drive letters than the old servers. Therefore, we needed to ALTER both system and user databases with the new drive letters in the file locations. If you restore master and don’t take these new drive letters into consideration, SQL will not restart after a master restore.
On new server, stop SQL services. Start SQL in single user mode by opening a command prompt and navigating to your SQL install directory where sqlservr.exe is located. Type the following…
sqlservr.exe -m -c -s <instance name>
Once SQL has started in single user mode, open another command prompt and type the following…
sqlcmd -S <servername>\<instancename> –E
This will connect you to your SQL Server using your Windows login, assuming your system is in mixed security mode. In the SQLCMD window, type in the following:
1>restore database master from disk = 'h:\master_052115.bak' with replace
2>go
Your command prompt will close and SQL Server will shut down after you run the restore statement. Now you will need to bring SQL back up with a trace flag. If you don’t use this trace flag, the new master database will attempt to start up the system databases, but won’t be able to because the drive letters on the new server are different. Use the trace flag T3608 and the minimal configuration start up parameter (see links below).
Type the following at the command prompt to start sql with the trace flag in minimal config…
NET START MSSQL$<instancename> /f /T3608
SQL will let you know the service is starting with these messages:
The SQL Server (<instancename>) service is starting.
The SQL Server (<instancename>) service was started successfully.
Now that SQL Server has started, make a SQLCMD connection so you can alter the system dbs with the new drive locations. Type this in the command promt.
sqlcmd -S <servername>\<instancename> -E
This will bring up your SQLCMD prompt. We needed to ALTER our system db file locations to match our new drive layout. Type the following in your SQLCMD connection. Make sure the physical drive path in the FILENAME parameter matches the path on your new server.
1> ALTER DATABASE model MODIFY FILE(NAME = modeldev, FILENAME = 'H:\MSSQL\DATA\model.mdf');
2> ALTER DATABASE model MODIFY FILE(NAME = modellog, FILENAME = 'H:\MSSQL\DATA\modellog.ldf');
3> go
SQL will print out the following information letting you know your ALTER statement was successful.
The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.
Now move on to the msdb, tempdb, and mssqlsystemresource databases…
1> ALTER DATABASE msdb MODIFY FILE( NAME = MSDBData, FILENAME = 'H:\MSSQL\DATA\MSDBdata.mdf');
2> ALTER DATABASE msdb MODIFY FILE( NAME = MSDBLog, FILENAME = 'H:\MSSQL\DATA\MSDBlog.ldf');
3> go
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'I:\Data\tempdb.mdf');
2> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'J:\Logs\templog.ldf');
3> go
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
1> exit
For SQL 2005 only: Do not run the next statement on systems above SQL Server 2005. In 2005 the mssqlsystemresource db was in the default data directory, so it may need to be modified if your drive letters have changed. However, in 2008 and later versions, it is located in the binn directory so there is no need no change anything.
1>ALTER DATABASE mssqlsystemresource MODIFY FILE( NAME = data, FILENAME = 'H:\MSSQL\Data\mssqlsystemresource.mdf');
2>ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'H:\MSSQL\Data\mssqlsystemresource.ldf');
3>GO
Type this in a CMD prompt to bring SQL offline again.
NET STOP MSSQL$<instancename>
The SQL Server (<instancename>) service is stopping.
The SQL Server (<instancename>) service was stopped successfully
Start the SQL Server service, not SQL Agent, from configuration manager. We don’t want to start the SQL Agent yet because we don’t want any jobs kicking off.
You should next view the error log to ensure there are no system database errors. There will be user db errors you can ignore. Those will go away once we bring over the user db files from the old server.
Step 4- RESTORE MSDB
First, stop SQL Agent service if it is running. Now restore MSDB with replace using the source back up you took earlier, in the new standard directory structure.
If your model database has changed, you may want to back it up and restore it. We didn’t need to in our migrations.
Next, restart SQL Service, not the SQL Agent service. Review the SQL Error Log to make sure there are no problems with MSDB.
Keep track of which SQL Agent jobs are enabled and disabled. Disable all the SQL Agent jobs so you can verify SQL Agent will start after the restore. However, you do not want any of the SQL Agent jobs kicking off and running before this new server goes live. That could be bad if both the old server and the new server kick off the same jobs.
Lastly, start SQL Agent service via configuration manager.
Step 5- ALTER User Database File Locations
On the new instance, alter the user DBs with new file locations. By doing this now, you can avoid any stress or last minute questions on migration night. When migration night does come, all you will have to do is stop SQL on the old server, copy the user db files to the new server in the new directories, then stop and restart SQL on the new server. Once SQL is restarted, it will see the db files and start up the user databases.
To alter your user databases, use the same ALTER statement listed above in the restore master section of this document, but obviously change the db name and the drive location and the logical and physical file names appropriately. You can use this script to generate ALTER statements. This is helpful if you have a lot of databases migrating. Once you get the output of the script, you can put it in Excel and use replace to update the new drives and paths. I’m sure there is a way to automate that, but Excel works pretty fast too.
SELECT 'ALTER DATABASE [' + DB.name + '] MODIFY FILE (NAME = [' + MF.name + '], FILENAME = ''' + MF.physical_name + ''')' FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id WHERE MF.database_id > 4 ORDER BY DB.name
Step 6- Verify System Configuration & Other Miscellaneous Pre-Migration Tasks
The 11 items below are things you should consider carefully well before your actual migration and make the appropriate decision.
- Is your Max Server Memory on the new server configured correctly?
- CPU Settings- most likely nothing will change here and you will use the default config of the new install, but just verify to be sure that the old system isn’t using some type of specific config that needs to be carried over to the new system.
- Tempdb - Are the TEMPDB data files configured properly according to the CPU count?
- Agent Jobs - Do any of the SQL Agent jobs read or write to a different drive location than it used to? Here is an article to help with output. Here is one to help you with commands.
- Integration Services - Are there any SSIS packages stored outside of MSDB? (search for *.dtsx files on the source server to verify). If you find any, you will need to work with the SSIS owners to ensure these are copied to a location on the new server that will allow them to use these SSIS packages.
- SSIS Packages - Do any SSIS packages read or write to a different drive than the new server has?
- SQL Agent - Run SQL Agent suspended job fix. Sometimes after MSDB is restored, you may need to reset the subsystem pointers in MSDB. In order to do this, follow the steps below:
Back up the current MSDB to be safe.
Run the following:
use msdb go delete from msdb.dbo.syssubsystems exec msdb.dbo.sp_verify_subsystems 1 go- Stop the SQL Agent Service. Then Start it. We tried doing just a restart and that didn’t work so well. So actually stop it and then start the sql agent service.
- Enable broker service on MSDB so database mail works. You can verify if the broker service is enabled by running “select name, is_broker_enabled from sys.databases”. 1 is enabled, 0 is disabled. If it is disabled, you can enable it by running “ALTER DATABASE msdb SET ENABLE_BROKER with rollback immediate".
Identify the user database files that need to be copied over. You don’t want to be guessing on migration night what is supposed to come over. Run this script on the source server to identify which files to bring over. This script can also help you size the data and log drives on the new server.
SELECT DB.name AS DatabaseName , MF.name AS LogicalFileName , MF.physical_name , MF.state_desc , CAST(MF.size AS INT) * 8 /1024.0 /1024.0 AS size_in_gb§ FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id WHERE MF.database_id > 4 ORDER BY MF.database_id- Are there any shares that need to come over?
- Are there any third party apps/drivers that need to come over?
Migration Day
All of the above steps have been pre-migration steps. Now we will talk about the steps to take on the actual migration day.
- Let the clients know you are starting the migration.
- On the old instance, stop SQL Server.
- Copy the user database files you identified in the pre-migration steps over to the new server.
- Start SQL Server
- Verify all the user databases come up without issues.
- Carefully review the error log for any errors.
- Rename the old server and assign it a new IP address.
- Rename and set the IP for the new server with the old server’s values.
That should do it! Your clients and apps should now be able to reconnect without any reconfigurations since the IP and the server name and instance name and port are all what they used to be.
The Pros to This Method:
All the complicated steps of the migration will occur prior to the production migration.
For us, migration night was simple and stress free. Just stop SQL Server on the old server, copy the user db files to the new server, and restart the new SQL Server. We had no logins to move since we restored master (if any changes to master occurred after you made your initial backup of master, you will need to do a fresh backup/restore).
There were no SQL Agent jobs to move since we restored msdb (if any changes to MSDB occurred after you made your initial backup of MSDB, you will need to do a fresh backup/restore). We had no linked servers to move since we restored master.
Most of the SSIS packages were stored in msdb, so nearly all SSIS packages were restored with msdb and those that were stored outside of msdb were worked out in our pre-migration tasks with the SSIS package owners.
We had to perform no backups or restores of user databases. This can be helpful if you have a lot of databases per instance. We also did not have to update the server name in sys.servers table.
The Cons to This Method
- Production down time on databases could possibly be longer if you just copy the files instead of doing a backup, restore, then tran log restores. However, our copy speeds from old server to new was about 200GB per hour, so we could move a lot of small databases quickly with no other work, other than a copy.
- Our drive letters were different on the new VMS than on the old server which forced us to ALTER both system and user database file locations.
Final Notes
This was a challenging project and extremely satisfying. It was a great team effort from all of our DBAs and our Intel Ops, Storage, and VM Engineers. Special thanks to Joseph Mills and Eric Beus.
Skilled experienced airline pilots use checklists to ensure nothing is overlooked. I would like to recommend we as DBAs do the same. By thoughtful and careful preparation, you can avoid all kinds of risks and mistakes during complex tasks. If you are working on many different migrations at once, by keeping a checklist and marking which steps are completed with notes, it will help you from doing re-work and allow others to pick up where you left off.
