10 Things Every Production SQL Server Should Have
4.2 (390 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8,569 students enrolled

10 Things Every Production SQL Server Should Have

The Bare Essentials Every SQL Server Production Server Should Have.
4.2 (390 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8,569 students enrolled
Created by Mike West
Last updated 1/2016
English [Auto]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 1.5 hours on-demand video
  • 12 mins on-demand audio
  • 14 articles
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Configure SQL Server correctly for production environments
  • Set up an alerting system so you are notified when jobs fail and when alerts are fired
  • Become more confident when an outage occurs.
  • You'll need to have SQL Server installed. It can be any version but I'd suggest 2012 or 2014.
  • You'll also need a fundamental understanding of SQL Server.
  • There is no other cost other than the price of the course. Everything else is included or free.

Some recent reviews!!!

"I can't thank Mike enough for providing such useful information without cost; the content in this course is invaluable." Ken

"Thank you for the 10" David V

"Great Course." David S

This course focuses on 10 things that every SQL Server in production should have.

We will start with the single MOST important facet to every SQL Server DBAs job. Backups. We will cover the different kinds of backups and discuss how often we should be taking them. We will demo restores so WE understand the mechanics of how SQL Server backups up a database.

We will talk about the importance of the FULL backup and what having it means to recovery.

WE will cover consistency checks and talk about how often they should be run and why they should be run.

WE will cover statistics and index REBUILDS and how and why we should use them.

Lastly, WE will cover how to set up database MAIL so we can be alerted when OUR jobs fail.

Many organizations don't have production DBAs so this course can assist them with setting up the very basic necessities for a production SQL Server.

This is an entry level course and can be taken by developers, network administrators and database administrators. The course is even well suited for those who manage DBAs.

The course is a real world approach to setting up your SQL Server in a production environment.

The course if FREE so sign up now.

Who this course is for:
  • There are no prerequisites for this course. If you have a basic knowledge of SQL Server then this course is for you.
  • This course will cover the bare minimum we need for our SQL Servers in a production environment.
  • Using the collegiate scale as a guide this course will be a 101 level course.
Course content
Expand all 39 lectures 01:36:05
+ Introduction.
3 lectures 02:25

What are we going to learn in this course? This course will cover 10 things all production SQL Servers should have.

In this lesson let's overview those 10 items.

Preview 01:36

This is where you'll find the downloadable content for the course.

Course Content Can Be Downloaded Here

A little about me.

Instructor Introduction
+ Backups
11 lectures 38:32

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.

Preview 01:29

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.

What Are Recovery Models?

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.

Preview 01:57

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

Why The Full Backup Is So Important

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.

Preview 06:17

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.

Differential Backup: Demonstration

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.

Transaction Log Backup: Demonstration

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.

Creating A Very Simple Maintenance Plan For Our Backups

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.

Removing Unwanted Backup and Log 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.

Backup Strategies For Real World Boxes

Let's wrap up what we've covered.

Section 2 Summary
Section 2 Quiz
10 questions
+ Updating Statistics
3 lectures 05:47

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.

What Are Statistics?

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.

Creating A Job To Update Our Statistics

Let's wrap up what we've covered.

Section 3 Summary
4 lectures 04:28

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."

How Does 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?

How Often Should I Run Consistency Checks?

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

Creating Our Job To Run Consistency Checks

Let's wrap up what we've covered.

Section 4 Summary
Section 4 Quiz
5 questions
+ Rebuild Indexes
4 lectures 13:22

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

What's The Difference Between Rebuilding and Reorganizing?

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.

Rebuild VS Reorganize - Part 1

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

Rebuild VS Reorganize - Part 2

Let's wrap up what we've covered.

Section 5 Summary
Section 5 Quiz
10 questions
+ Change The Default Growth Settings
2 lectures 05:46

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.

Is The Default Really 1 MEG?

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

Section 6 Summary
+ Change Error Detection On Every Database
3 lectures 03:53

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 Enable CHECKSUMS

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

Section 7 Summary
+ Catch Critical Errors In The Errorlog
3 lectures 03:15

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 Errors Defined

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.

Catching Critical Errors Before They Turn Into Disasters
Section 8 Summary
+ Testing Our Restores
2 lectures 06:40

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.

Testing Our Backups Via A Restore

Let's summarize our lesson on testing our backups.

Section 9 Summary
+ Setting Up Database Mail
3 lectures 04:49

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.

Configure Our Account and Our Profile

Let's set up an operator.

Configure Operator For Failed Jobs

Let's summarize what we've covered.

Section 10 Summary