SQL Server 2019/2022 with High Availability – Always On

This 3-day course aims to discuss features in SQL Server that will use best practices in order to configure and able SQL Server on premise to be always available or up.

Course Outline

Day 1: Introduction to SQL Server Always On and Prerequisites

Module 1: Introduction to SQL Server High Availability (HA)

• Overview of High Availability and Disaster Recovery (HADR)

  • SQL Server HA Options: Failover Clustering, Log Shipping, Replication, and Always On Availability Groups

  • Scenarios requiring HA and DR in SQL Server

  • Overview of SQL Server Always On technologies (FCI and Availability Groups)

• SQL Server 2019 Architecture Overview

  • SQL Server instance architecture relevant to Always On

  • Database engine enhancements in SQL Server 2019

  • Impact of architecture on High Availability and performance

Module 2: Planning and Designing a SQL Always On Solution

• Prerequisites and Requirements

  • SQL Server Edition comparison (Standard vs. Enterprise features for HA)

  • System requirements (OS, hardware, SQL Server version, network)

  • Understanding Windows Failover Clustering (WFC)

  • Networking and DNS configurations for Always On

• Planning HA for Applications

  • Identifying databases for HA: Critical vs Non-critical

  • Designing Recovery Time Objective (RTO) and Recovery Point Objective (RPO)

  • Backup strategies with Always On

Module 3: Setting Up Windows Failover Clustering (WFC)

• Introduction to Windows Failover Clustering

  • Key concepts of WFC

  • Cluster Quorum Models

  • Node majority, File share witness, Cloud witness

• Step-by-step Failover Cluster Installation and Configuration

  • Setting up the cluster environment: Virtual or Physical

  • Network setup for the cluster

  • Adding Nodes to the Windows Failover Cluster

  • Validating cluster configuration

Laboratory 1: Installing and Configuring Windows Failover Clustering (WFC)

Objective:

Install and configure a Windows Failover Cluster, preparing for SQL Server Always On setup.

Prerequisites:

1. At least two virtual/physical machines with Windows Server installed (Windows Server 2019 Standard or Enterprise Edition).

2. Basic knowledge of networking and Windows Server.

Day 2: SQL Server Always On Availability Groups Configuration

Module 4: Introduction to Always On Availability Groups

• Always On Availability Groups Overview

  • Concept of Primary and Secondary replicas

  • Synchronous vs Asynchronous commit modes

  • Automatic failover vs Manual failover

• Configuring Availability Groups

  • Step-by-step walkthrough of creating Availability Groups in SQL Server 2019

  • Choosing databases for the Availability Group

  • Configuring primary and secondary replicas

  • Configuring Availability Modes and Failover Modes

  • Initial data synchronization

• Listener Configuration and Load Balancing

  • Creating and configuring Availability Group Listeners

  • Configuring Read-Only routing for load balancing

  • Configuring DNS for the Listener

Module 5: Backup and Recovery in Always On

• Backups in an Always On Environment

  • Backup strategies for databases in Availability Groups

  • Performing backups on Primary and Secondary replicas

  • Transaction log backups in Always On

• Restoring Databases in Availability Groups

  • Restoring a database to Availability Groups

  • Seeding secondary replicas after restoration

  • Managing database restores in large Availability Groups

Laboratory 2: Configuring Always On Availability Groups and Listeners

Objective:

Configure SQL Server Always On Availability Groups (AG) and Availability Group Listener.

Prerequisites:

1. SQL Server 2019 Standard or Enterprise Edition installed on both nodes with Always On enabled.

2. An existing Windows Failover Cluster.

Day 3: Monitoring, Troubleshooting, and Best Practices

Module 6: Monitoring and Managing Always On Availability Groups

• Monitoring Health and Performance

  • Using SQL Server Management Studio (SSMS) for Always On

  • Configuring Always On dashboards and reports

  • Monitoring replica synchronization health

  • Identifying potential issues using Dynamic Management Views (DMVs)

• Alerts and Notifications

  • Configuring alerts for Failover, Sync issues, and performance bottlenecks

  • Automating alerts and notifications using SQL Server Agent

Module 7: Troubleshooting Always On

• Common Troubleshooting Scenarios

  • Replica synchronization failures

  • Failover issues and connectivity problems

  • Resolving Listener connectivity issues

  • Cluster Quorum loss

• Understanding Failover Process

  • Manual vs Automatic failover process

  • Understanding quorum configuration in failover situations

  • Testing failover scenarios

• Module 8: Best Practices for Always On Configuration

  • Best Practices for Performance Optimization

  • Optimizing network and hardware configurations

  • Recommendations for replica distribution (Primary vs Readable Secondaries)

  • Using Read-Only routing efficiently

• Security and Compliance

  • Securing Availability Group communication

  • Implementing encryption and auditing in Always On

  • Compliance with disaster recovery requirements

Laboratory 3: Monitoring and Troubleshooting Always On Availability Groups

Objective:

Monitor, manage, and troubleshoot SQL Server Always On Availability Groups.

Prerequisites:

1. Configured Always On Availability Group from Laboratory 2.

NOTE:

1. This customized course is focused on AAG and WFC using MS SQL Server 2019

2. Standard/Enterprise Edition running on Windows Server 2019 Standard/Enterprise Edition.

3. Use Windows Server 2019 Standard/Enterprise Edition.

4. Download MS SQL Server 2019 Standard/Enterprise Evaluation copy.