Overview
This 3-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 and SQL 2014.
Prerequisites
Those who wish to attend this course should meet the following prerequisites:
- Experience as SQL DBA
- Experience as Windows IT professional
Who Should Attend?
This lab-intensive course is recommended for experienced DBAs, Windows Server professionals and team leads.
Course Outline
- Course Introduction
- 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
- 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 AlwaysOn?
- Table of AlwaysOn Comparison
- 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
- Dependencies
- 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 Initiators to add the shared storage
Lab: Create the Windows Cluster
Lab: Add a Clustered Service
Lab: Test a Failover of the Windows Service
Lab: Delete Role
Lab: Examine the Quorum Settings
- 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
- Availability Groups and Replicas
- Primary Replica
- Secondary Replicas
- Availability Group Listener
- Availability 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
- 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
- Reporting with Secondary Replicas
- Configuring a Readable secondary
- Read-Only Routing
- Load Balancing
- Database Backups with Secondary
- Steps of Backup Using secondary
- Backup Preference Options
Lab: Database Backup Using Secondary Replica
Lab: Configure a Read-Only secondary
- DBCC Checks
- Database Adding and Removing
Lab: Add a Database
Lab: Remove a Database
Lab: Add a Replica
Lab: Remove a Replica
- The Dashboard in Depth
- Events
- Policy Based Management for Availability Groups
Lab: Dashboard Wizards
Lab: Create and 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
