Sql-server – Failover from Primary to Asyn Secondary Replica

availability-groupsdata synchronizationdisaster recoveryfailoversql-server-2012

In my environment we have 1 Primary 1 Synchornous Secondary and 1 Asynchronous Secondary Replica (Off site). We are planning to do a manual failover to the Asynchronous Secondary Replica (Off site) as the Primary data center will be down due to maintenance.

My question is will there be any problem if I manually change the Availability Mode to Synchronous mode from Asynchronous mode and do the failover

Best Answer

No. It is not a problem.

Just to it in a low intensive moment. And waith sync is complete. You can check it via interface.

Otherwise you can use this query (checked in SQL 2016):

SELECT ag.NAME
    ,drs.database_id
    ,drs.group_id
    ,drs.replica_id
    ,drs.synchronization_state_desc
    ,drs.end_of_log_lsn
FROM sys.dm_hadr_database_replica_states drs
    ,sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

synchronization_state_desc must be: SYNCHRONIZED.

Then you can switch.

Related Question