Configure SQL Server Always On Availability Group on Windows and Linux (Cross Platform) - Part 3

Test and verify both the ways to failover the primary replica

There are two ways to fail over the primary replica in an availability group with cluster type NONE.
    -  Forced manual failover with data loss
    -  Manual failover without data loss

Forced manual failover with data loss


Step #1: Promote the target secondary replica to primary

To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command

ALTER AVAILABILITY GROUP [AVG_Group1] FORCE_FAILOVER_ALLOW_DATA_LOSS;


When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a secondary role run the following command on the previous primary replica.


Step #2: Demote the primary replica to a secondary replica

Demote the primary replica to a secondary replica and resume data movement

ALTER AVAILABILITY GROUP [AVG_Group1]  SET (ROLE = SECONDARY);
ALTER DATABASE TESTDB1 SET HADR RESUME


After the failover, SQL-LINUX node is now primary server and SQL-WINDOWS server is readable secondary. Verify availability group role and status using Dashboard as show below


Verify that the database is getting populated on the secondary replica


Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. To avoid potential data loss,ensure that the target secondary replica is up to date.To manually fail over without data loss


Step #1: Set the replicas to synchronous commit


ALTER AVAILABILITY GROUP [AVG_Group1] MODIFY REPLICA ON N'SQL-WINDOWS' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP [AVG_Group1] MODIFY REPLICA ON N'SQL-Linux' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);



Step #2:  Set SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.

The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named AVG_Group1.

ALTER AVAILABILITY GROUP [AVG_Group1] 
SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1)



Step #3: Demote the primary replica to a secondary replica.

After the primary replica is demoted, it's read-only. To update the role to SECONDARY, run the following command on the SQL Server instance that hosts the primary replica:

ALTER AVAILABILITY GROUP [AVG_GROUP1] SET (ROLE = SECONDARY);



Step #4: Promote the target secondary replica to primary


ALTER AVAILABILITY GROUP [AVG_GROUP1] FORCE_FAILOVER_ALLOW_DATA_LOSS;


Verify that the database is getting populated on the secondary replica.


After the failover, SQL-WINDOWS node is back to primary server and SQL-LINUX server is readable secondary. Check the overall health and replica role for the database on both the secondary replica using Dashboard




No comments:

Post a Comment