Local Volume Migration Misconceptions

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT
Published:
Updated:
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.
3
948 Views
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Comments (7)

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Author

Commented:
Sorry about that...Wrote the article late...Thanks for your help...
CERTIFIED EXPERT
Top Expert 2012

Commented:
The big problem with sp_attach_db/sp_detach_db is the potential you will lose database settings such as SET TRUSTWORTHY and DB_CHAINING.  You also should not be using sp_detach_db in the first place as it is deprecated, you should use CREATE DATABASE instead.

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.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Author

Commented:
Hi Anthony,

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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
I think I see what you are saying now and yes, I see your point.  Of course, this approach is only meaningful if you are not moving to a different box.

Happy New Year.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Author

Commented:
My next article will be *LAN Volume Migration Misconceptions*...

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

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.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community