Autoplay
Autocomplete
Previous Lesson
Complete and Continue
SQL Server Always On Availability Groups: The Senior DBA’s Ultimate Field Guide
Program Introduction
What this training is all about
How to get the most out of this training course
Windows Server Failover Clustering Fundamentals
What is a high availability cluster? (6:46)
High Availability: Hardware-level, Operating System-level and Application-level (6:04)
3 Common Types of Microsoft® High Availability Clusters for SQL Server Workloads (12:27)
Assessment
The Traffic Light Analogy: How Windows Server Failover Clustering Works (7:01)
Windows Server Failover Clustering Components: Nodes (3:19)
Windows Server Failover Clustering Components: Cluster Resources (9:35)
Windows Server Failover Clustering Components: Resource Groups (5:12)
Failover and Failback (7:14)
Dependencies (5:50)
Windows Server Failover Clustering Components: Quorum (10:52)
Windows Server Failover Clustering Components: Heartbeat (3:30)
The “split brain” Phenomenon (6:34)
Planning and Installing a Windows Server Failover Cluster
Hardware Requirements for Installation (16:14)
Active Directory and DNS Requirements for Installation (12:20)
Planning: Asking The Right Questions (22:07)
Cluster Preparation Checklist (4:48)
Preparing The Servers: Adding the Failover Cluster Feature and Failover Cluster Validation
Creating the Windows Server Failover Cluster
Granting Permissions in Active Directory
Pre-staging the Virtual Computer Objects in Active Directory
Using Windows PowerShell to Validate and Create a WSFC
Deep Dive on Windows Server Failover Cluster Quorum
Quick Recap on Quorum
Requirements for a Disk Witness
Requirements for a File Share Witness
Requirements for a Cloud Witness
Difference Between Disk Witness and File Share Witness
Partition In Time
Typical Events Leading Up To Quorum Issues
Dynamic Quorum Feature
Quorum Behavior with Dynamic Quorum
Dynamic Witness Feature
Quorum Behavior with Both Dynamic Quorum and Dynamic Witness
Dynamic Quorum and Dynamic Witness In Action with a File Share Witness
When the Dynamic Quorum and Dynamic Witness Features Might Not Appear To Work
Single Data Center versus Multi-Data Center Considerations: Choosing Between Automatic or Manual Failover
Single Data Center versus Multi-Data Center Considerations: Choosing a Witness Type
Windows Server Failover Cluster Configuration
How the Create Cluster Wizard Chooses Quorum Configuration
Verifying and Modifying Quorum Configuration
Configuring Cloud Witness using Azure Blob Storage
Understanding Windows Server Failover Cluster Networking
Configuring Windows Server Failover Cluster Networking
Testing Failover
Understanding Active Directory and DNS Registration Behavior and How It Affects Multi-subnet Windows Server Failover Clusters
Configuring DNS Registration Behavior for Multi-subnet Windows Server Failover Clusters
Understanding Cluster Heartbeat Settings
Configuring Cluster Heartbeat Settings
Applying the Concepts of Site-awareness for Multi-Subnet/Site Configuration
Running Windows Server Failover Clusters on VMWare
Summary & Assignments
SQL Server Always On Availability Groups Fundamentals
SQL Server Always On Availability Groups: Basic Architecture
How SQL Server Always On Availability Groups Work
Components and Definitions: Availability Group
Components and Definitions: Replicas
Components and Definitions: Availability Group Listener
Components and Definitions: Availability (Replication) Modes
Components and Definitions: Availability Group Endpoint
How Always On Availability Group Failover Works
Automatic Failover Without Data Loss
Planned Manual Failover Without Data Loss
Forced Manual Failover With Possible Data Loss
How Is Data Loss Possible with Forced Manual Failover?
What Happens if a Synchronous Secondary Replica Goes Offline?
What Happens if an Asynchronous Secondary Replica Goes Offline?
Summary
Planning and Deploying SQL Server Always On Availability Groups
Planning Always On Availability Group Deployments: Asking The Right Questions
SQL Server Availability Group Requirements
This Will Happen If You Don’t Design Your Architecture Properly
4 Important Questions You Need To Ask For Implementation
Common Design Patterns
Licensing Changes in SQL Server 2019 (and higher) That Will Influence Your Design
Time to Update the Cluster Preparation Checklist
Building a 4-replica SQL Server Availability Group
Assignment #1
Combining SQL Server Failover Clustered Instances with Availability Groups
Viewing Availability Group Properties
Testing Application Connectivity and Availability Group Failover
Configuring Dedicated Network for Availability Group Replication Traffic
What is a Distributed Availability Group?
Configuring a Distributed Availability Group
Deploying Basic Availability Groups
Deploying SQL Server Availability Group without Active Directory
My Favourite Tool for Network Connectivity Testing
Assignment #2
Configuring SQL Server Always On Availability Groups
Replicating SQL Server Logins
Replicating SQL Server Agent Jobs
Availability Groups as Failover Cluster Resource Groups
Understanding Cluster Resource Dependency Behavior
Viewing Cluster Resource Dependencies
Configuring Resource Group-level Policies
Configuring Resource-level Policies
Configuring Availability Groups Listener Name DNS settings for Multi-subnet Failover Clusters
How Readable Secondary Replicas Work
Configuring Readable Secondary Replicas
The extra 14-bytes that Microsoft probably didn’t tell you about
How Read-only Routing Works
Configuring Read-Only Routing
How Load Balancing of Read-Only Workloads Works
Configuring Load Balancing of Read-Only Workloads
Summary
Managing SQL Server Always On Availability Groups
Running Integrity Checks: DBCC CHECKDB
Configuring Backup Preferences
Adding or Removing a Database in the Availability Group
Adding or Removing a Secondary Replica
Modifying Availability/Replication Modes
Temporarily Suspending and Resuming Data Synchronization
Dealing With Different Database File Paths Between Replicas
Performing Planned Manual Failover of Availability Groups
Performing Forced Manual Failover of Availability Groups
How the Flexible Failover Policy Affects High Availability
Configuring Flexible Failover Policy
Modifying Session Timeout Period
Performing Index Maintenance
Managing Statistics
Installing Service Packs and Cumulative Updates
Installing Service Packs and Cumulative Updates – Part 2
Summary & Assignment
Monitoring SQL Server Always On Availability Groups
What the Availability Group Dashboard is Telling You
Relevant Performance Counters and Wait Statistics to Watch Out For
Windows Server Failover Clustering Internals
Understanding sp_server_diagnostics in the context of WSFC Health Detection
Windows Server Failover Clustering Internals in Action
R.A.P. Monitoring
The AlwaysOn_health Extended Events Session
The suspect_pages table and Automatic Page Repair
Summary & Assignment
Troubleshooting Common Issues in SQL Server Always On Availability Groups
Workflow Process for Troubleshooting Availability Issues
Troubleshooting Availability Group Outages: Step 1
Troubleshooting Availability Group Outages: Step 2
Troubleshooting Availability Group Outages: Step 3
Troubleshooting Availability Group Outages: Step 4
Troubleshooting Availability Group Outages: Step 5
Force Start a Failover Cluster Without Quorum
Most Common Deployment and Configuration Issues
Dealing with 35250 Errors
Troubleshooting Availability Group Listener Creation Failure
Troubleshooting Read-only Routing Issues
Most Common Operational Issues
Dealing with Listener Connection Failures
Handling Uncontrollable Log File Growth
Database and/or Replica Synchronization Issues
Why Did Failover Take A Long Time?
When Automatic Failover Does Not Appear To Work
Troubleshooting Exercise #1
Troubleshooting Exercise #2
Troubleshooting Exercise #3
Summary
Teach online with
SQL Server Always On Availability Groups: Basic Architecture
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock