May 29, 2016 at 7:01 pm
Hi,
We are considering using availability groups for our solution. It will have 4 SQL Server 2014 instances running on 4 cluster nodes, spread across two cities (two nodes in each city).
All those instances will be online at all times. One will be the primary, two others will be synchronous replicas and one last - asynchronous replica.
We are concerned about one scenario: should the link between two cities go down, one of the synchronous replicas, which is in another city would not be able to get updated and the transaction will fail on the primary replica. We can't allow this to happen, as the system must be available for read and write at all times.
Our solution must have zero data loss, so I was wondering if there is a fix for this scenario.
Thanks.
May 30, 2016 at 7:41 am
Replicas being offline will NOT cause a transaction on the primary to fail. They will simply queue up on the primary and catch-up when the secondary that was offline become available again (assuming just a transient disconnect/outage where the secondary is still in it's pre-outage state SQL Server wise). Do note that your transaction log will be unable to flush committed transactions from VLFs until the secondary(s) are caught up though.
Please note that doing failover cluster instances and AGs is all kinds of persnickety.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 12:00 am
TheSQLGuru (5/30/2016)
Replicas being offline will NOT cause a transaction on the primary to fail. They will simply queue up on the primary and catch-up when the secondary that was offline become available again (assuming just a transient disconnect/outage where the secondary is still in it's pre-outage state SQL Server wise). Do note that your transaction log will be unable to flush committed transactions from VLFs until the secondary(s) are caught up though.Please note that doing failover cluster instances and AGs is all kinds of persnickety.
I thought the definition of a synchronous replica is the one, that will fail the transaction on the primary replica if it can't commit on the secondary. Asynchronous replica will continue to queue up until the link is available again.
They are not failover instances, they can't fail over from one node to another. Instead there are 4 independent instances, each having either a primary or secondary replica. The failover will be happening on the availability group level. It is my understanding that AG need windows cluster to operate.
May 31, 2016 at 8:05 am
Roust_m (5/31/2016)
TheSQLGuru (5/30/2016)
Replicas being offline will NOT cause a transaction on the primary to fail. They will simply queue up on the primary and catch-up when the secondary that was offline become available again (assuming just a transient disconnect/outage where the secondary is still in it's pre-outage state SQL Server wise). Do note that your transaction log will be unable to flush committed transactions from VLFs until the secondary(s) are caught up though.Please note that doing failover cluster instances and AGs is all kinds of persnickety.
I thought the definition of a synchronous replica is the one, that will fail the transaction on the primary replica if it can't commit on the secondary. Asynchronous replica will continue to queue up until the link is available again.
They are not failover instances, they can't fail over from one node to another. Instead there are 4 independent instances, each having either a primary or secondary replica. The failover will be happening on the availability group level. It is my understanding that AG need windows cluster to operate.
You said 4 cluster nodes. My apologies I took that to be Failover Cluster. There is no need to mention clustering when speaking of AGs since we all know you have to have that feature enabled to set up AGs.
Since you have things set up it is easy to do your own test. Create a small database and make a sync AG for it. Do some DML in a loop then pause the AG to see what happens.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 1:48 pm
Roust_m (5/29/2016)
Hi,We are considering using availability groups for our solution. It will have 4 SQL Server 2014 instances running on 4 cluster nodes, spread across two cities (two nodes in each city).
All those instances will be online at all times. One will be the primary, two others will be synchronous replicas and one last - asynchronous replica.
We are concerned about one scenario: should the link between two cities go down, one of the synchronous replicas, which is in another city would not be able to get updated and the transaction will fail on the primary replica. We can't allow this to happen, as the system must be available for read and write at all times.
Our solution must have zero data loss, so I was wondering if there is a fix for this scenario.
Thanks.
with an even number of nodes in the cluster and even number of nodes on each site choose your quorum model carefully.
Remove the vote from one of the nodes on the secondary site will provide instant Majority Node Set quorum for you, however, choose carefully
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 1, 2016 at 11:50 am
Perry, would you mind elaborating a little on your last comment in this thread.
Regarding the quorum model, and choosing carefully.
Or point to a good reference...
Cheers
Alex
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply