question

AshifShaikh-9988 avatar image
0 Votes"
AshifShaikh-9988 asked AshifShaikh-9988 answered

What if I have Asynchronous replica within the AG?? this query still shows Synchronizing for the Asynchronous replicas??

What if I have Asynchronous replica within the AG?? this query still shows Synchronizing for the Asynchronous replica i.e. Node3, Node4, Node5. Can't I move forward, what can I do at this time??

FYI, I have already completed the 1st step

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15&tabs=manual#failover

SELECT ag.name
, drs.database_id
, db_name(drs.database_id) as database_name
, drs.group_id
, drs.replica_id
, drs.synchronization_state_desc
, drs.last_hardened_lsn
FROM sys.dm_hadr_database_replica_states drs
INNER JOIN sys.availability_groups ag on drs.group_id = ag.group_id;

My Architecture overview:
Physical data center:

Node1 (Synchronous)
Node2 (Synchronous)
Node3 (Asynchronous)
Node4 (Asynchronous)
Node5 (Asynchronous)

Cloud:

Node1 (Synchronous)
Node2 (Synchronous)
Node3 (Asynchronous)

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AshifShaikh-9988 avatar image
0 Votes"
AshifShaikh-9988 answered

As per Kevin Farlee,

What matters is that the Forwarder (what would be Primary for the Secondary AG) is in a Synchronized state. As long as that is the case, any updates will flow to
the async secondary, and will not be lost.



https://github.com/MicrosoftDocs/sql-docs/issues/6413 -- for your reference




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AshifShaikh-9988,

Thanks for sharing the information. Your contribution is highly appreciated.

1 Vote 1 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered AshifShaikh-9988 converted comment to answer

Hi @AshifShaikh-9988,

Before manual failover, we need to set the distributed availability group to synchronous commit by running the following code on both the global primary and the forwarder. Change the node 3 from Asynchronous mode to synchronous mode.

 -- sets the distributed availability group to synchronous commit 
  ALTER AVAILABILITY GROUP [distributedag] 
  MODIFY 
  AVAILABILITY GROUP ON
  'ag1' WITH 
   ( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
   ), 
   'ag2' WITH  
   ( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
   );

In a distributed availability group, the synchronization status between the two availability groups depends on the availability mode of both replicas. For synchronous commit mode, both the current primary availability group, and the current secondary availability group must have SYNCHRONOUS_COMMIT availability mode. For this reason, you must run the script above on both the global primary replica, and the forwarder.

Wait until the distributed availability group is synchronized and has the same last_hardened_lsn per database. Then we can go to next step.

Please read all detail information from MS document Fail over to a secondary availability group.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @Cathyji-msft

Apologies, I missed specifying the other nodes i.e. Node4 and Node5 which are also Asynchronous. Afaik Only 2 secondary replica can be set as a synchronous besides all the other replicas should be set as Asynchronous.

Up to 8 secondary replicas, including 2 synchronous secondary replicas No No No No




Reference: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15

How can we handle this?? or is it that we just need to make sure the LSN are same on all the replica's rather than waiting for the Async Replica to become Sync Replica (Which is not possible in our case)

0 Votes 0 ·

Hi @AshifShaikh-9988,

What is your SQL server version? Please share the result of the query select @@version.

For SQL server 2016 and SQL 2017, on Enterprise Edition, provides support for up to 8 secondary replicas - including 2 synchronous secondary replicas.

For SQL server 2019, on Enterprise Edition, provides support for up to 8 secondary replicas - including 5 synchronous secondary replicas.

Refer to MS document.




0 Votes 0 ·

it is SQL server 2016 Enterprise Edition CU5


Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)
Jan 10 2019 18:51:38
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )

cc @Cathyji-msft

0 Votes 0 ·
Show more comments