Recently a client brought me a challenge. They wanted to more a database from one LUN to another. So far this is pretty straight forward. The trick here is that this is a multi-TB instance with several databases which are about 1TB in size, and the instance has a two hour per night maintenance window.
So the trick here is how to move all the databases (including the system databases) to the new disk within the acceptable outage window. Making things more complex, the database instance is a Clustered SQL Server 2014 instance.
Log shipping would work, but that’s a pretty complex migration strategy for a simple LUN migration.
The easiest solution in this case was to add new database files and let SQL Server move the data. How I did this went something like this.
1. Mount the new LUN to the cluster and format it. In this case as the F: drive (the databases are currently on the E: drive).
2. Robocopy everything except for the database files to the new drive. This has the nice side effect of creating all the folders which are needed.
3. Add a new data file for each of the large databases on the F: drive.
4. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the main data files to the new data file.
5. Once the DBCC SHRINKFILE command is done (it actually throws an error because the system objects can’t be moved). Shrink the main database files down as small as possible. I got most of them down to a few megs in size.
6. Change the auto growth settings on the existing file to be disabled so that it can’t grow beyond this small size.
7. Repeat this for all the databases in question.
At this point we wait for the maintenance window.
8. Stop the SQL Server service.
9. Robocopy the database files to the new drive.
10. Remove the drive letter from the old volume.
11. Change the drive letter on the new volume to the new drive.
12. Start SQL Server
13. Log into SQL Server and use the ALTER DATABASE … MODIFY FILE command to change to filename from the new drive letter to the old drive letter.
14. Restart SQL Server
15. Use the DBCC SHRINKFILE command with the EMPTYFILE parameter to remove all the data from the new data files to the main data file.
16. Drop the new data files from the database.
Using this approach I was able to move several terabytes of databases from one LUN to another with about 20 minutes of total outage to the system.
Now this does require that there’s a good amount of free space on the new volume as you have to be able to have the two database files on the volume at the same time. But a little drive space is a small price to pay for a short maintenance window.
Denny
