When it comes to large databases, the performance and stability of the storage layer are of utmost importance. However, upgrading the storage layer without impacting the servers can be a challenging task. One of the major concerns during a storage migration is the downtime required to copy the database files, which can take several hours. In this article, we will discuss how to minimize downtime for larger databases during a storage migration.

Migrating Small Databases

For small databases, the migration process is relatively simple. The following steps are typically followed:

1. Attach the new storage to the SQL Server instance with different drive letters or mount points.
2. Stop the SQL Server Service.
3. Copy the data files to the new disks with the same directory structure.
4. Swap the disks and start the SQL Server Service.

This process can be applied to migrate a list of databases on the SQL instance, including system databases.

Migrating Large Databases

When dealing with large databases, the time required to copy files can be significantly longer, resulting in a larger downtime window. To reduce this downtime, we can utilize SQL Server Log Shipping. Here’s how it works:

Before starting the migration, there are a few tasks that need to be completed:

1. Attach the new storage to the SQL Server instance with different drive letters or mount points.
2. Take a full backup of the large database and restore it in the same SQL instance with a different name.
3. Set the recovery mode of the new database to “No Recovery”.
4. Ensure that the data and log files of the new database reside in the new storage disks with the same directory structure as the original database.
5. Set up Log Shipping from the original database to the new database.

Once the setup is complete, allow Log Shipping to run until the planned downtime window starts. During the downtime window, the following tasks need to be completed:

1. Restore the final log backup.
2. Stop the Log Shipping.
3. Set the recovery mode of the new database to “Recovery”.
4. If the new database is a replicated database, ensure to set the “KEEP REPLICATION” setting.
5. Stop the SQL Server service.
6. Swap the disk drive letters.
7. Start the SQL Server service.
8. Remove the old disk drives from the cluster.

At this point, the original database will point to the data and log files residing in the new storage device, while the new database will point to the data and log files residing on the old storage. Verify that the databases are functioning properly and if everything looks good, confirm the end of the downtime window.

To rollback the migration, simply follow the same steps to swap the disks back. If the databases and applications are working fine, it is possible to drop, detach, or keep the new database as necessary.

Conclusion

By utilizing SQL Server Log Shipping, it is possible to minimize the downtime required for migrating the storage of large databases. In the example provided, the downtime was reduced to approximately 30 minutes, compared to the 4+ hours it would have taken to copy a 4TB data file. This method can significantly save time and ensure a smoother storage migration process.