Database Migration

I have been thinking of what should be my first post and this immediately pop up as this is what I have been doing for the past 6 months.

We have projects on replacing all old servers in all our data centers with new servers and as such we need to move all databases hosted on the old server to new server with minimum impact and downtown to the users. As most mission critical databases contain financial information, we also need to ensure there is no data loss and also modification to the data in the databases adhere to SOX compliance requirements.

There are multiple ways to deal with database move, backup/restore, detach/attach and SSIS are among the most frequently used method by most DBA. However the challenge is that there are about 90 databases, size range from 5-20GB each, hosted on one SQL instances for example. Think about questions below:

1) How long do you think it needs to complete the move using above method?
2) What is the rollback plan if the databases don't work on new server, are you going to use the same method to move the databases back to old server?
3) What will be the total downtime especially with rollback?

The method that I used is actually to setup database mirroring on all databases. Old server is the principal and new server is the mirror. I normally set this up a week prior to the official move date. Once I setup mirroring, I will also start collecting performance baseline information on new server. This is to ensure the performance of the databases will not be impacted. All of these steps can be done anytime without any outage.

During the official switch date time, what need to be done is just failover the databases to mirror server. The outage is essentially the failover time. If thing went wrong, the rollback is just to failback to old server.

With this method, the overall outage that I have for the SQL instance above is less than 5 minutes. As it is high safety mirroring, we are confident on the integrity of the data moved to satisfy audit requirement.

I hope this post is useful and give you an extra option if you are working on database migration project. This might not suitable for all migration project, careful planning and testing should be perform prior to implement this. Please let me know if you use any other alternative method before that actually works quite well, I am interested to learn that. Thanks.