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?

Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
IvanL
  • 2,475
  • 1
  • 26
  • 39

2 Answers2

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

DivineOps
  • 1,656
  • 16
  • 17
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

Jan Engelsberg
  • 1,067
  • 6
  • 8
  • 3
    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? – IvanL Jun 17 '15 at 07:42
  • 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." – Greg Grater Jan 24 '17 at 18:35
  • Updated the answer to reflect the new capabilities. After 3 years we now have a built-in solution. – IvanL Jul 13 '18 at 05:34