10 Things Every Production SQL Server Should Have

Let's cover the very basics of what a SQL Server database is. All SQL Server databases must have 2 files. Each database must have one data file and one log file.

All production databases should be in FULL recovery and transaction log backups should be in place. Let's take a look at what our options are here.

SQL Server has three major types of backups.

A full backup.

A differential backup.

A transaction log backup.

Let's discuss the different types in this lesson.

Without a full backup there is no restore. This minute long audio is the very foundation to our restores.

In this lecture let's demo the different kinds of backups. We will start with the full and then work our way to the differential and finally the transaction log backup.

It would be ideal to have have FULL backups nightly but as our databases grow into VLDBs (Very Large Databases), that isn't always possible.

Fortunately, we have differential backs to assist us.

We start with a full backup then restore our transaction logs up to the point where the failure took place.

Transaction logs provide us with an up to the minute (if we set them up that way) ability to recover our databases.

We have lots of ways to set up our backups but for those that are new to SQL Server, the wizard approach gives us a step by step GUI to configure them.

Companies have different retention policies. Many like the 3 days on disk approach. No matter what methodology you use you'll want to remove older .bak and .trn files.

Let's talk about real world backup strategies.

Ideally, I like the idea of having FULL backups nightly. Sometime that's not possible so we have to craft a plan that's right for our environment.

Let's wrap up what we've covered.

Statistics are crucial to the optimizer. Instead of guessing, the optimizer should be given the most up to date information you can when it comes to data distribution within your tables.

Here's a bitly to Phil's Script: http://bit.ly/1eePhXg

You can configure your stats to update via a maintenace plan but I like a little more control.

Let's wrap up what we've covered.

The vast majority of database corruption happens at the IO stack. Let's take a look at how the majority of corruption happens.

The titled really should be... "How Does The Vast Majority of Corruption Happen."

One of my favorite quotes is by Paul Randal. He says, "The overwhelming factor in how often to run consistency checks is you."

How comfortable do yo feel recovering a corrupt database?

Lets' craft a job to check for corruption. Ideally, I like to run these daily.

Let's wrap up what we've covered.

These two approaches work to eliminate fragmentation. Let's talk about them in this audio.

In this lesson we are going to run some tests to compare how well each approach removes fragmentation.

Each has it's strengths and it's weaknesses.

Let's continue to compare how these options stack up to one another.

Let's wrap up what we've covered.

Some of the default SQL Server settings are a little off. This is one of them. Let's get this fixed. It's an easy button fix.

With only one lesson this summation will be short and easy to remember.

We need to take as many proactive measures as we can to head off corruption.

Enabling checksums on database or many is easy. Let's cover how we do this.

Let's summarize what we've covered on error detection.

A description of the errors and how we approach them is beyond the scope of the course.

However, should one of these critical errors arise it would be good to have a reference point to start from.

Tim's article is a great overview of the various areas we need to be on the look out for.

The great thing about the SQL Server Community is their willingness to share their knowledge and their hard work. In this lesson we are going to use Glenn Berry's script to proactively catch errors.

How do you know your backups are working if you haven't tested them?

The answer is... you don't.

We should be testing our restores on our backups on a regular basis.

Let's summarize our lesson on testing our backups.

We need to be alerted in some fashion when our jobs fail or when corruption happens.

Most DBAs use email for this.

SQL Server comes with database Mail.

This provides us with a built in option for notifications.

Let's set up an operator.

Let's summarize what we've covered.