How to Move Application Databases from One SQL Server to Another

Moving databases from one SQL server to another is not rocket science. The key thing is to make sure you plan everything in advance.

It can be done with minimal downtime.

This is a high level article that can be used like a reminder or checklist. I assume you already have SQL DBA knowledge.

Image: Flickr

Migration process

Prior to the move

  • Ensure the application is supported on the environment you are moving it to. Failure to do this can cause big problems.
  • Create scripts to perform the backup and restore operations.
  • Plan each step that you will need to take.
  • Create the users on the new server. When you move SQL logins over you need to extract out the original hash and password to prevent orphans. To extract SQL logins use this – the official MS login hash extractor
  • Make sure user permissions are copied across. ALL user permissions not just some. If you have any doubts about this then spend more time figuring it out.
  • Track database usage. Prior to the move you should be checking exactly who and what is connecting to the database you are planning to move. I normally use something like this and make sure I am recording the hostname, and the loginname of any connections.
  • Check the collation of the new server vs the old. Your application needs to work with this collation.
    -------------------
    SELECT CONVERT (varchar, SERVERPROPERTY('collation')); 
    -------------------
    
  • Make sure you have allowed enough disk space (and other resources) on the new server for current + future growth of the databases.
  • Create a rollback plan. Know exactly what you need to do to get things back to how they were.
  • Make sure there are no encryption, certificates or master keys involved. If there are make sure you factor this into the process.

Actual Move

Make sure you have notified everyone that you need to.

If the application vendor advises to and you have an outage window you can shut down the services/applications.

On Old Server

  • Close off any connections to the the database then make it read only. (This will ensure that no data is lost in the transition)
  • Backup Database


On New SQL Server

  • Restore Database
  • Take DB out of Read only
  • Optional if required, Change DB owner to sa –EXEC sp_changedbowner ‘sa’
  • Change compatibility if required
  • Run DBCC checks
  • Add to backups and maintenance plans

Application Servers
Follow the vendors instructions as to changing connection strings and restarting services. Make sure you save the old settings so you can easily roll back if required.

If you come across something that is different to the instructions stop, check and make sure you understand exactly what it is, why it is different and how to deal with it.

If you don’t then rollback and do more research until you really get it.

Troubleshooting

After Move

  • Check the application is working and connecting to the new database server.
  • Take the old DB offline. I recommend that you keep it in that state for at least a few days before deleting it.
  • Ensure backups and maintenance is working.
  • Update any build docs/documentation
  • Check SQL error logs on the new and old SQL instances for any errors. Check the event logs of the application servers to ensure there are no errors
  • .

Summary

Database migrations can be seamless. Put a lot of effort into the planning. Take care with execution.

Remember this:

Give me six hours to chop down a tree and I will spend the first four sharpening the axe. – Abraham Lincoln

If there are any other gotchas that could be helpful to mention, please leave us a comment.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply