0

I have the below set up for availability group :

Always on configured in SQL Server 2017:

  • Primary is read write and secondary is read only.
  • Both primary and secondary are configured with asynchronous commit and manual failover

The set up is only for DR purpose and for reporting. It's not configured using a cluster. We have not failed over nor want to fail over to secondary.

I need help in steps to follow if I have to apply patches on both primary and secondary.

Recently when applying patches, always on broke on and had to reconfigure. Kindly advise the best possible way to reboot the primary and secondary servers without failover .

Thank you for all the possible options and solutions . But the business requirement is not to do any failover .

The issue is recently after windows patch is applied and server is rebooted , always on primary replica is going to resolving status.

It’s databases are in status of not synchronizing / pending recovery.

Always we are ending up re- doing the AG configuration.

The steps we followed are - stop data movement from primary and secondary , stop sql services , after reboot , start sql services and resume data movement .

Is there anything which should be avoided or anything which needs to be done additionally in order to overcome primary AG breaking ? Failover is not a option for us ( not my scope to request that requirement to change as it’s management decisions ) kindly need help

Improve this question
2
  • Why don't you want ot fail over to the secondary? This is the method by which you can patch the primary without any downtime.
    – sTTu
    Aug 31, 2021 at 19:49
  • Can you elaborate on what you mean by "Always On breaks" - once you resume data movement does it cause errors that indicate data movement has not or cannot resume?
    – HandyD
    Sep 2, 2021 at 21:08

1 Answer 1

Reset to default
2

There is no possible way to avoid the primary going down when you apply patches or updates to the primary.

Since you have manual failover setup, when the primary goes down the database is offline and inaccessible until you either failover manually or bring the primary back online.

I would suggest having two servers in the availability group that can serve as the primary or secondary, with synchronous commit between the two, and automatic failover. Then, if the current primary has an issue, the secondary will extremely quickly take over, and continue providing read-write access to the database. You could also have a third readable secondary that you use for reporting, or D/R.

With the setup I note above, you would apply patches to the non-primary member(s) first, then either manually or automatically failover to the patched secondary, and patch the original primary.

For further information, please take a look at Overview of Always On Availability Groups which states the following salient point:

Always On availability groups provide high availability, disaster recovery, and read-scale balancing. These availability groups require a cluster manager. In Windows, failover clustering provides the cluster manager. In Linux, you can use Pacemaker. The other architecture is a read-scale availability group. A read scale availability group provides replicas for read-only workloads but not high availability. In a read-scale availability group there is no cluster manager.

You appear to have setup a read-scale availability group, which provides no high-availability capabilities.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy