We have a geo-replicated database in SQL Azure (Premium) and are wondering if we are pointing to the South Central US database that is the master, if that goes down do we have to manually change our connection strings in our code (C# .Net / Entity Framework 6) to point to the new database in say North US? We are looking for a way to have a single connection string and then Azure do the under the covers to now point to the new database if the master ever goes down. Is that possible?
Update on method followed:
so I read this that we have to manually go into a web.config file on a production system when a region goes down and change the main database to another replicated database that is working.
I decided to just hard code east, west and south regions in the web config and wrote code to retry and fail over to other regions if the main one couldn't be reached. This means it can only read, not write until Microsoft or we manually failover the master to a Read Only Active slave. Not the best experience to me. Requires human intervention knowing it's down, then flipping it and custom code
Geo Replication is used for only Azure SQL Databases. Failover groups allow you to manage replication and failover of a group of databases on a server or all databases to another region. It provides automatic failover features. Failover groups support only one secondary server.
Up to four geo-secondaries can be created for a primary. If there is only one secondary, and it fails, the application is exposed to higher risk until a new secondary is created. If multiple secondaries exist, the application remains protected even if one of the secondaries fails.
The default replication type in active geo-replication is asynchronous. However, if the application needs to have synchronous replication, then you can do so by calling sp_wait_for_database_copy_sync immediately after committing a transaction.
The auto-failover group must be configured on the primary server, which will then connect it to the secondary server in a different Azure region. The groups can include all or some databases in these servers.
You should read this article Designing Cloud Solutions for Disaster Recovery Using Active Geo-Replication
There are a few options on how to setup Azure for availability. So, based on your requirements and configuration, you may need to change SQL Connection String. The key in this article is to look at the "SLA". For example, in Option 2: Active-active compute with decoupled failover, the SLA is:
RTO = SQL connection change + database state change + application verification test
Notice the SQL connection change
; this implies a connection string change.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With