Course 55246-A: SQL 2016 Always On High Availability

Microsoft

Course 55246-A: SQL 2016 Always On High Availability

Share This Learning Path

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.

Audience Profile

Experienced DBAs, Windows Server pros, team leads. This is a lab intense course! We designed this course based on our experience of having taught hundreds of classes to literally thousands of students. We tried very hard to make the labs, of which there are over 30, very oriented to a single concept such as Adding a Replica or Transferring Logons. We did this because it is common in technical courses to write long labs with multiple exercises which in our opinion is not effective as they turn into ? click streams ?. We assume the student is new to the technology and that the instructor is knowledgeable in it.

Prerequisites

Course outline

Module 1: Introduction

Module Overview

This module explains the course and objectives.

Lessons

Course introduction

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Understand the course and objectives.

Module 2: Alwayson And High Availability Concepts And Terminology

Module Overview

This module will introduce the participants to the concepts and terminology used in the course.

Lessons

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 Always On
Failover Cluster Instances
Log Shipping
A Typical Log Shipping Configuration
Monitor Server
Replication
Database Mirroring
Database Mirroring Terminology
Principle
Mirror
Witness (red box in image above)
Database Snapshots
Limitations of legacy solutions:
What do we mean by Always On ?
Table of Always On Comparison

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Terminology.
SQL 2014 Changes.
SQL2016 Changes.
Understand the role of the SANS.

Module 3: Windows Server 2016 Failover Clustering

Module Overview

Failover clustering is covered in this module and is a critical feature of Always On High Availability.

Lessons

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
Quorums
Node Majority
Node and Disk Majority configuration:
Node and File Share Majority
No Majority
Configuration
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
Resources
Dependences
Heartbeat

Lab Sessions

Set up iSCSI Server
Install the iSCSI VMS
Add Servers to Server Manager for Ease of Management
Add the Windows Cluster Feature to SQL1, SQL2 And SQL3
Create the iSCSI Initators to add the shared storage
Create the Windows Cluster
Add a Clustered Service
Test The Failover Of The Windows Service
Delete Role

Lab Lessons

Lab lessons not available

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

Module Overview

In this module we move from the generic failover clustering to the specifics involving SQL.

Lessons

Failover Cluster Instance
As A FCI Appears To A Client

Lab Sessions

Create A Configuration File By Running The Advanced Cluster Preparation Wizard
Complete The SQL Cluster Installation On SQL1
Install The Cluster On SQL2 And SQL3
Test the SQL Cluster

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Cluster Testing.
Understand Configuration Files.
Install Clusters.

Module 5: SQL 2016 Always On Availability Groups

Module Overview

Within the failover clusters of SQL are the concept of Availability groups and their enhancements with the release of SQL 2016 which is the focus of this module.

Lessons

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
Manual Failover Requirements
Common Topologies

Lab Sessions

Create a SQL Instance For The Availability Group
Enable the SQL Server AlwaysOn Availability Group Feature
Set Up For Availability Groups
The Availability Group Wizard
SSMS and Availability Groups

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Enable Always On.
Understand and work with availability Groups.

Module 6: The Dasboard

Module Overview

Managing Always On High Availability groups wit SQL 2016 is accomplished with the Dashboard. This module will demonstrate the skills necessary for the accomplishment of the management tasks.

Lessons

The Dashboard
How to view logs
Using replication with Logins
Using partially contained databases

Lab Sessions

The Dashboard
Replicating Logins and Jobs
Contained or Partially Contained Databases

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Understand the Dashboard.
Perform Logon and Job replication.

Module 7: Active Secondary Availability Group Actions

Module Overview

Within Availability groups you may have Active secondary SQL which is covered and demonstrated in this module.

Lessons

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 Sessions

Database Backup Using Secondary Replica
Configure a Read-Only secondary

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Perform backups with Secondary Replicas.
Configure a Read-Only Replica.

Module 8: Maintenance

Module Overview

In this module you explore maintenance procedures for Always On High Availability Groups.

Lessons

DBCC Checks
Database Adding and Removing

Lab Sessions

Add a Database
Remove a Database
Add a Replica
Remove a Replica

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Add and Remove Databases.
Add and Remove Replicas.

Module 9: Monitoring And Troubleshooting Availability Groups

Module Overview

In this the final module you will learn how to monitor the clusters and Availability groups and various common troubleshooting procedures.

Lessons

The Dashboard in Depth
Events
Policy Based Managemnt for Availability Groups
Migrating Settings by using Windows Easy Transfer
Configuring a Reference Image of Windows 7
Configuring a Reference Image

Lab Sessions

Dashboard Wizards
Create an Extended Event Session
Using T-SQL
Policy based management for Availability Groups
Observe a Policy In Action
Create Three Conditions To Be Used In The RTO And RPO Policies
Create Two Policies RTO and RPO
Test The Policies
Change Endpoint Owner

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Change Owners.
Work with Policies.
Work with Extended Events.

Book Your Seat

Find Learning Paths

Course 20703-1-B: Administering System Center Configuration Manager

Join our community of certified professionals

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Contact Us

International Computer Training Center