Advanced SQL Server High Availability & Disaster Recovery
4.1 (12 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.
95 students enrolled

Advanced SQL Server High Availability & Disaster Recovery

Implement tried-and-true high availability and disaster recovery solutions with SQL Server
4.1 (12 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.
95 students enrolled
Created by Packt Publishing
Last updated 1/2020
English
English [Auto]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6 hours on-demand video
  • 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 and troubleshoot Replication, AlwaysOn, and Log Shipping
  • Study the best practices to implement HA and DR solutions
  • Design HA and DR topologies for the SQL Server and study how to choose a topology for your environment
  • Automate the AlwaysOn setup using PowerShell
  • Migrate from On-Premise SQL Server to Azure SQL Database
  • Configure and troubleshoot snapshot replication
Course content
Expand all 40 lectures 05:57:42
+ Getting Started with SQL Server High Availability and Disaster Recovery
7 lectures 58:55

You will require the below to be setup on your PC

  • An Azure subscription

  • SQL Server Management Studio 17.2 or above

  • PowerShell 5.1

  • SQL Server 2014 SP2 or above installed as named instance Server\SQL2014

  • SQL Server 2016 SP2 installed as named instance Server\SQL2016

Preview 05:12

Let us begin with the first lesson and understand what we are going to cover in our learning journey.

Lesson Overview
01:04

High availability refers to providing an agreed level of system or application availability by minimizing the downtime caused by infrastructure or hardware failure. When the hardware fails, there's not much you can do other than switch the application to a different computer so as to make sure that the hardware failure doesn't cause application downtime.

Disaster recovery refers to re-establishing the application or system connectivity or availability on an alternate site, commonly known as a DR site, after an outage in the primary site.

The outage can be caused by a site-wide (data center) wide infrastructure outage or a natural disaster.

In this section you will understand the following:

  • Uses

  • Terminologies, and

  • Solutions

What is High Availability and Disaster Recovery
14:00

Replication is a SQL Server feature that synchronizes data from a database (known as a publisher) to one or more databases (known as subscribers) on the same or different SQL Server instances.

A replication has a number of components that work together to synchronize data between databases.

Here you will learn about the following:

  • Replication Components

  • Replication Agents

    • Snapshot agent

    • Log Reader agent

    • Distribution agent

    • Merge agent

  • Types of Replication

    • Transactional

    • Merge

    • Snapshot

Introduction to SQL Server Replication
08:03

Configuring snapshot replication is a two-step process:

  • First step is to create the publication, and

  • Second step is to create the subscription.

In this section you will learn the following:

  • Creating a Publication

  • Exploring the Distribution Database

  • Creating a Subscription

  • Exploring the Distribution Database

  • Running the Job

  • Distribution Agent Process

Configuring Snapshot Replication Using SQL Server Management Studio
26:23

In this section, we'll discuss optimizing snapshot replication by following best practices and tuning the snapshot and distributor agent parameters.

Let's look at a few of the best practices you should consider when working with snapshot replication:

  • Minimizing Logging at Subscriber

  • Minimizing Locking

  • Replicating Only Required Articles

  • Using Pull Subscription

  • Compressing the Snapshot Folder


Optimizing Snapshot Replication
03:29

Summarize your learning from this lesson

Lesson Summary
00:44
Test your knowledge
7 questions
+ Transactional Replication
5 lectures 21:24

Let us begin with the second lesson and understand what we are going to cover in this section.

Preview 00:53

Transactional replication is the most common type of replication used to synchronize data between two databases. It is often used to load-balance read and write operations and is also a cost-effective solution for high availability.

In this section you will learn to do the following:

  • Creating a Publication

  • Creating the Subscription

  • Modifying the existing Publication

  • Removing Transactional Publication

  • Configuring and Verifying Azure SQL Database as a Subscriber

Understanding and Configuring Transactional Replication
10:01

Peer-to-Peer replication, also referred to as master-master replication, is built upon transactional replication in which changes at the subscriber are also replicated to the publisher.

A peer-to-peer topology consists of two or more databases where a change in each node (database) gets replicated at every other node (database) in the topology.

Peer-to-peer replication therefore provides a scalable high availability solution

In this section you will learn to do the following:

  • Creating the Publication, Adding Articles, Adding Subscription and Pushing the Subscription Agent to the Publication on Server 1

  • Taking the Backup of the Publication Database on Server 1

  • Restoring the Database on the Subscriber Server

  • Creating the Publication, Adding Articles, Adding Subscription and Pushing the Subscription Agent to the Publication on Server 2

  • Verifying the Replication

Understanding Peer-to-Peer Replication
07:14

In this section, you will learn how the publication properties can be changed, as discussed for the standard transactional replication. We'll look at how to remove and add an article in a P2P replication.

Modifying an Existing Publication
02:35

Summarize your learning from this lesson.

Lesson Summary
00:41
Test your knowledge
7 questions
+ Monitoring Transactional Replication
6 lectures 47:58

Let us begin with the third lesson and understand what we are going to cover in this section.

Preview 00:54

The replication monitor is a GUI that's used to monitor replication agent status and replication performance.

It's not specific to transactional replication and can be used to monitor any of the available replication types.

In this section you will understand the following:

  • Setting Up the Replication Monitor

  • Monitoring Replication with the Replication Monitor

  • Configuring Replication Alerts

The Replication Monitor
06:34

In this topic we will discuss some of the common real-world transactional replication problems that may occur in a transactional replication configuration.

There are no specific patterns for these problems. Almost all of these problems can be solved without having to reconfigure the replication

Here you will learn about the following:

  • Configuration Issues

  • Data Issues

Real World Transactional Replication Problems and Solutions
25:10

Replication performance can be improved not only by appropriately configuring the agent parameters as discussed earlier.

Here you will learn about the problem of the Log Reader agent taking time to scan the transactional log.

Performance Issues
10:20

P2P transactional replication can result in conflicts. Here you will look at a conflict example.

First you will have a look at the conflicts in P2P Transactional Replication and then you will learn how to add and Article.

Conflicts in Peer to Peer Transactional Replication
04:08

Summarize your learning from this lesson.

Lesson Summary
00:52
Test your knowledge
7 questions
+ AlwaysOn Availability Group
7 lectures 01:28:36

Let us begin with the fourth lesson and understand what we are going to cover in this section.

Preview 01:44

An AlwaysOn availability group is a group of databases that can fail over from a primary server to a secondary server automatically or manually, as and when required.

Commonly known as AlwaysOn AGs, they're the premium high availability and disaster recovery solution available in SQL Server.

In this section you will also learn about the various Availability Groups, Automatic and Manual Failover, then we will have a look at the different health check timeout threshold and lastly all the five failure condition levels that specify the condition that will cause the automatic failover.

AlwaysOn Availability Group Concepts and Terminology
18:40

In synchronous commit mode, a transaction takes a longer time to complete as it has to be committed at primary and secondary replicas.

A synchronous commit, however, ensures zero data loss. The primary and secondary replicas always ping each other to check connectivity. If there's a delay of more than 10 seconds in receiving a response, the synchronous replica mode changes to asynchronous. The mode is restored back to synchronous once the connectivity is restored

Here you will learn about the following:

  • Synchronous Commit

  • Asynchronous Commit

  • Primary Replica Fails

    • Planned

    • Forced and,

    • Automatic

  • Secondary Replica Fails


Data Synchronization
08:30

We'll first look at the prerequisites for the setup and will then do the following:

  • Set up four Hyper-V VMs; one VM will act as a domain controller and the other three will be the availability group replicas

  • Set up AlwaysOn AGs

As a part of the AlwaysOn availability setup, we'll also configure a domain controller.

In a real-world scenario, configuring a domain controller and Windows Server Failover Cluster is part of a System Administrator's job.

We'll also talk about modifying an existing availability group such as adding/removing a database from an availability group, modifying backup preferences, and adding a new replica.


Prerequisites for Configuring Availability Groups
21:18

ADDC is implemented in almost every organization that has a Windows workstation.

In day-to-day work, ADDC is used to authenticate users into their laptops, Outlook, and other applications. ADDC is required as a prerequisite for configuring AlwaysOn availability groups on Windows Server 2012 R2

Here you will learn about the following:

  • Configuring Active Directory Domain Controller

  • Downloading and Installing the SQL Server 2016 Developer Edition

  • Restoring Sample Databases

  • Configuring Windows Server Failover Cluster


Active Directory Domain Controller
15:02

In a real-world scenario, the replicas will be in different subnets; however, they'll be under the same domain and part of one WSFC

Here you will learn about the following:

  • Enabling the AlwaysOn Availability Group feature

  • Creating the Availability Group

  • Reviewing an AlwaysOn Availability Group Configuration

  • Configuring an AlwaysOn Availability Group on Microsoft Azure

  • Reviewing Our Availability Group Configuration

  • Limitations of Configuring AlwaysOn Availability on Microsoft Azure

  • Deleting the AlwaysOn Configuration


Configuring AlwaysOn Availability Groups
22:26

Summarize your learning from this lesson.

Lesson Summary
00:56
Test your knowledge
9 questions
+ Managing AlwaysOn Availability Group
7 lectures 01:20:11

Let us begin with the fifth lesson and understand what we are going to cover in this section.

Preview 01:18

A failover can be of two types: Automatic and Manual

  • An automatic failover, triggered as a result of the primary instance being unavailable or the availability databases being unavailable (when database level health detection is enabled)

  • A manual failover to a synchronous (zero data loss) or an asynchronous (data loss) replica

Let's now look at automatic and manual failover in detail.

AlwaysOn Availability Group Failover
10:55

In this section, you'll learn about the various management tasks that you will have to perform once an AlwaysOn availability group is up and running.

Here you will also learn to do the following:

  • Removing a Database from the Secondary and Primary Replica

  • Adding a Database to an Availability Group

  • Removing and Adding a Replica to an Availability Group

  • Changing the Availability and Failover Mode of a Replica

  • Creating Multiple Listeners for the same Availability Group

  • Configuring Backups on the Secondary Replica

  • Configuring Readable Secondaries

  • Read-Only Routing

  • Configuring the Flexible Failover Policy

Managing AlwaysOn Availability Groups
29:16

An availability database can have the following synchronization states:

  • Initializing

  • Reverting

  • Synchronizing

  • Not Synchronizing

  • Synchronized


Availability Database Synchronization States
03:34

Monitoring an AlwaysOn availability group is important to quickly find problems before or when they occur, and then take corrective actions.

Monitoring an AlwaysOn AG can be done using the AlwaysOn dashboard, T-SQL, PowerShell, Performance Monitor, or other third-party monitoring tools.

In this section, you will learn about the following:

  • The AlwaysOn Availability Group Dashboard

  • Collecting Latency Data Using the AlwaysOn Availability Group Dashboard

  • The SQL Server Agent Job

  • Configuring Reports

  • Primary Replica Latency Report

  • Secondary Replica Latency Report

  • Monitoring Availability Replica States

  • Monitoring Availability Database’s Synchronization Status

  • Monitoring the AlwaysOn Availability Group Current Workload

  • Monitoring an AlwaysOn Availability Group Using Power Shell


Monitoring AlwaysOn Availability Groups
21:31

In this section, we'll look at some of the common AlwaysOn Availability Groups problems and their solutions.

Some of the common issues which you can come across:

  • DDL Queries Block the Redo Thread on the Secondary Replica

  • Transaction Log is Growing

  • Replica Database in the Resolving State


Troubleshooting AlwaysOn Availability Groups
12:59

Summarize your learning from this lesson.

Lesson Summary
00:38
Test your knowledge
6 questions
+ Configuring and Managing Log Shipping
5 lectures 43:54

Let us begin with the sixth lesson and understand what we are going to cover in this section.

Preview 01:09

In this section you will learn to configure and manage Log Shipping.

Here you will also learn to do the following:

  • Adding a New Secondary Instance to an Existing Log Shipping Configuration

  • Removing a New Secondary Instance to an Existing Log Shipping Configuration

  • Adding a Database to a Log Shipping Configuration

  • Removing a Log Shipped Database

Managing Log Shipping
16:44

Log shipping configuration can be monitored in the following ways:

  • Configuring Email Alerts for the Log Shipping SQL Agent Jobs

  • Using T-SQL to Set Up a Custom Monitoring

  • Changing the Database State at the Secondary

  • Performing a Failover from the Primary Instance to the Secondary Instance


Monitoring Log Shipping
13:21

We’ll now look at few of the common problems that can occur in a log shipping environment and their solutions:

  • Middle of a Restore Error

  • Directory Lookup for File Failed

You will also understand about Comparing AlwaysOn, Replication, and Log Shipping


Troubleshooting Common Log Shipping Issues
11:54

Summarize your learning from this lesson.

Lesson Summary
00:46
Test your knowledge
6 questions
+ SQL Server Failover Cluster
3 lectures 16:44

Let us begin with the final lesson for this course and understand what we are going to cover in this section.

Preview 00:43

Windows Server Failover Cluster are a group of independent servers that work together to increase the availability of applications and services

SQL Server can take advantage of Windows Server Failover Cluster services and capabilities to support

  • Always On availability groups

  • SQL Server Failover Cluster Instances

Here you will also understand the following concepts:

  • SQL Server Failover Cluster Instance

  • Key Benefits of SQL Server Clustering

  • Limitations of Failover Clustering

  • SQL Server Failover Cluster

  • SQL Server Failover Clustering + Availability Groups

  • Failover Cluster Instance + Availability Groups


SQL Server Failover Cluster
15:31

Summarize your learning from this lesson.

Lesson Summary
00:30
Test your knowledge
9 questions
Requirements
  • To gain maximum benefits from this course, you should have worked prior experience of working with SQL Server.
Description

Business continuity is of utmost importance. High availability (HA) and disaster recovery (DR) ensure that your infrastructure experiences no downtime and smoothly functions at all times. Sound knowledge of various HA and DR techniques help you to drastically reduce the downtime of your applications.

This course begins by explaining the high availability and disaster recovery technologies available in SQL Server: Replication, AlwaysOn, and Log Shipping. You’ll learn what they are, how to monitor them, and how to troubleshoot any related problems. You’ll learn how to use different HA and DR topologies to create a highly available infrastructure that also includes hybrid topologies. You’ll learn what is Always On, it’s pros and cons, prerequisites, and restrictions. You’ll also learn about log shipping, one of the oldest SQL Server solutions, which is mostly used for DR and SQL Server migration. Through this course, you will be able to explore the technical implementations of high availability and disaster recovery technologies that you can use when you create a highly available infrastructure, including hybrid topologies.

By the end of the course, you’ll feel confident and look forward to changing the game with robust and high performing infrastructure.

About the Author

Ahmad Osama works for Pitney Bowes Pvt Ltd as a database engineer and is a Microsoft Data Platform MVP. In his day to day job at Pitney Bowes, he works on developing and maintaining high performance on-premises and cloud SQL Server OLTP environments, building CI/CD environments for databases and automation. Other than his day to day work, Ahmad blogs at dataplatformlabs and has written over 100 blogs, including SQL Server Administration/Development, Azure SQL Database, and Azure Data Factory. He regularly speaks at user group events and webinars conducted by the Dataplatformlabs community.

Gethyn Ellis has over eighteen years of experience with SQL Server and for past ten years he has been working on Azure. He is an Microsoft certified trainer. He also trains and is a consultant for SQL Server. Prior to this he has worked with Packt and written books on "Getting Started SQL Server 2014 Administration" , "Microsoft Azure laaS Essentials", and Professional Azure SQL Database Administration.

Who this course is for:
  • Professional SQL Server High Availability and Disaster Recovery is designed for database administrators, database developers, and application developers, who want to improve the performance of their production environment.