55246: SQL 2016 AlwaysOn High Availability

55246: SQL 2016 AlwaysOn High Availability


Experienced DBAs, Windows Server pros, team leads.


Before attending this course, students must have:

  • Experience as SQL DBA
  • Experience as Windows IT PRO


3 days. Hands on.

This is a lab intense course.

Course Objectives

This three-day instructor-led course is designed for database administrators and Windows engineers to familiarize them with the concepts in SQL AlwaysOn and High Availability. The course utilizes SQL 2016, but explains the differences from SQL 2012- SQL 2014.

After completing this course, students will be able to:

  • Understand AlwasyOn High Availability
  • Employ Server 2016 Failover Clustering
  • Deploy SQL Failover Clusters
  • Work with Availability Groups
  • Perform maintenance
  • Monitor and Troubleshoot Availability Groups

Course Content

Module 1: Introduction
Course introduction

Module 2: AlwaysOn and High Availability Concepts and Terminology
Concepts and Terminology
Table of Availability
High Availability
Causes of Downtime
Planned downtime
Unplanned downtime
Disaster Recovery
Recovery Time Objective (RTO)
Recovery Point Objective (RPO)
Recovery Level Objective (RLO)
Storage Area Networks (SAN)
Edition Changes from SQL 2012
SQL Server 2014 Changes
SQL Server 2016 Changes
Legacy Solutions prior to AlwaysOn
Failover Cluster Instances
Log Shipping
A Typical Log Shipping Configuration
Monitor Server
Database Mirroring
Database Mirroring Terminology
Database Snapshots
Limitations of legacy solutions
What do we mean by AlwaysOn ?
Table of AlwaysOn Comparison
After completing this module, students will be able to:
• Terminology
• SQL 2014 Changes
• SQL 2016 Changes
• Understand the role of the SANS

Module 3: Windows Server 2016 Failover Clustering
Understanding Failover Clustering in Server 2016
Statefull High Availability Solution
Supported in both Standard and Datacenter
Servers should run similar hardware
Should run same edition
Hyper-V best with datacenter
Certified for Windows server logo
Shared Storage
Node Majority
Node and Disk Majority configuration
Node and File Share Majority
No Majority
Cluster Networks Best Practices
Connection to nodes to shared storage
Private network for internal cluster
Public network for client connections
Cluster Aware Updating
Virtual Machine Failover Clustering
Preferred Owners
Failover Failback
Lab : Set up iSCSI Server
Lab : Install the iSCSI VMS
Lab : Add Servers to Server Manager for Ease of Management
Lab : Add the Windows Cluster Feature to SQL1, SQL2 And SQL3
Lab : Create the iSCSI Initators to add the shared storage
Lab : Create the Windows Cluster
Lab : Add a Clustered Service
Lab : Test The Failover Of The Windows Service
Lab : Delete Role
Lab : Examine the Quorum Settings
After completing this module, students will be able to:
• ISCSI Setup
• Work with Roles
• Understand Quorums
• Understand Windows Failover
• Understand Cluster Service
• Understand Preferred Owners
• Understand Node Majority

Module 4: SQL 2016 Failover Cluster Instances
Failover Cluster Instance
As A FCI Appears To A Client
Lab : Create A Configuration File By Running The Advanced Cluster Preparation Wizard
Lab : Complete The SQL Cluster Installation On SQL1
Lab : Install The Cluster On SQL2 And SQL3
Lab : Test the SQL Cluster
After completing this module, students will be able to:
• Cluster Testing
• Understand Configuration Files
• Install Clusters

Module 5: SQL 2016 AlwaysOn Availability Groups
Availability Groups and Replicas
Primary Replica
Secondary Replicas
Availability Group Listener
Availability Mode
Synchronous Commit Mode
Asynchronous Commit Mode
Failover Modes
Automatic Failover Without Data Loss
Automatic Failover Requirements
Manual Failover Requirements
Common Topologies
Lab : Create a SQL Instance For The Availability Group
Lab : Enable the SQL Server AlwaysOn Availability Group Feature
Lab : Set Up For Availability Groups
Lab : The Availability Group Wizard
Lab : SSMS and Availability Groups
After completing this module, students will be able to:
• Enable AlwaysOn
• Understand and work with availability Groups

Module 6: The Dashboard
The Dashboard
How to view logs
Using replication with Logins
Using partially contained databases
Lab : The Dashboard
Lab : Replicating Logins and Jobs
Lab : Contained or Partially Contained Databases
After completing this module, students will be able to:
• Understand the Dashboard
• Perform Logon and Job replication

Module 7: Active Secondary Availability Group Actions
Reporting with Secondary Replicas
Configuring a Readable secondary
Read-Only Routing
Load Balancing
Lab: Configure a Read-Only Secondary
Database Backups with Secondary
Steps of Backup Using secondary
Backup Preference Options
Lab : Database Backup Using Secondary Replica
Lab : Configure a Read-Only secondary
After completing this module, students will be able to:
• Perform backups with Secondary Replicas
• Configure a Read-Only Replica

Module 8: Maintenance
DBCC Checks
Database Adding and Removing
Lab : Add a Database
Lab : Remove a Database
Lab : Add a Replica
Lab : Remove a Replica
After completing this module, students will be able to:
• Add and Remove Databases
• Add and Remove Replicas

Module 9: Monitoring and Troubleshooting Availability Groups
The Dashboard in Depth
Policy Based Management for Availability Groups
Lab : Dashboard Wizards
Lab : Create an Extended Event Session
Lab : Using T-SQL
Lab : Policy based management for Availability Groups
Lab : Observe a Policy In Action
Lab : Create Three Conditions To Be Used In The RTO And RPO Policies
Lab : Create Two Policies RTO and RPO
Lab : Test The Policies
Lab : Change Endpoint Owner
After completing this module, students will be able to:
• Change Owners
• Work with Policies
• Work with Extended Events

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...