10 Things Every Production SQL Server Should Have
4.3 (131 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,647 students enrolled
Wishlisted Wishlist

Please confirm that you want to add 10 Things Every Production SQL Server Should Have to your Wishlist.

Add to Wishlist

10 Things Every Production SQL Server Should Have

The Bare Essentials Every SQL Server Production Server Should Have.
4.3 (131 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,647 students enrolled
Created by Mike West
Last updated 1/2016
English
Current price: $10 Original price: $20 Discount: 50% off
15 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 12 mins on-demand audio
  • 14 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I 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.
View Curriculum
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.
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.

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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 39 Lectures Collapse All 39 Lectures 01:36:05
+
Introduction.
3 Lectures 02:26

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
00:02

A little about me.

Instructor Introduction
00:48
+
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?
01:22

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
01:05

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
04:07

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
05:50

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
09:00

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
03:50

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
02:44

Let's wrap up what we've covered.

Section 2 Summary
00:50

Section 2 Quiz
10 questions
+
Updating Statistics
3 Lectures 05:48

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?
02:12

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
03:17

Let's wrap up what we've covered.

Section 3 Summary
00:19
+
DBCC CHECKDB
4 Lectures 04:26

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?
01:36

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?
00:33

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

Creating Our Job To Run Consistency Checks
01:46

Let's wrap up what we've covered.

Section 4 Summary
00:31

Section 4 Quiz
5 questions
+
Rebuild Indexes
4 Lectures 13:23

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

What's The Difference Between Rebuilding and Reorganizing?
04:10

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
06:25

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

Rebuild VS Reorganize - Part 2
02:04

Let's wrap up what we've covered.

Section 5 Summary
00:44

Section 5 Quiz
10 questions
+
Change The Default Growth Settings
2 Lectures 05:45

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?
05:27

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

Section 6 Summary
00:18
+
Change Error Detection On Every Database
3 Lectures 03:52

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

What's A CHECKSUM?
01:32

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

Let's Enable CHECKSUMS
01:48

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

Section 7 Summary
00:32
+
Catch Critical Errors In The Errorlog
3 Lectures 03:17

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
00:14

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
02:27

Section 8 Summary
00:35
+
Testing Our Restores
2 Lectures 06:41

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
05:50

Let's summarize our lesson on testing our backups.

Section 9 Summary
00:51
+
Setting Up Database Mail
3 Lectures 04:48

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
02:26

Let's set up an operator.

Configure Operator For Failed Jobs
01:40

Let's summarize what we've covered.

Section 10 Summary
00:42
1 More Section
About the Instructor
Mike West
4.2 Average rating
1,669 Reviews
29,132 Students
32 Courses
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

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

Personal

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