Before proceeding, I would like to explain AG (Availability Group) so that it helps you to understand a real time scenario where we can utilize this article.

Always On Availability Groups

When the customer is having mission critical applications, as an engineer, we always focus on HA (High Availability) and (DR) disaster recovery. So, "Always On" is a simplified and unified solution to achieve the high availability and disaster recovery. Availability Group is the new concept that supports multiple database failovers as well as multiple active secondaries along with many other features.

SQL Server

Image Source: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

In simple terms, we have multiple SQL Servers and synchronized databases to protect customer data. As we are already aware that software always requires upgrades and patching to match the requirements as well as addition of new features, and it’s really easy to upgrade the stand alone SQL Server even in production environments. In case of Availability Group, it’s kind of tricky. So, I thought to provide steps which help to upgrade your SQL Server instances without any down time!

There are different types of topology of Availability Groups in industry. Here are a few examples -

Let’s take the simple topology. This diagram explains the customer setup.

SQL Server

Upgradation Steps

Change the Availability Mode to "Asynchronous" to avoid automatic failover.

These steps need to be performed from Primary replica (node) by connecting to SSMS (SQL Management Studio).

ALTER DATABASE database_name SET HADR RESUME

Helpful Links