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

Question

I have a database in SQL Azure which is being actively replicated to 2 different regions. They are both read-only accessible (tested from SQL management studio by connecting to their respective servers) but what connection string do I need to use for my application to use automatic failover/fallback and/or to use the readonly instance for read and the master instance for write?

Answer 1

You can now create a failover group and set up auto-failover (currently in preview) and then you will have a read/write listener connection string

Go to the Azure SQL Server (not a specific database) -> Failover Groups -> Create and fill in the required info - servers, DBs and policy

After the group is created you will have a listener connection string you can use across both DBs in the failover group configuration details

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

Answer 2

Your application has to connect in the same way to the readable secondaries as you did from SSMS: You have to use the connection string of the server that holds to replica.

Failovers can be triggered use T-SQL commands, PowerShell and through the management portal. Check these two documentation articles:

Active Geo-Replication for Azure SQL Database Terminate a Continuous Copy Relationship