Enabling Auto-Failover Group on Azure SQL

Today in Article I will explain How we can enable the Auto Failover group on Azure SQL and what is different between Auto-Failover Group and Active Geo-Replication in Azure SQL. Before starting in How to implement the Azure SQL Auto Failover Group I recommend you to take a look on this ➡️ POST to understand How you can configure Active Geo-Replication on Azure SQL and for more Azure SQL articles check this ➡️ LINK and for more other Azure Posts and articles check this ➡️ LINK for all of my azure articles

Active Geo-Replication VS Failover Group
How to enable Auto-Failover Group on Azure Single SQL Server
Check the Failover Group Configuration and Connections
Test the Failover
Conclusion
Resources
Follow Me on Social Accounts

Active Geo-Replication VS Failover Group

Active Geo-Replication is Azure SQL Database Feature allows you to replicate one Database only this meaning if you have 3 databases as our example here and you choose to build Active geo-replication at this time you should configure it on each database individually, Active geo-replication give you the ability to replicate your DB Up to four secondaries in the same or different regions

Auto-failover group is Azure SQL Database Feature allows you to replicate all databases or what you need to choose from the database to another secondary server, this means you can manage your replication and failover on Group of databases. Not like Active geo-replication, Auto-failover groups support replication of all databases in the group to only one secondary server in a different region. And the secondary server should be in the same subscription, different subscription sill not supported in Single Azure SQL and Elastic pool, With Auto-failover groups Feature after configuration you can remove or add a new database easily from the group of the databases.

How to enable Auto-Failover Group on Azure Single SQL

In my example, I have Azure SQL Server “elmasrycloudserver” hosted on “East US 2” Region and it contains 3 databases I will enable the auto-failover on the 3 databases to be replicated on new Azure SQL named “elmasrycloudserver2” and it will be hosted on “East US” as mentioned before that we cannot host the primary and secondary server on the same region while using Failover Group and the secondary server we can create during this configuration if you don’t have a secondary server from the first

Auto-failover groups in Azure SQL | Data Exposed

Open Azure Portal in the search write “Azure SQL”
Select your Azure SQL Server (Primary Server) as you can see below, I have one azure SQL and 3 databases on it that I will configure the Failover Group on it

In the left side, you will find Failover Groups Select it
In the Top select Add Group in this section, we need to write the Failover Group Name and this will be the Listener name Like SQL Server Always on and the connection of it will be “FailoverGroupName.database.windows.net” this connection will direct you or the application to Read Write server “Primary Server”
In the same section, you can select the secondary server if you have or you can create a new one.
In the same section, you will be able to select the database you need to include it in the failover group, after creating the Failover group you can add a new Database or you can remove a database from the Failover Group easily.

After completing the other configuration click on create and wait for few minutes you will find your secondary database create with the 3 databases hosted on the primary and your Failover group created and now you can test your connection using (Primary Server, Secondary Server, Failover group read-write listener, Failover group read-only listener)
Now if you return to Azure SQL page on your Azure Portal you will find you have 6 databases and 2 Azure SQL servers (“elmasrycloudserver”, “elmasrycloudserver2”)

Check the Failover Group Configuration and Connections

Now if you connected to Azure SQL and selected anyone from the two servers and the left side selected the Failover Groups, you will be able to see that the Primary server is “elmasrycloudserver” and the secondary server is “elmasrycloudserver2”

At this time, you should have 4 connection two static and other two dynamics

Static connection #1: Current Primary Server “elmasrycloudserver.database.windows.net”
Static connection #1: Current Secondary Server “elmasrycloudserver2.database.windows.net”
Dynamic Connection #1: Failover group read-write listener: “elmasryfailover.database.windows.net” this connection will direct you to the Primary Server (Read Write) instance and this should be used on your application whatever where is the primary hosted the application will connect.
Dynamic Connection #2: Failover group read-only listener: “elmasryfailover.secondary.database.windows.net” If you have reports or any process on your application doing heavy ready you can offload it from the primary server and direct it to the secondary server whatever where it is hosted it will connect automaticity to the secondary server it same for me like Read intent in SQL Server Always On

Test Failover

Now we can do failover from the primary Server “elmasrycloudserver” to the secondary server “elmasrycloudserver2” at this time the Primary Server will be “elmasrycloudserver2” and the secondary server will be “elmasrycloudserver”

Open Azure Portal in the search write “Azure SQL”
Select the Primary Server
In the left side select Failover Groups and select the Failover Group Row created on the right
After this, on the top, you will find the option of Failover and also you can remove the databases or add a new database from this page.

Now After Failover, you can find the Primary Server is “elmasrycloudserver2” and the secondary server is “elmasrycloudserver”

Conclusion

Azure Provided us multiple Features on Azure SQL or How we can replicate our database and each service (Active Geo-Replication or failover group) Contain some features and it is supported some things and not supported somethings based on your business requirements you can be determined what is the best solution for you.