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 -
- Availability group with remote secondary replica
- Availability group with failover cluster instance nodes
- Availability group with multiple secondary replica (Asynchronous commit on one of replica)
Let’s take the simple topology. This diagram explains the customer setup.
![SQL Server]()
Upgradation Steps
- Go to Properties of AG in SQL Server
![SQL Server]()
![SQL Server]()
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).
- Second step is to upgrade the secondary replica. [You may be upgrading from 2014 to 2016]
- Now, change the availability mode back to "Synchronous" from primary node.
- Wait until synchronization state shows as "Synchronized" in the dashboard of AG.
- Perform failover to interchange the primary node.
- Upgrade the current secondary node.
- Run the below resume command
ALTER DATABASE database_name SET HADR RESUME
Helpful Links
- https://msdn.microsoft.com/en-us/library/dn178483.aspx
- https://blogs.msdn.microsoft.com/sqlalwayson/tag/sql-server-2012/
- https://msdn.microsoft.com/en-us/library/mt735149.aspx