Advanced SQL Server High Availability & Disaster Recovery

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

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

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

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.

Summarize your learning from this lesson.

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

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

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

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.

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.

Summarize your learning from this lesson.

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

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.

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

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.

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

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

Summarize your learning from this lesson.

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

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.

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

An availability database can have the following synchronization states:

• Initializing

• Reverting

• Synchronizing

• Not Synchronizing

• Synchronized

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

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

Summarize your learning from this lesson.

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

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

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

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

Summarize your learning from this lesson.

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

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

Summarize your learning from this lesson.