SQL Server AlwaysOn Rolling Upgrade with Powershell & DBATools

Upgrading SQL Server in High Availability and Disaster Recovery (HADR) environments is a routine task for database administrators. More importantly, these upgrades often need to be performed with minimal or no downtime. 😊 Fortunately, Microsoft introduced rolling cluster upgrades in Windows Server 2016, allowing the operating system in a Windows cluster to be upgraded without stopping cluster services.

Additionally, rolling cluster upgrades can be combined with SQL Server AlwaysOn rolling upgrades, enabling database administrators to upgrade SQL Server HADR environments with minimal downtime. In this post, we will demonstrate how to upgrade a Windows Server 2016/SQL Server 2017 AlwaysOn cluster to Windows Server 2019/SQL Server 2019.

Prerequisites

Before Proceeding, ensure you have the following:

• Oracle VirtualBox is downloaded and installed.
• Working knowledge of:
  • Windows Failover Clustering
  • SQL Server AlwaysOn Availability Groups
  • Active Directory
  • DBATools
  • PowerShell
• Preconfigured environment:
  • Windows 2019 or Windows 2016 Domain Controller
  • A two-node Windows Server 2016 failover cluster with SQL Server 2017 AlwaysOn
  • A configured witness server
  • Two additional servers with Windows Server 2019 and SQL Server 2019 installed

Instructions

Part 1. Add Windows 2019 servers to cluster

Part 2. Add SQL 2019 instances to availability group

Part 3. Perform Rolling upgrade