A friend of mine recently moved from New York to California. As I talked with him, I remembered my own move from New York to Chicago and everything that was involved. One of the biggest factors in how we decided to move was time. Initially, when I was planning on moving back to Chicago, the new company wanted me to start ASAP, and the time from my last day at my old company to the first day at my new company was just a few days. So, our initial plan was that we needed to take a plane and probably ship all our stuff. But, then I convinced them to give me more time and we were able to drive from New York and bring most of our stuff, although we did ship a few items.
Needless to say, when a person has to move from one place to another, how he does it depends on the amount of time involved. Similarly, when we are moving things in the database world, such as migrating databases, how we do it depends on the time we have available to do it. And really, how much time we have is a business decision. Of course, it is always better if we have more time, but that is not always the case.
Why may you have to move databases? Well, two basic reasons come readily to my mind. Perhaps you are moving all your databases from one server to another because of using new hardware or an upgraded version of SQL Server. Or maybe your company just purchased a new storage device (i.e. SAN) and you have to move all your databases from one drive to another.
Granted, there are a lot of different methods we could use for a database migration. I am just going to focus on some of my preferred ways and I am going to categorize them based on the amount of time you have to handle the migration. Also, I am NOT going to discuss all the other things that may be necessary for a successful database migration, including moving logins, jobs, configurations (i.e. alerts, operators, sp_configure settings), re-pointing your application to the new server, or even running maintenance after the move (i.e. index and statistics maintenance, integrity checks). But, certainly, you would want to give attention to all these things.
If the database(s) can only have minimal downtime, then …
The more complicated situation of migrating databases is when the business says that the database(s) can only have minimal downtime. So, what can we do?
- Log shipping (http://msdn.microsoft.com/en-us/library/ms187103.aspx) – This option can be used when migrating to a new server. The basic idea is that a database has its transaction logs backed up and shipped to a remote server and applied at specified intervals. Thus, this remote database is up-to-date to a certain point in time. When you are ready to do the cutover for the migration, you take one last log backup and restore it to the remote database. Finally, you recover the remote database and the migration is done. The amount of downtime you will have in this scenario is the amount of time it takes to do the last log backup and restore it to the new server.
- Database mirroring (http://msdn.microsoft.com/en-us/library/ms189852.aspx) – This option can also be used when migrating to a new server. Database mirroring is similar to log shipping in the sense that transactions are applied at the remote database. However, one of the differences is the timing. With database mirroring, you can have those transactions applied immediately (synchronous) or with some lag (asynchronous). The amount of downtime you will have in this scenario is the amount of time it takes to bring the mirrored database online. So, generally this may have less downtime than log shipping. However, Microsoft is deprecating this feature in a future release, so it would not be my first choice.
- Third-party tools – There are also some third-party tools that can migrate databases to a new server or new drive with minimal downtime. For example, Vision Solutions has a product called “Double-Take Availability” (http://www.visionsolutions.com/Products/DT-Avail.aspx), which migrate byte-level changes between storage systems asynchronously. The basic idea is that you begin synchronizing the database files (i.e. mdf, ndf, ldf) between the two storage systems. Then, when you are ready to cutover, you attach the database files to the database server. Thus, the amount of downtime you will have in this scenario is the amount of time it takes to attach these new database files. And there could be other third party software that does something similar, but this is the only one I have ever used.
- Filegroup migration (http://www.sqlphilosopher.com/wp/2012/02/moving-a-filegroup-to-a-new-disk-array-with-no-downtime/) – This option can be used when migrating to a new drive with no downtime. The basic idea is that you empty all the data files for your database into one file. Then, you create new files on the new drive. Then, you empty the file from the old drive into the ones on the new drive. This can be intensive on your server, but you will experience no downtime.
If the database(s) can have a longer outage, then …
If you are not under a time constraint, the following options are the simplest.
- Microsoft’s recommendations (http://support.microsoft.com/kb/314546)
- Backup and restore database(s) – This option can be used when migrating to a new server. The basic idea is that you will backup the database and restore it to the new server. If your database is in FULL recovery model, you can also do a transaction log backup and a restore to reduce the downtime needed.
- Detach and re-attach – This option can be used when migrating to a new server or a new drive. This method is quite simple, you detach the databases from the server. You copy them to a new server or even a new drive. Then, you re-attach them. The amount of downtime needed will be largely based upon the length of the copy and detach and re-attach operations.
- Third-party tools – There are also some third-party tools that can migrate databases to a new server or new drive. One tool I like is from Idera and it is called “Database Mover”, which is part of the “SQL admin toolset” (http://www.idera.com/productssolutions/sqlserver/sqladmintoolset). It basically does a detach of the database, copy of the database files, and re-attach of the database. Additionally, it will copy the logins, which is a nice added feature. However, if you have a lot of database moves to do, this may not be the tool to use. You will probably want to write a script that uses one of Microsoft’s recommended ways.
Hopefully, this guide can help you determine how to handle your database migrations based on the amount of time you have available.