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?
What connection string to use for Azure SQL DB using Active Geo Replication?
3.8k Views Asked by IvanL At
2
There are 2 best solutions below
3

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
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://learn.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview#auto-failover-group-capabilities