16.5 fail-Over

16.5 fail-Over

16.5.1 Fail-over mode

When you create a new availability group, automatic failover and synchronous commit are not selected by default, and you can specify automatic failover (up to 2 replicas) and synchronous commit (up to 3 replicas) based on your actual business needs.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/69/wKioL1V77YmiYe7AAAKlQ_IZyGc532.jpg "title=" Availability replicas. png "alt=" wkiol1v77ymiye7aaaklq_izygc532.jpg "/>

Similar to the availability mode for database mirroring, AlwaysOn availability groups can be selected in the following 3 modes of high availability.

(1) automatic failover and synchronous commit. When automatic failover is selected, synchronous commit is automatically selected at the same time. This replica will use synchronous-commit availability mode while supporting automatic failover and manual failover without data loss.

(2) manual failover and synchronous commit. That is, select synchronous commit only. This replica will use synchronous-commit availability mode, support manual failover only, and no data loss.

(3) manual failover and asynchronous commit. Either "Automatic failover" and "synchronous commit" are not selected. This replica will use the asynchronous commit availability mode and only support forced failover (data loss may be possible).

In the properties of the availability group, you can also edit the availability mode and failover mode. If failover mode is set to Automatic, but availability mode is not automatically set to synchronous commit, manual confirmation is required.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/6A/wKioL1V79I3Sc0Z2AAL3AgnVtpk390.jpg "title=" Property 2. PNG "alt=" wkiol1v79i3sc0z2aal3agnvtpk390.jpg "/>

For a secondary replica that is committed synchronously, its synchronization status is displayed as synchronized, and for the secondary replica of the asynchronous commit, the synchronization status is displayed as synchronizing.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6E/wKiom1V7-u-g8uE0AAUKsvo5lFo139.jpg "title=" panel Sync status. png "alt=" wkiom1v7-u-g8ue0aauksvo5lfo139.jpg "/>

16.5.2 automatic failover

When a primary replica is detected to fail, the failover Cluster service automatically transfers to a secondary replica that has failover mode automatic.

16.5.3 manual failover

Select an availability group in SSMS, and then select Failover in the right-click menu. There is also a link to the Start Failover Wizard in the display panel.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/6A/wKioL1V79tngltgpAATYAoGNqWE379.jpg "style=" float: none; "title=" Failover 1.png "alt=" Wkiol1v79tngltgpaatyaognqwe379.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/6E/wKiom1V79S_gr1AyAAH-4cGMEHE620.jpg "style=" float: none; "title=" Failover 2.png "alt=" Wkiom1v79s_gr1ayaah-4cgmehe620.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6A/wKioL1V79trxu0LNAAId2b9uQSs484.jpg "style=" float: none; "title=" Failover 3.png "alt=" Wkiol1v79trxu0lnaaid2b9uqss484.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/6E/wKiom1V79TCAAGBzAAGBPuDHoUs253.jpg "style=" float: none; "title=" Failover 4.png "alt=" Wkiom1v79tcaagbzaagbpudhous253.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6A/wKioL1V79tugZ6SJAAGVnwkCC6E328.jpg "style=" float: none; "title=" Failover 5.png "alt=" Wkiol1v79tugz6sjaagvnwkcc6e328.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6E/wKiom1V79THhD_RkAAH2VRq8nLI687.jpg "style=" float: none; "title=" Failover 6.png "alt=" Wkiom1v79thhd_rkaah2vrq8nli687.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/6A/wKioL1V798_BqFp0AAHhGW7B9fU701.jpg "title=" fail over 7. PNG "alt=" wkiol1v798_bqfp0aahhgw7b9fu701.jpg "/>

To do this, the script is:

---You must EXECUTE the following SCRIPT in SQLCMD MODE. : Connect SQLSVR2 ALTER availability GROUP [HAGROUP01] FAILOVER; GO

16.5.4 Forced transfer

When a primary replica fails, and an automatic failover fails, you may need to force a secondary replica to be promoted to the primary replica.

You need to execute the following script on the secondary replica. This operation may result in data loss, please use with caution.

ALTER availability GROUP [HAGROUP01] force_failover_allow_data_loss;

After a forced switchover, the database on the new primary replica appears synchronized, and the database on the other secondary replicas appears as "not synchronized."

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/6A/wKioL1V7-PGRU7h8AAS-G0Qcm-U473.jpg "title=" Force transfer. png "alt=" wkiol1v7-pgru7h8aas-g0qcm-u473.jpg "/>

After a forced transfer, when the secondary replica comes back online, you can run the following script on the secondary replica separately to synchronize data updates.

ALTER DATABASE [SQLDB01] SET HADR RESUME; ALTER DATABASE [SQLDB02] SET HADR RESUME;

After successful execution of the above script, the database status on the secondary replica is displayed as synchronized (synchronous-commit mode) or synchronizing (asynchronous-commit mode).

This article from "SQLServer2014 series" blog, declined reprint!

16.5 fail-Over