Auto-failover groups can be used for business continuity to switch database(s) to secondary location automatically in case of primary failure. failover group allows to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region.
Failover groups are built on top of Geo-Replication and provide automatic failovers for your applications, this is achieved by azure created listeners.
In addition, auto-failover groups provide read-write and read-only listener endpoints that remain unchanged during failovers. Whether you use manual or automatic failover activation, failover switches all secondary databases in the group to primary. After the database failover is completed, the DNS record is automatically updated to redirect the endpoints to the new region.
Best practices for SQL Database
The auto-failover group must be configured on the primary server and will connect it to the secondary server in a different Azure region. The groups can include all or some databases in these servers. The following diagram illustrates a typical configuration of a geo-redundant cloud application using multiple databases and auto-failover group.
Let’s Create Failover Group
- Go to existing Managed SQL server Instance or Create New on Azure.
- Click Failover groups under “Data management”
- Click on ‘Add Group’
- Enter failover group name, Select secondary server or create new if secondary doesn’t exists.
- Failover policy select “Automatic”, can be changed later if needed.
- Read/Write grace period is telling the system for wait for some specific period of time before initiating a failover, default is 1 hour.
- Click “create” to Add Failover group.
Test Failover(Manual)
To test manual failover, go to failover group and click ‘Failover’’, this will initiate the switch of roles for the primary and secondary, there is also “Forced Failover”, that initiates the failover immediately without waiting for the grace period.
Useful Links:
https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview
Conclusion:
To achieve business continuity, adding database redundancy is part of the solution, recovering application end-to-end after a catastrophic failure requires recovery of all components that constitute the service.
Thank you
Srinivasa Avanigadda
Azure SQL Server auto-failover groups.
Tweet
Srinivasa Avanigadda 