1

By default, secondary replica brings databases into offline state when connection to the primary replica fails. We are using SQL 2016 SP1.

The goal is to reduce interruption for users on secondary async replica when primary replica goes offline for maintenance or due to network issues.

Is there a way to change this behavior and keep secondary databases always online ?

Chaminda Bandara
  • 191
  • 3
  • 17
Alex Bochkov
  • 165
  • 10

1 Answers1

3

Is there a way to change this behavior and keep secondary databases always online?

Yes. Use SQL 2014 or later :)

Beginning in SQL Server 2014 (12.x), readable secondary replicas can remain online even when the primary replica is offline due to user action or a failure. However, read-only routing does not work in this situation because the availability group listener is offline as well. Clients must connect directly to the read-only secondary replicas for read-only workloads.

Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
  • This is interesting and I was under assumption that a role reversal happens on all the nodes for a brief time which is downtime for app. Good info @DavidBrowne-Microsoft. – Kin Shah May 05 '18 at 20:20
  • You are right - I did some testing and confirmed that secondary databases are staying readable if connection to the primary node failed. I wonder if there are any exceptions, because we definitely had some cases when secondary databases were down as well, but that was cluster-wide failures. Thanks for reply! – Alex Bochkov May 07 '18 at 17:26
  • One exception is where the instance hosting the replica is a Failover Cluster Instance, which requires cluster quorum for the instance to run. – David Browne - Microsoft May 07 '18 at 23:50