Technique to Migrate large databases and minimising downtimeIntroductionMigrate Large Databases - The purpose of this article is to describe a technique to migrate large databases, this process involves a manual log shipping process that will help you minimise business downtime when migrating mission critical databases to new hardware or new versions of SQL Server. Methods of Moving DatabasesThere are several ways to migrate/move databases between different servers and instances of SQL Server including the backup and restore method, making a backup of the database on the source server, copying over the backup file to the new destination server and restoring the database there. You can also go down the route of detaching a database on the source server copying the database files (mdf, ndf and ldf) to the new destination server and the reattaching the database on the new destination server. SQL Server also ships with a copy database wizard although I have to admit, due to the size of databases i have worked with, I have never used this last method to move a database. Backup and RestoreThis article will focus on the backup and restore method to migrate large databases and utilising a manual log shipping process to keep the database downtime/unavailability to a minimum. Migrate Large Databases - Manual Log Shipping ProcessPre-requisitesFor this article it is important that readers understand how to backup and restore databases. If you need guidance with this, please these articles: Backup Restore Before we begin to look at this method in more detail it is import to state the database you will be migrating needs to be in full recovery mode with regular (i have assumed at least hourly for this process) transaction log backups taken. It is important that regular full database backups are taken of the production database.
Migrate Large Databases - Manual ProcessA couple of days before the planned switch over, copy the full database backup across to the new server. Once you have the backup on the new server, restore the database from the backup file ensuring that all database files mdf, ndf and ldf are restored to the correct location and leaving the database in a recovering state so that transaction log backups can be applied. The next step is to copy over all the transaction log backups that have been taken since the full backup was restored. You need to ensure that every transaction log backup taken on the current production database is copied to the new server and restored; obviously this can be done in stages. If there is three days between the full backup being taken and the cut over to the new server, and the transaction log is backed up on hourly basis, then each morning before ‘go-live’ day I usually copy over the previous days (24 hours worth) logs. I then restore each one leaving the database in recovering state so the next batch of log backups can be applied. Now I have always worked on high available systems, usually 24/7 systems, so on the day of go-live the first thing I do is restore all the previous days backups. Then throughout the day I copy over restore the transaction log backups as they are taken or in batches of two. By the time we are in the last hour before switchover i like to ensure that all logs have been applied to the database on the new server and all is left to do during the downtime is copy over and apply the last log backup. take the old database offline, but leaving it on the old server for failback purposes. Change the application config/connection settings to point to the new db server. Enable the SQL agent jobs on the new server and we are ready to go.
Migrate Large Databases - Precautions to takeEnsure that the database is left in a recovery state after the full database restore so further logs can be applied. Restore the backup and logs as soon as you can, that way if any of the files get corrupted in the copy process you will have time to copy them again. I actually had this problem with the full backup file, but we had allowed enough time to re-copy the file. I found copying the full backup, which is likely to be quite large, was best during off peak hours, i had to try and avoid the backup window too, all i’m sayingis try and time the copy for non-peak periods.Ensure all jobs that are needed are copied and disabled before the go-live day but disable them and enable during the cutover period or when necessary. After the last log has been restored on the new database take the old database offline ensuring nobody can connect to it by mistake which also means you can bring it back online if you need to rollback. If this is used to migrate SQL Server 2000 to 2005 or 2008 you won’t have long to do this though as it will mean losing any data added since the migration. If you are moving to the same platform on new hardware then this can be an ideal backup plan.
Conclusionin summary when I migrate large databases I follow this process
If you enjoyed this article discussing how to migrate large databases then signup for the GRE newsltter and keep informed of the latest site updates.
|
