10

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?

Improve this question

2 Answers 2

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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

Improve this answer

3 Comments

So monitoring and failover falls down to an implementation you do yourself? There's no way of configuration or an application block to resolve this?
Has anyone provided some boilerplate code to manage failover and role changes? With AlwaysOn, it's managed through the ApplicationIntent setting in the connection string. I'm shocked that Azure SQL has no similar construct (and no alternative solution other than 'roll your own code'). Per the links provided above: [Upon failover] "Update Connection Strings [Manually]: Because your recovered database will reside in a different server, you need to update your application’s connection string to point to that server."
Updated the answer to reflect the new capabilities. After 3 years we now have a built-in solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.