June 21, 2009 | SQL Server

Why is disaster recovery an afterthought?

I think that people have been lulled into the false sense of security that you can set up a SQL Server database, leave all the defaults in place, and never have to do anything.  While it is true that Microsoft has added many features to SQL Server that make the "hard" parts of being a DBA easier, there is no question in my mind that many people have over-compensated and adopted an attitude of "set it and forget it."  I can't really blame them, I guess, but I am still amazed when these same people get bitten by lack of maintenance or planning, and have no idea how to resolve the situation.

The most common scenario is when users first set up a database and accept the defaults (e.g. FULL RECOVERY), because it sounds good and must be the best option.  They may or may not set up full nightly or backups (or that may be "someone else's job"), but very rarely do they understand the importance of log backups.  So a few weeks or months go by, and everything seems to be working great, and then all of a sudden the drive that their log is on runs out of disk space.  (And as an aside, this is often C:\, and is often also the drive that their data is on.)  This is because their data file has only grown to 500 MB, but their log file is now 40 GB.  I wish I was exaggerating, but I see this all the time.  And in most cases these people are looking for the "quick fix" : tell me how to shrink the file, so I can have my space back, then I'll go back to ignoring the situation.  They don't realize (and don't want to understand) that shrinking the log file in and of itself is not going to fix anything.  It gives them temporary breathing room, but that just means they are going to have to run the SHRINKFILE command in a repetitive loop, days or weeks apart.  Tibor has some great advice about this here and here – the latter is a bit long but, trust me, it is really worth the read.

For a long time I tried to figure out how people got into these situations in the first place.  Some of them are involuntary DBAs, for sure – they need an app, which needs a database, and they can't become a full-fledged DBA just to support this one app, and they can't always predict their long-term file growth needs or understand why they may need disaster recovery or how it can be accomplished.  I then realized that SQL Server actually encourages this behavior in the "New Database" wizard.  Since I always use CREATE DATABASE DDL to create databases, I didn't realize that the UI populates so many defaults for you (though, granted, some of those defaults are the same if you use DDL and omit those options).  Why can't SQL Server be more helpful in this case?  Most of these involuntary DBAs are using the UI to create a database, and the defaults are horrible, if we're being honest.  Here are a couple of ways that I think the defaults could be much better:

General tab

 

Initial Size

2 MB data file, and 1 MB log file?  I guess this is okay if you are planning to store a single table that never grows.  But for most applications, this is not going to be sufficient, and you are going to start experiencing autogrowth events almost immediately.  And if your data is growing significantly, both your data and log files are going to have these events occurring quite frequently.  Why would you want to make your transactions wait for these events all the time, instead of picking a better starting size?  Couldn't the default be generated dynamically by some formula of free space on the drive, the size of other databases on the system, and globally observed usage practices?

Autogrowth

1 MB for data, 10 percent for log?  Terrible.  I think this should be dynamic and based on the initial size you specified for the files, and % should never, EVER, EVER be a default for data or log.  The problem is kind of like doubling your kids' allowance every week; eventually, it will put you in the poorhouse.  If you grow by 10% every time, that chunk gets bigger and bigger, and unless you are on zippy SSDs or similar, the growth event takes longer and longer – even with instant file initialization.  Of course all transactions have to wait for this file growth to complete, so making that period more predictable is in your best interests.

Path

Personally, I think the drive letters should be dynamically selected based on free space available, or even better, if an HBA is detected or a SAN is otherwise present, users should be reminded that that is likely the best location.  If you still choose smallish drives (and especially C:\), this should pop up a warning, giving some guesstimate about how long the database will "survive" on this drive if the data grows at, say, 1MB/day or 10MB/day.  Same for log.  This may help to prevent some people from just plopping data or log or both on a small C:\ when there is a much bigger D:\ or E:\ available.  Of course C:\ is the default based on the initial installation of SQL Server, and they are getting better about this in the setup wizard, but have a long way to go.  There should be much better guidance on placing data and log on different drives.

Options tab

Recovery model

This is the big one, in my opinion.  The option to place the database in full recovery by default should be accompanied with, at the very least, a reminder that disaster recovery is important.  Also it could be useful to explain WHY full is preferential, and a link to documentation describing the different recovery models (and why log backups are important).  It would be better to have a tab that allows them to set up both a full and log backup schedule, before the database can even be created.  By default the schedule could be a full backup every night at midnight, and log backups throughout the day, every 15 or 20 minutes.  This should be a very easy set of options and should work much more predictably than the maintenance plans (the main problem with maintenance plans is that when you add a database not all plans automatically pick it up).  Finally, the backups should *not* be allowed to go to the same drive as the data or log; another poor default in the maintenance plan wizard.  There should actually be a way to provide SQL Server with a default network share for backups for all new databases.

So why is disaster recovery an afterthought?  Part of it is a need for better education.  But part of it is because the defaults encourage people to use techniques which are nowhere near the realm of "best practice."  Personally I think some very minor tweaks to the "New Database" wizard could make some giant strides in reducing the number of people who get surprised and burned by ginormous log files.

File Attachment: ndw.zip

9 comments on this post

    • Glenn Berry - June 22, 2009, 2:19 AM

      Great post Aaron!  We have all seen the 500MB data file with the 50GB log file, both on the C: drive. SQL Server 2008 is a little better if you know enough to set the default locations to somewhere besides C:, but the average non-SQL "I.T. Guy" who is probably installing SQL Server 2008 for the accidental DBA is usually just using the defaults for everything.
      At the very least, it would be nice for the SSMS UI to pop a warning when you try to create a new database with the data file and log file on the same logical drive.

    • jchang - June 22, 2009, 3:39 AM

      Why is disaster recovery an afterthought?
      1) I will be dead, and it will be the other guys problem
      2) I never liked the old server room
      3) I was planning on asking for a raise then and wanted to be sure I was going to have the bossed undivided attention.

    • gbn - June 22, 2009, 10:42 AM

      Initial sizes, autogrow and recovery model are taken from the model database. This would need changed…

    • JLangston - June 23, 2009, 6:07 PM

      I like Joe Chang's sense of humor.
      Disaster recovery isn't sexy and is a bit like how some think about insurance ("It will never happen to me").

    • Steve Malley - June 28, 2009, 7:06 PM

      It is amazing how many people do not know how to use the recovery models and logs. I actually lost a contract recently because I tried to provide 'the boss' with suggestions how to fix log problems. Can you believe it? I can't.

    • Tim White - June 29, 2009, 4:42 PM

      Personally, I am against changing the defaults.  Anyone who is responsible for setting up a database in any size shop should take just a little time to learn about how files and backups work.  There is no single turn-key option that will work for everyone.  You have to know (at a least a little) what you're doing, or hire a professional that does.
      "Full recovery" or "Simple Recovery" does'nt matter if you don't know the difference.
      Changing the defaults only means DBA's will now have to go through and modify what they do to account for the simpleton defaults.

    • Aaron Bertrand - June 29, 2009, 4:57 PM

      Sorry Tim, but I disagree.  The main problem is that people have demonstrated over and over again that they are absolutely NOT willing to read about (never mind understand) file growth or recovery.  Which is why the problem crops up so often.  And this won't go away anytime soon as SQL Server is being marketed to more and more people, and is included in many more products than, say, 3-5 years ago.
      Surely you're not suggesting that anyone in the world could benefit in some way from having a 1mb log file that autogrows at 10%, and is never encouraged in any way to establish a decent backup plan?  Remember that some of these people are installing sharepoint, wsus etc. and have little to no idea that SQL Server is a part of it.

    • Tim White - June 29, 2009, 6:10 PM

      Thanks Aaron, your articles are excellent, as always.
      Just trying to protect my job security 😉

    • Sashikanta Mishra - June 30, 2009, 8:08 AM

      Aron, i would like to say you that,your articl just saved all my time explaining to others in my org the necessity of having a needful recovery plan.And thanks all of the guys giving valuable suggestions and appreciations, those only helped me.
      Regards!!

Comments are closed.