MOC-55246 SQL AlwaysOn High Availability

Module 1: Introduction

• 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 1: Set up iSCSI Server

Lab 2: Install the iSCSI VMS

Lab 3: Add Servers to Server Manager for Ease of Management

Lab 4: Add the Windows Cluster Feature to SQL1, SQL2 And SQL3

Lab 5: Create the iSCSI Initators to add the shared storage

Lab 6: Create the Windows Cluster

Lab 7: Add a Clustered Service

Lab 8: Test The Failover Of The Windows Service

Lab 9: Delete Role

Lab 10: Examine the Quorum Settings

Module 4: SQL 2016 FAILOVER CLUSTER INSTANCES

• Failover Cluster Instance
• As A FCI Appears To A Client

Lab 1: Create A Configuration File By Running The Advanced Cluster Preparation Wizard

Lab 2: Complete The SQL Cluster Installation On SQL1

Lab 3: Install The Cluster On SQL2 And SQL3

Lab 4: Test the SQL Cluster

Module 5: SQL 2016 ALWAYS ON 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 1: Create a SQL Instance For The Availability Group

Lab 2: Enable the SQL Server AlwaysOn Availability Group Feature

Lab 3: Set Up For Availability Groups

Lab 4: The Availability Group Wizard

Lab 5: SSMS and Availability Groups

Module 6: The Dasboard

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

Lab 1: The Dashboard

Lab 2: Replicating Logins and Jobs

Lab 3: 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 1: Database Backup Using Secondary Replica

Lab 2: Configure a Read-Only secondary

Module 8: Maintenance

• DBCC Checks
• Database Adding and Removing

Lab 1: Add a Database

Lab 2: Remove a Database

Lab 3: Add a Replica

Lab 4: Remove a Replica

Module 9: MONITORING AND TROUBLESHOOTING AVAILABILITY GROUPS

• The Dashboard in Depth
• Events
• Policy Based Managemnt for Availability Groups

Lab 1: Dashboard Wizards

Lab 2: Create an Extended Event Session

Lab 3: Using T-SQL

Lab 4: Policy based management for Availability Groups

Lab 5: Observe a Policy In Action

Lab 6: Create Three Conditions To Be Used In The RTO And RPO Policies

Lab 7: Create Two Policies RTO and RPO

Lab 8: Test The Policies

Lab 9: Change Endpoint Owner