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?
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.
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.
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 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.
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:
- Redirect client application connections to
primary replica name/IPwhich pointing to
Listener name, in this case you must bring AG online and switch client connection to
- Announce the down-time and bring AG online (with above mentioned reference link), let client application connects on
listener nameas it was.