A frequent problem DBA have to face is the need to locally migrate database files for one of the following situations:
- The volume gets too small for accommodating data file sizes growth .
- The volume gets too small for accommodating log file sizes growth.
- The need to speed up queries by putting them on a faster IO volume. By faster, I mean SSD or RAM.
- The need to isolate a specific database files from other files for security(ex: encryption), performance (IO contention isolation)
Looking at the above frequent situations, I am amazed at how many DBA tend to have a bias
to immediately think of same-host database migration as an OFFLINE operation and assume of BACKUP/RESTORE WITH MOVE, SP_DETACH/SP_ATTACH
as safe and better ways to do that. These are two big misconceptions a recent thread urged me to clarify .
Let's review frequent available methods, namely:
- BACKUP/RESTORE WITH MOVE option: consists of having an OFFLINE database backed up, then restoring the backup on a new volume (and new drive)
- SP_DETACH/SP_ATTACH : consists of having an OFFLINE database file detached and copied on another drive then reattaching to the database
- ADD FILE/DBCC SHRINKFILE(EMPTYFILE)/DROP OLD FILES/RENAME NEW FILES TO OLD FILES: consists of creating new files on the new drive then giving the order to the engine to migrate all data pages from the old files to the new files. Once data pages are migrated, simply drop the old files and rename the new files to have the old file's name.
Let's review some of the facts related to each method:
- BACKUP/RESTORE and SP_DETACH/SP_ATTACH are OFFLINE operations
- BACKUP/RESTORE takes twice as many physical IO's as SP_DETACH/SP_ATTACH or ADD FILE/DBCC SHRINKFILE(EMPTYFILE)/DROP OLD FILE/RENAME OLD FILES methods. On BACKUP/RESTORE method, the operation requires reading twice (to perform backup and read it at restore time) and writing twice (one for making the backup and one for extracting the files from the backup). The other methods require one write and one read physical IOs per database.
In conclusion, ADD FILE/DBCC SHRINKFILE(EMPTYFILE)/DROP
OLD FILE/RENAME OLD FILES is the only method, among the three above to perform ONLINE migration (ok it will slow down things) at a lesser
IO cost. Taking the habit of expanding space by adding files and moving data locally that way will hopefully greatly simplify your life.
And when you think about it, this is exactly what a database system is all about: making maintenance operations as transparent as possible
to end users.
Hope this helps.