Local Volume Migration Misconceptions
Published:
Browse All Articles > Local Volume Migration Misconceptions
A frequent problem DBA have to face is the need to locally migrate database files for one of the following situations:
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_ATTACHas 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:
Let's review some of the facts related to each method:
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.
- 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
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
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (7)
Author
Commented:Commented:
Provided that you are not migrating the databases to a different server, you are better off just taking the databases offline and moving them, before placing them online again. This, in my view, is a far simpler approach and less error prone.
Author
Commented:Hope you are doing all right.
Of course you are correct about the PROS / CONS part but that is not really the point I am trying to make here. The real hidden subject name here is : How to avoid a bias, think about it twice then choose the most appropriate technique depending on your context.
A little history on where this article came from...
Last month, I realized I never posted a single article about SQL on EE so I (shamefully) asked myself: What operations are frequently done (hence can be assumed to be useful) on SQL Server and could require clarification ? I created a list of article topics I could post onto without getting bored and looked for a theme I could follow. I came to think that the use of specific administration techniques tend to subconsciously create biases in ourselves until we face a situation where it does not work. Space management is one of them. Is that a fatality ? <the purpose of the article is to answer no.
For instance, assuming that databases should *de facto* be put OFFLINE is a bias. Why assume databases should be put OFFLINE in the first place ? Do we have to ? Are the reasons to put the databases OFFLINE justified ? Bingo I found my many-articles theme.
In this article I wanted to share perspective on a situation I faced in 2008 in Dublin Ireland. I had a 30TB databases to administer with huge data growth and refresh rate. These databases had to be ONLINE all the time and we added new drives all the time to cope with phenomenal data growth and performance management. (with partitioning management)
Since I had several hundreds files I had to locally juggle with data location all the time. Therefore, I had to think out of the box in designing IO and time effective local space management techniques. Due to multiplicity and size of files, and availability requirements , moving files with BACKUP/RESTORE simply was not an option. This is where I started considering my own previous bias of always doing backup restore stuff when I think of space. Due to my specific context, I started reorganizing data physically by adding new files and transferring data pages as a mean of control of performance and space.
The situation forced me to do so. But I did it in pain. Later on, I started even looking with SAN migration capabilities to perform switch operations.
Among other things, I had a shock in discovering that it was far easier to use FILEGROUPS native capabilities to move data around than to use the BACKUP/RESTORE who has clear limitations, which are sometime unacceptable. I now use this technique all the time and I do not have to think of planning OFFLINE operations. Locally, the FILEGROUP ADD FILE/DBCC SHRINKFILE/DROP FILE/RENAME does everything BACKUP /RESTORE does only at lesser IO costs and downtime. + The technique gives me a better sense of where the data resides since I keep moving away data pages thanks to the help of the engine. My previous bias was replaced with another new more general bias, I find more effective when accustomed to.
But perhaps that is just me. Anyway, I appreciate your comment and it is always good to hear from you. I wish you a merry Christmas and Happy New Year.
Commented:
Happy New Year.
Author
Commented:As a Christmas gift, here is a spoiler for you...
Not only BACKUP/RESTORE is not always the most preferable technique for moving data locally but it is not either always the most appropriate technique to do it in a LAN...There are much faster techniques when availability and file size becomes an issue on my next article.
Best Regards...
View More