What connection string to use for Azure SQL DB using Active Geo Replication?

Question

I have a database in SQL Azure that is actively replicating in 2 different regions. They are read-only (tested from SQL Management Studio, connecting to their respective servers), but what connection string do I need for my application to use automatic failover and / or use readonly instance for reading and master instance for writing?

Answer 1

Now you can create a failover group and set up automatic failover (currently in preview mode), and then you will have a connection string to listen to read / write

Go to Azure SQL Server (not a specific database) → Failover Groups → Create and populate the necessary information - servers, databases and policies

After creating the group, you will have a listener connection string, which you can use in both databases in the details of the failover group configuration

For more information, please check https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview#auto-failover-group-capabilities

Answer 2

Your application should connect to readable secondary resources in the same way as to SSMS: you must use the server connection string, which is stored in the replica.

Fail-safe functions can be launched using the T-SQL, PowerShell commands and through the management portal. Check out these two documentation articles:

Active geo-replication for Azure SQL database Stop continuous copy relationship

Answer 3

We have an API hosted in an application service in two regions - Singapore and Hong Kong. In addition, in Singapore we have a primary SQL database with geographic replication in HK.

We intend to keep two ConnectionString lines in our application service configuration — one for reading and one for writing. Our GetMethods API will use the Read Connection String, and the Update / Add / Delete methods will use the Write Connection String.

The Singapore Application Service will have both connection strings pointing to the main database in Singapore, while the HK Application Service ConnectionString entry will point to the Singapore database, and reading ConnectionString will point to the additional HK database.

Thus, users of the HK App Service will have faster reads (and the database in HK will also be used instead of sitting and waiting for a disaster to occur).

If we use the generated Failover-Group ConnectionStrings, then we do not need to change the configuration on the day of the accident.

Do you see any problems with the above installation? I can think of two a. If geo-replication between Singapore and Hong Kong is slow, it will be a bad user experience. b. It’s a little difficult to write an API to use the correct connection string in Get / Set methods.

My question is that such use is probably not documented anywhere, so we have to do it or not.

Thanks Ashish