What Are These Backup Settings All About?

I ran across a client the other day that had these Backup and Recovery options set like the picture below because it is defaulted this way. The Database Administrator didn’t know what they should configure them as so he left them alone. I find this is the case with a lot of options. For the most part leaving the defaults can be just fine, but other leaving others cause leave you missing out on some good features.

Let’s start from top to bottom.

Default backup media retention in days. Now the first things that comes to my mind is that “hey this is a cleanup job” SCORE! Thinking that maybe this will auto delete old backups. After all isn’t that what retention means? NOPE, not in this case.

In this case it’s just a number of days before that a backup media can be OVERWRITTEN. If the DBA goes to overwrite the media before those days it will give a warning message. You’ll note in every back up action you do the RETAINDAYS option is filled in. In this case it will always reflect to 90 now that we have changed it. In general, this a pointless option to me. I don’t normally OVERWRITE backup media. To me this was more relevant when Tapes were used and disk were harder to come by, so I leave it alone.

TSQL

 

GUI

Compress backup. This one is exactly what you think it is, no guessing here. Backup Compression is one I highly recommend changing from the default. Compression is a HUGE topic I will save for another time. But in short, the smaller the files the less space it takes up, less data stored means IO (and less data sent to your backup device) and therefore your databases back up and restore faster. Here is a great MDSN link to learn more about the benefits of backup compression. Backup compression is included in all editions of SQL Server since 2008 R2, so use it!

Recovery Interval (in minutes). Now this one I always thought meant Recovery Point Objective, in other words how much data am I willing to lose in minutes. I am partially right. According to MSDN, this option defines an upper limit on the time recovering a database should take. The SQL Server Database Engine uses the value specified for this option to determine approximately how often to issue automatic checkpoints on a given database.

This is an option I don’t change. I have yet to see a scenario where I want to override when SQL Server does a check point on the database by default.  There are times when I want to force a check point but it’s not something I am going to set a standard for. The only reason I have heard was to reduce IO on a data drive, but to me that’s at too high of a cost.

TSQL

So, there you have it, three more options that may not be a mystery for some any longer.

Initial SQL Server Configurations

Wonder if I Do Things Differently?

I am always wondering what other DBA’s do and if I am doing things differently. One such thing is my initial server setups, basically, what I configure for each of my new servers. So, why not blog about it and see what others chime in with after they read this. Keeping in mind that everyone has different requirements and different ways that they like to do the actual configurations.

For now, I am not going to go into what each one of these configurations do and why I choose the value I do. That’s for another time. If you want that information you can always go to Books Online or just Google it. In this case, I am just going to give you a running list with scripts; that I’ve added too over the years based on best practices and experience.

How Does Yours Compare?

I’d really love to hear what others do and if I may have missed something that you like to implement that may benefit me as well.  So leave a comment, tweet to me, or send me an email let’s compare notes.

The List

So here are the basics setups I do on every server post install in no particular order.

* Value varies based on server configuration

  1. Min and Max Memory *

  1. Enable and Configure Database Mail ( This is only to enable, full script will be in later post)

  1. Set Default Database Locations

  1. Set SQL Agent Job History Retentions

  1. Set Cost threshold for Parallelism *

  1. Set Max Degree of a Parallelism *

  1. Set Optimize for Adhoc work loads

  1. Change Number of Error Logs

  1. Create Cycle Error Log Job

  1. Add Additional TempDB Files All With Same Size and Growth Rates *

  1. Set Media Retention

  1. Set Backup Compression Default On

  1. Change to Audit Successful and Failed Logins

  1. Set Default Growths in Model Not Be Percentages

  1. Set AD Hoc Distributed Queries off 
  2. Set CLR Enabled off 
  3. Set Ole Automation Procedures off 
  4. Set Scan For Startup Procs off 
  5. Set xp_cmdshell off
  6. Setup Operators

  1. Set Up Alerts 17-25 and Error codes  823,824,825 (Remember to add the alerts to the operator)

Note: Most of these can be set using GUI as well as the scripts above. Also, in addition to these configurations, I make sure that the server is brought up to the most current stable CU or Service Pack. Everyone’s environment is different, my list may not be right for you.