Hello everybody! One of my favorite features in Azure SQL DBaaS is Failover Groups. I have been working with these since they were in preview and I cannot stress the value of this feature enough! In my former company, we had 22 web applications that all had connections to various databases. We had all of our databases configured for Geo-Replication already, but still if we had to failover, we had to update each connection string for the web apps along with others which became a tedious process. In came Failover Groups to the rescue! With a Failover Group, I was able to create two endpoints that stayed the same no matter which server was primary/secondary. I liked to think of these as my Availability Group Listeners as they kinda serve the same functionality: Route traffic to a node depending on if its read-only or not. Best part? It’s configured through the Azure Portal SO EASILY! You can use PowerShell as well, but for this blog post, I will walk through the creation via the Portal. I will make a separate post or attach a script at some point for the PowerShell deployment.
Before we start the configuration portion of this though, let’s take a look at how Microsoft defines what a Failover Group is. I found this definition here: “Azure SQL Database auto-failover groups (in-preview) is a SQL Database feature designed to automatically manage geo-replication relationship, connectivity, and failover at scale.” Sounds pretty interesting, right? Let’s make one!
I will do my best to provide step-by-step instructions, but please feel free to reach out to me if you have any questions. By following the steps below, they should enable you to be able to create your first Failover Group through the Azure Portal with little to no problems.
First, login to the Azure Portal and go to the instance you want to start as Primary. I will be using FlippityFloppity as my Primary server.
Once you have your Primary server selected, scroll down until you reach the “Settings” section, and select “Failover Groups”.
Once you select “Failover Groups” the right side should display something like this if you have no Failover Groups configured yet:
When you are ready to configure the group, click the ” + Add group” button at the top of the window.
Once you do this, you will get a setup dialog box similar to this one. The one below is filled out already just to save space on the blog post, but each setting is described below the picture.
There is a GREAT article here that describes these settings in a deeper way and before creating your Failover Group I would suggest reading it over to make sure you have a better technical explanation that I gave above: Failover Group Documentation.
So, once you have the information filled out, go ahead and click the Create button in the lower right hand corner. This will begin the creation of the Failover Group(DNS entries/Backing up and restoring database to secondary). Once it is finished, you should see the Failover Group if you click on Failover Groups again under your Primary Server.
Now, go ahead and click on your new Failover Group, and look at the magic inside! In the interest of keeping the image smaller, I did not include the world map, but one of the first things you will notice is a world map that shows you the geographic locations of your two servers. If you scroll that down, you will see settings similar to what is below:
So, on here, you will see what server is currently Primary, and what is currently Secondary. With my configuration, flippityfloppity is currently Primary, and floppityflippity is currently Secondary. You also see your “Read/Write listener endpoint”, and your “Read-Only listener endpoint”. These are connections that you should use for any application connection strings, but with one additional piece explained below. Alot of my clients use their secondaries for reporting purposes so after we configure the Failover Group I supply them with the Read-Only connection string to use for their reporting tool.
As you can see, the initial setup and configuration of your Failover Group is super easy with the Azure Portal. See part 2 for how to connect and use your Failover Group!
Hello John. I was not using Elastic Pools in my configuration above, but you are right, it should not make a difference. What kind of issue are you having?
In this example, are your primary and secondary servers using elastic pools? Based upon my reading of the MS documentation that should not be an issue but I seem to be running into one.