10 Things Every Production SQL Server Should Have

The Bare Essentials Every SQL Server Production Server Should Have.
4.0 (124 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
7,615 students enrolled
Take This Course
  • Lectures 39
  • Length 1.5 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works


Find online courses made by experts from around the world.


Take your courses with you and learn anywhere, anytime.


Learn and practice real-world skills and achieve your goals.

About This Course

Published 8/2015 English

Course Description

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.

What are the requirements?

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

What am I going to get from this course?

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

Who is the target audience?

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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.


Section 1: Introduction.

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.


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


A little about me.

Section 2: Backups

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.

Section 2 Quiz
10 questions
Section 3: Updating Statistics

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.

Section 4 Quiz
5 questions
Section 5: Rebuild Indexes

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.

Section 5 Quiz
10 questions
Section 6: Change The Default Growth Settings

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.

Section 7: Change Error Detection On Every Database

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.

Section 8: Catch Critical Errors In The Errorlog

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.

Section 8 Summary
Section 9: Testing Our Restores

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.

Section 10: Setting Up Database Mail

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.

Section 11: Conclusion


You've reached the end.

Thanks for taking my course.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mike West, SQL Server Evangelist

I've been a production SQL Server DBA most of my career.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.

Experience, education and passion

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.


Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).


Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.

Ready to start learning?
Take This Course