Working with Azure SQL Auto Failover group

Article
1/17/2024

On 11th May, 2017 Microsoft announced the public preview of auto-failover groups that extend. Introduction of this feature, removes the hassle of changing the ConnectionString in the application after failover. It was a very much needed feature towards providing high availability.

Today we will go through a step by step process to see how we can create a failover group and see how it will make our life easy by introducing the Listeners end-points for Primary (read-write) and secondary (read-only) servers.

Currently Azure portal does not provide the capability to create failover group. In order to create failover group, user needs to either use the Azure Resource Manager based Azure SQL REST API or Azure SQL Database PowerShell cmdlets.

For this article I would be using the Powershell cmdlets to create the group and use it.

I am going to use 1 primary node, which will have read/write database and two secondary read only geo-replica.

Step 1:
Use the cmdlet New-AzureRMSqlDatabaseFailoverGroup to create the failover group
This cmdlet requires following inputs

ResourceGroupName: Resource group under which the failover group will be created
ServerName: Name of the Primary SQL server Node. Provide only the name and not the fully qualified server name (exclude database.windows.net)
PartnerServerName: Name of the Secondary server Node. Provide only the name of server
FailoverGroupName: Name of the failover group. Keep all characters lower case in the name of group else you will receive below error (InvalidFailoverGroupName: Failover Group name cannot be empty or null. It can only be made up of lowercase letters 'a'-'z', the numbers 0-9 and the hyphen.
The hyphen may not lead or trail in the name.)
FailoverPolicy: Failover policy that you want to apply. Keep the value as Automatic
GracePeriodWithDataLossHours: Time before the automatic failover happens in case failover cannot be completed without data loss. Default value is 1

New-AzureRMSqlDatabaseFailoverGroup –ResourceGroupName "SQL_Azure_Failover_Group" -ServerName "primarynode" -PartnerServerName "sqlsecondarynode" `
–FailoverGroupName "azure-auto-failovergroup" `
–FailoverPolicy Automatic `
-GracePeriodWithDataLossHours 2

Once the command runs successfully, following will be returned on the screen

FailoverGroupName : azure-auto-failovergroup
Location : West US
ResourceGroupName : SQL_Azure_Failover_Group
ServerName : primarynode
PartnerLocation : West US 2
PartnerResourceGroupName : SQL_Azure_Failover_Group
PartnerServerName : sqlsecondarynode
ReplicationRole : Primary
ReplicationState : CATCH_UP
ReadWriteFailoverPolicy : Automatic
FailoverWithDataLossGracePeriodHours : 2
DatabaseNames : {}

At this point, the failover group is created and the two listeners end points are created, one for primary and another for secondary.
Format of Primary and Secondary listeners are
Primary : <FailoverGroupName>.database.windows.net
Secondary : <FailoverGroupName>.secondary.database.windows.net

Note: If at this point you go to SSMS and try to connect to your Primary/Secondary database using above listeners, you will receive error and will not be able to login.
Ideally it should have allowed, but it currently fails, as it tries to connect to the Master database which is currently not part of the group. This is currently being worked upon and should be resolved soon. Till then, workaround is to provide the database name while connecting to server. Use the option button to provide database name.

Step 2
After the group has been created, now we need to add one or more databases to the failover group.
Get-AzureRmSqlDatabase : Gets the database details that needs to be added to group
Add-AzureRmSqlDatabaseToFailoverGroup : Adds the database to the failover group specified

Get-AzureRmSqlDatabase -ResourceGroupName "SQL_Azure_Failover_Group" -ServerName "primarynode" -DatabaseName "TestDB" | Add-AzureRmSqlDatabaseToFailoverGroup -ResourceGroupName "SQL_Azure_Failover_Group" -ServerName "primarynode" -FailoverGroupName "azure-auto-failovergroup"

Once the command runs successfully, following will be returned on the screen, which shows the newly added database(s).

At this point we are done with the creation of failover group and addition of database(s) to the group.
Let's try to connect to primary and secondary database using the listeners end-point. Ensure to create a firewall rule for your IP address for primary and secondary servers

Below image shows connection to primary node using the read-write endpoint listener.

Below image shows connection to primary node using the read-only endpoint listener.

Now let's trigger the manual failover to secondary server.

Note: For the demo purpose, we are triggering the manual failover, but in real-world if primary goes down, same set of events will occur. In our case primary will immediately be failed over to secondary, in real-life scenarios, when primary is up after failure, it will become active read-only secondary. To make it primary again, we will need to perform manual failover.

Step 3
To perform the manual failover, cmdlet is Switch-AzureRMSqlDatabaseFailoverGroup

Switch-AzureRMSqlDatabaseFailoverGroup -ResourceGroupName "SQL_Azure_Failover_Group" -ServerName "sqlsecondarynode" `
-FailoverGroupName "azure-auto-failovergroup"

Once the command runs successfully, below cmdlet can be used to check the configuration after failover

Get-AzureRMSqlDatabaseFailoverGroup -ResourceGroupName "SQL_Azure_Failover_Group" -ServerName "primarynode"

Once the cmdlet runs successfully, following will be returned on screen. As we can see now the Replication role of server primarynode is changed to Secondary
FailoverGroupName : azure-auto-failovergroup
Location : West US
ResourceGroupName : SQL_Azure_Failover_Group
ServerName : primarynode
PartnerLocation : West US 2
PartnerResourceGroupName : SQL_Azure_Failover_Group
PartnerServerName : sqlsecondarynode
ReplicationRole : Secondary
ReplicationState : CATCH_UP
ReadWriteFailoverPolicy : Automatic
FailoverWithDataLossGracePeriodHours : 2
DatabaseNames : {TestDB}

In the above cmdlet, we can change the server name to that of sqlsecondarynode to confirm that now it's replication role is set to primary.

Geo-replication state after failover as displayed in portal.

Now let's go to SSMS and see where the end-points are pointing.
As depicted in below image, the connection string remains the same, but it is now pointing to sqlsecondarynode, which is now read-write node

And if you connect using the read-only connection string, it should ideally point to server primarynode, as it is still active. This is not happening currently.
Note: Read-only connectionstring not pointing to new secondary node is a known issue which is currently being addressed and should be available with GA release.

Hope this helps!. Please feel free to drop your comments/feedback.

Share via

Working with Azure SQL Auto Failover group

Additional resources