-1

I have problem with MS SQL Always On Availability. Suppose the connection between primary and secondary replica is broken. The client inserts data to the primary database (replication cannot be done because of disconnection) that committed. Then the connection between client and primary database is disconnected. Now the client should be connected to the secondary database but it has not the last inserted data by the client. How does this problem can be solved?

3

The client inserts data to the primary database (replication cannot be done because of disconnection) that committed.

That can only happen if you configure the replica to be in Asynchronous-commit mode;

Asynchronous-commit mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances. If every secondary replica is running under asynchronous-commit mode, the primary replica does not wait for any of the secondary replicas to harden the log.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017

In that scenario you must choose between restoring service and losing data. If the secondary is in Syncronous-commit mode, the primary will wait on commit until the log records for the change are hardened on the secondary.

  • If a synchronous-commit secondary replica times out without confirming that it has hardened the log, the primary marks that secondary replica as failed. The connected state of the secondary replica changes to DISCONNECTED, and the primary replica stops waiting for confirmation from the secondary replica. This behavior ensures that a failed synchronous-commit secondary replica does not prevent hardening of the transaction log on the primary replica. link – Mohammad Afrashteh Aug 17 '19 at 19:03
  • Yes in that scenario you are running unprotected, which is deemed preferable from going offline. You can go offline instead if you configure cluster quorum to be lost in that case. – David Browne - Microsoft Aug 17 '19 at 20:21
2

Very simplified, but when the connection between primary and the replica is lost, one of them (primary or replica) will no longer have a quorum. AlwaysOn is designed that way that you should be able to have only one quorum at the time.
Node without the quorum will become not available for connections and wait until it can belong to quorum and data are synced.
So the scenario you described is not possible, client will not be able to connect to the replica after inserting data to primary.

  • I couldn't understand why it is impossible the client connects to the replica! After disconnection happened between primary and client so for availability client should connect to the replica. – Mohammad Afrashteh Aug 16 '19 at 23:46
  • When replica will lose connection the the quorum (assuming Primary will keep the quorum), it will change its state to DISCONNECTED and will not let the client connect. – Piotr Aug 19 '19 at 14:47
  • So in this situation what does mean availability?! We want to have several servers that in some disasters or usual disconnections, the system automatically uses secondary servers transparently to reach high availability. – Mohammad Afrashteh Aug 19 '19 at 17:29
  • It is your responsibility to build your cluster that way so when a node or part of network is down, surviving nodes can establish a quorum and then you can use secondaries. Or you can make a call and force replica to accept connections with potential data loss. – Piotr Aug 19 '19 at 18:02
0

I think, first you need to identity what causes disconnection between Primary replica and Secondary replica, also you must have alerts in place which notify as soon as the synchronization stopped with any error.

For troubleshooting and making stable connection between Primary - Secondary replicas..

Alerts:

USE [msdb]
GO

-- Alert when Always-On Data Movement Suspended
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn - Data Movement Suspended', 
        @message_id=35264, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=0, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO


USE [msdb]
GO

--- Alert when Role Changed 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn - Role Change', 
        @message_id=1480, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=0, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO


Now the client should be connected to the secondary database

I don't think you should do this since you already aware the secondary database stopped with replication.

Instead, you may want do anyone of following:

  1. Redirect client application connections to primary replica name/IP which pointing to Listener name, in this case you must bring AG online and switch client connection to listener name ASAP
  2. Announce the down-time and bring AG online (with above mentioned reference link), let client application connects on listener name as it was.

Not the answer you're looking for? Browse other questions tagged or ask your own question.