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 se...
Updated Nov 09, 2020
Version 4.0
Blog Post
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.