Monday 10 June 2019

Configure SQL Azure Auto-Failover Group - Azure Portal

In addition to the Geo-Replication feature that Azure has Microsoft has built in an additional awesome feature call Auto-Failover Group to failover Group of Azure databases and all database in Managed Instance, the more import feature is that the endpoint for the primary and the secondary endpoint remains the same, I would not go into more details you can find more details on
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auto-failover-group 

Let see how it can be configured via Azure Portal,

My environment,

Primary SQL Instance - td-sqltestinstance
Region - UK West
Database Name - AdventureWorks2017

Secondary SQL Instance - td-sqlinstance-dr
Region - UK South


I am going to create a geo-replication first between the primary and the secondary server and allow geo-replication link to be inherited by the failover group.



I am adding UK South as my secondary server location and keeping the secondary Replica as a readable copy as below,


In the below Pic you can see my secondary read-only replica has been added, Primary Server can be used for all RW functions and Secondary read-only replica can be used for all reports.


Now lets move on and create a Failover Group for these 2 databases, on the settings of primary server td-sqltestinstance select Failover Groups and Add a Group


I am going name my Failover group as 'td-sqltestinstace-fo' this failover groups name acts like cluster virtual name / AG Listener Name so we do not need to change the server name setting in the application on failover. Select the database(s) which needs to be in the failover group.



The below pic shows the failover group has been created and the databse added to the failover group, Another important point is to note the RW Listener endpoint and Read-only Listener Endpoint. These 2 name will remain constant even in failover so the application need not change anything in case of failover this is a significant improvement from geo-replication where we have to change the servernames in case of failover.






Now that we have created the Failover group, Let us try manually failing over manually,



As you can see below, UK South has now become primary and UK west is now secondary read only replica but the Listener endpoints remains the same.

Hope the above article help, I am also sharing the Microsoft link for various topologies we can use with the Failover groups its worth a read before designing your DR solution.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-designing-cloud-solutions-for-disaster-recovery