Why is disaster recovery an afterthought?

AaronBertrand — Sun, 21 Jun 2009 16:44:00 GMT

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.

Search results matching tags 'SSMS' and 'disaster recovery'

Why is disaster recovery an afterthought?