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

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
• 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: 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

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

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
• 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

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

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

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

Module 9: Monitoring and troubleshooting Availability Group

• The Dashboard in Depth
• Events
• Policy Based Managemnt 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