Using Failover Groups with Private Link for Azure SQL Database

[Rohit Nayak]

Reviewers:- Abdul Sathar Sait

Azure SQL Database offers the ability to manage geo replication and failover of a group of databases by adding them into failover group.  A failover group spans two servers – a primary server where the databases are accessed by the end user or application & a secondary server in a different region where a copy of each database is kept in sync using active geo-replication.

With Private Link, we are offering the ability to associate a logical server to a specific private IP address (called a private endpoint) within the Vnet(& subnet). This blog outlines how Failover Groups can be implemented using private endpoint for SQL Database instead of the public endpoint thus ensuring that customers can get security benefits that it offers as outlined here.  

At a very high level you need to do the following steps to accomplish this

1. Start by picking a list of DR paired regions where you will host the primary and secondary servers

2. Create Vnets/subnets to host private endpoints for primary and secondary servers and make sure that they do not have overlapping IP address spaces. I.e. if the primary Vnet is 10.0.0.0/16 and the secondary is 10.0.0.1/16 these are overlapping. To understand more about how Vnet ranges are specified in Azure refer to this blog

3. Create private endpoints for primary and secondary servers so that they can communicate with each other via a shared Azure Private DNS Zone.

a) Create a private endpoint(PE) + Azure Private DNS Zone for the primary from the Portal per https://docs.microsoft.com/azure/private-link/create-private-endpoint-portal. At the end of this process you should see a single entry in the DNS zone that refers to the primary SQL Database (as shown in the screenshot below)

b) Create a PE for the secondary via the Portal but choose to reuse the same Private DNS Zone that was created for the primary.  At the end of this process you will have two entries in the DNS Zone – one for primary and another for secondary SQL Database (as shown in the screenshot below)

4. After both Private endpoints are setup (as shown above), you can create a new Failover Group just like before, using the Azure portal as described here or using PowerShell script as described here

In summary, this quick blog post shows you how to get started with using failover groups and Private link for Azure SQL Database. We hope you find this useful and look forward to your feedback.

[KMS2222]

@Rohit Nayak Thank you for this blog. Can you please help shed some light on how the connection string will look like in this scenario where we have private endpoints and Azure Failover Group:-

We have an ASP.Net Web app hosted as Azure App serivce connecting to Azure SQL Server. We have 2 Azure SQL Server one in North and other in South region (For Failover). We have created Private endpoints for both the sql servers:-

NorthRegion-devxxxs-sql-server.database.windows.net
SouthRegion-devxxxs-sql-server.database.windows.net

Following is the Read/Write listener endpoint from Failover group:-

XXXXX-devxxxxs-failovergroup.database.windows.net

North is Primary and South is secondary and they are added to Failover group. AsP.Net Web app connection string uses private endpoint to North Region SQL Server as follows:-

  <add name="xxxxxxxx" connectionString="Data Source=tcp:NorthRegion-devxxxs-sql-server.database.windows.net,1433;Initial Catalog=dtasbasename;Persist Security Info=False;User ID=Admin;Password=YYYYYY;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />

We did a Failover test and SQL Server were switched as South primary and North secondary but our suspicion was its still connecting to North region database due to pvt endpoint so for testing we delted north region database and we got runtime exception.

Then I changed my connection string to use the READ/WRITE Listner and this is how my connection string looked (same user id and password) and still we got runtime exception : Cannot connect to the database. following was the modified connection string using the listener endpoint from failover group:-

<add name="xxxxxxxx" connectionString="Data Source=tcp:XXXXX-devxxxxs-failovergroup.database.windows.net,1433;Initial Catalog=dtasbasename;Persist Security Info=False;User ID=Admin;Password=YYYYYY;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />

My question here is what should the connection string look like in this scenario where I have 2 SQL Servers in 2 different regions with Private EndPoints and in the Failover group. I am not sure about what should be the Database Server name that goes in web.config connection string so that it can automatically connect to the Secondary Server if Primary Server goes down.

[Rohit Nayak]

@KMS2222 The connection string remains unchanged. All we are doing is ensure that failover groups rely on the Private DNS Zone to resolve to primary or secondary. In your case, is the App Service deployed inside the same ( or peered ) VNET using ASE? Secondly all the VNETs involved should be covered in a single DNS Zone so that the DNS resolution to private endpoints works as expected.

[KMS2222]

@Rohit Nayak Thank you, so in the connection string should we be using the Failovergroup Listener or the Private End point of the SQL?

In my case SQL1, App Service both are in the same region VNet (NC) and the SQ2 failover is in the SC (Different Vnet/Subnet) but all of them in the same Private DNS Zone. Right now even after keeping the NC as primary and connection string using the Failover Listener its not working so I am confused as to what is missing here. NSlookup resolves the failovergroup link to the primary SQL from the App Service Kudu but when I try to open the app it complains with cant connect to the database ( I am using the failover Read/Writer Listener endpoint in the connection string)

[Rohit Nayak]

@KMS2222  Adding private endpoint in the mix does nothing to change the connection string. You setup connection string exactly as mentioned in our failover group docs - which if I remember correctly tell you to use the failover group listener ( but please double check ). I'd suggest opening a support case to narrow this down further and get to the root cause.

[Gopinath_Ravichandran]

@Rohit Nayak @KMS2222 Hello is there any documentation or tutorials you can share. We are planning to do the azure fail over group for sql server database. Initially we have been using the failover group with the public addrees whichh has been shared on the read-write listener endpoint. We have been asked to use the private endpoints for the sql server. In this case we are not able to find any documentation or tutorials how to implement the failover group. i can see only this forum. Please help us in sharing any useful docs.

[Nelson_Paily_Varghese]

Does this setup work for SQL Managed Instance offering as well?

[SaswatMohanty]

Does this solution works for Azure SQL deployed in two different subscription and both SQL DB has private endpoint . 

SQL A (Private Endpoint) --> In Azure Subscription 1

SQL B ( Private Endpoint) --> In Azure Subscription 2

requirement : SQL A and SQL B should work with Geo-replication and separated between two subscriptions