Archives

SQL Server Maintenance Plans for Small/Mid-sized Businesses

I get a ton of questions and calls about SQL Server Maintenance issues from Administrators who just want their server to work and be protected. They’re not interested in the intricate structure of a transaction log, how to use wait stats, etc. While many database administrators who read this article will disagree or perhaps outright flame me, (cough Sean McCown cough :)) I feel that SQL maintenance plans are a very effective means for non-dbas to protect their data.

Rather than recreate tutorials that others have already written and take the same screenshots they provide, I’ll link you to them and suggest settings that are applicable to most M2M companies. Keep in mind that every situation is different, what I’m offering are very general guidelines.

Maintenance Plan Tutorials

The simplest way to maintain a SQL Server is to use the Maintenance Plan Wizard. Please select a link to instructions for your server version. Each site has an easy to follow tutorial. These types of tutorials will explain the purpose of most settings, but will not make recommendations because so many factors come into play.

Database Mail

Before moving on to the guidelines, make sure you also have database mail set up for notifications. A Maintenance Plan isn’t any good if it isn’t running successfully. Unless you want to manually check the server each day, you’ll need to set up notifications.

For SQL Server 2000, there are a couple of ways to do this neither of which is particularly appetizing. The easiest way is to use Outlook on your server. However, ideally you shouldn’t be running anything but SQL Server on your SQL Server (more on that in a future article). You can send mail using stored procedures using SQL Server but I don’t expect that many of you would want to deal with that. It’s a mess and also a perfect excuse to nag your boss to approve a SQL Server upgrade.

SQL Server 2005 and 2008 have mail capability built in and it’s very easy to set up. Visit the following sites for complete walkthroughs:

FAQs and Guidelines for Small/Mid-sized Companies

Alright, now that the groundwork is out of the way, my recommendations follow. Companies which use M2M tend to be small to mid-sized. Most M2M databases are between 5 and 100GB and with data smaller databases like these, performance isn’t a huge issue. These guidelines assume that you fit this profile. The following questions are frequently asked of me, and most of them are business related, rather than technical.

Which recovery model should I use?
I respond to this question with a question. “How much data can you afford to lose?” If you are willing to risk losing a day’s productivity, then Simple Recovery Model is fine and you won’t have to worry about transaction log backups. However, if you need better protection than that, use the Full Recovery Model. I have some databases set to the Simple Recovery Model because I simply don’t care about them. One example is WebSense. Would it be the end of the world if I lost 24 hours employee web surfing records? Not really. I wrote a more detailed explanation of recovery models here.

How often should I do transaction log backups?
Once again, how much work can you afford to lose? Most of the M2M sites I’ve dealt with which use the Full Recovery Model, schedule transaction log backups every 15 to 60 minutes. Keep in mind that whatever time span you set for this represents the maximum amount of work you can lose. Get your backups (full and t-log) off the server as quickly as possible. I’ve looked at more than a few M2M Servers where the backups were kept on the same drive volume as the database. This is a waste of time.

How long should I keep backups?
I hate to keep echoing questions back at you, but how long do you need them? A couple of the companies I do business with keep their full database backups almost a year. However, they move them to tape every day and only have one or two copies internally. Read further about backup options for details.

Do I have to worry about SQL Database Integrity?
I often receive blank looks when I mention integrity checks. M2M recommends moving from Visual FoxPro to SQL Server because it is more stable. However, that doesn’t mean you can’t get data corruption. I recommend checking database integrity before your nightly full backup. If any database fails an integrity check contact a qualified database administrator to deal with it. Unlike the old days of re-indexing, purifying, and packing Visual FoxPro tables, SQL database integrity failures can be a serious problem. If you decide to try to handle that yourself, look for articles written by known experts such as the great Paul Randal. While we’re on the subject of data integrity, make sure to check that the maintenance plan verifes the integrity of the backups as well. However, you still need to perform test restores to be absolutely sure that your backups are sound.

Do I need to re-index my SQL Databases?

Once again, when I answer this question I am assuming that you have a database between 5 and 100GB and have decent server hardware as well. The answer is yes, you should index your SQL Databases. I index mine on weekends, but I could just as soon index it on weeknights as it doesn’t take much time with a database that small. You won’t notice much a difference in performance after indexing either as M2M is not heavily indexed. Since time isn’t a factor here, set your indexes to rebuild the indexes which effectively drops and then recreates them. Also, go ahead and update statistics as well.

Can I shrink my database and make it smaller?
No, do not ever shrink your database unless you have a compelling reason to do so. Unless your database grew for a specific reason do not shrink your database because it will then have to grow again. These processes are expensive and unnecessary. A typical scenario which may require a shrink would be an over-sized transaction log which went without a backup for a long time. You can purchase that shirt here.

How often should I perform a test restore?
Do you have a good backup? Are you sure??? You know that scene in My Cousin Vinny where Vinny and Mona Lisa are arguing about the dripping faucet?

Mona Lisa: Did it ever occur to you that it could be turned off and drip at the same time?
Vinny: No, because if you turned it off, it wouldn’t drip.
Mona Lisa: Maybe it’s broken.
Vinny: Is that what you’re saying? It’s broken?
Mona Lisa: Yeah, that’s it; it’s broken.
Vinny: You sure?
Mona Lisa: I’m positive.
Vinny: Maybe you didn’t twist it hard enough.
Mona Lisa: I twisted it just right.
Vinny: How can you be so sure?
Mona Lisa: If you will look in the manual, you will see that this particular model faucet requires a range of 10-16 foot pounds of torque. I routinely twist the maximum allowable torquage.
Vinny: How can you be sure you used 16 foot pounds of torque?
Mona Lisa: Because I used a Craftsman model 1019 Laboratory edition, signature series torque wrench. The kind used by Cal Tech High Energy physicists, and NASA engineers.
Vinny: In that case, how can you be sure that’s accurate?
Mona Lisa: Because a split second before the torque wrench was applied to the faucet handle, it had been calibrated by top members of the state and federal Departments of Weights and Measures, to be dead-on balls accurate. Here’s the certificate of validation.
Vinny: “Dead-on balls accurate”?
Mona Lisa: It’s an industry term.

The scene is funny, but the point is valid. How can you be sure that your backups are “Dead-on balls accurate?” Perform test restores. If you haven’t performed a test restore then the backup cannot be relied upon. I can hear the question forming in your mind, “Do I have to perform a test restore every single day?” No, I don’t expect you to check every single backup to verify that it restores. However, once or twice a month isn’t out of line. After all, with the size of most of our databases, it only takes 10 to 15 minutes to perform a test restore anyway.

In future articles, I’ll show you how to perform database restores using both full and transaction log backups. I’ll also document the system I’m currently using. I backup both a SQL 2000 and 2008 R2 database server using t-sql scripts, do-it-yourself compression and encryption, as well as nightly test restores. It will take me awhile to document, since I have to re-set all of that up from scratch at home. I’ll try to post these articles before Consona Connect.

Any questions? Did I forget anything? Any disagreements?

Related posts:

4 comments to SQL Server Maintenance Plans for Small/Mid-sized Businesses FAQs

  • Judy G

    Terrific article, good tips and what a wonderful idea – testing your backup. I’ve seen so many backups that weren’t any good. And all you have to do is test your backups – I tested mine weekly, just to be sure! Doesn’t take long and is a wonderful “life” saver in the future. Data is such a valuable asset – take care of it!

  • Good post Dave! I think you could write another post with more detail on every point you made in this one, which is awesome. One thing I would like to add is on alerting and using database mail. I found a catch several years ago that when using maintenance plans or any jobs with sqlagent you have to remember to tell the agent what mail profile to use. Just setting up database mail and telling SQL to mail a particular operator is not quite enought. Here is a post I wrote about that for reference. http://www.ryanjadams.com/2009/10/sqlagent-alerts/

  • […] SQL Server Maintenance Plans for Small/Mid-sized Businesses FAQs […]

  • Dave

    Nice job on the write-up which made me aware of a few items I need to address.
    I presently use Full Recovery w/full backup every 4 hours, transactions every hour, Integrity & SQL Optimization once weekly, M2M utility weekly and share backup nightly.

    As you previously mentioned on the phone backup history longtime storage should be considered for many reasons.

    Thanks again

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>