<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Local Volume Migration Misconceptions

Published on
4,061 Points
761 Views
3 Endorsements
Last Modified:
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
Comment
  • 5
  • 2
7 Comments
LVL 23

Author Comment

by:Racim BOUDJAKDJI
Thanks...
0
LVL 23

Author Comment

by:Racim BOUDJAKDJI
noticed a few typos corrected.
0
LVL 23

Author Comment

by:Racim BOUDJAKDJI
Sorry about that...Wrote the article late...Thanks for your help...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LVL 75

Expert Comment

by:Anthony Perkins
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.
0
LVL 23

Author Comment

by:Racim BOUDJAKDJI
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.
0
LVL 75

Expert Comment

by:Anthony Perkins
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.
0
LVL 23

Author Comment

by:Racim BOUDJAKDJI
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...
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month