Azure SQL Database
An Azure relational database service.
5,618 questions
Failover for secondary read-only endpoint in SQL failover group
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(185.6, 84%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDP%3C/text%3E%3C/svg%3E)
Devanshi Patel
0
Reputation points Microsoft Employee
Usecase
My team has a SQL failover group set up where we use primary region for read-write workloads. We use secondary region to serve read only workloads. I am exploring options for ensuring data availability when secondary server/region is down.
Here's my question:
As per SQL team recommendation, the failover should be customer managed and not microsoft managed. In this case, how can we handle issues with secondary region so as not to impact read only workloads?
Ideally I would have considered adding a replica for secondary server. However right now, it seems like the SQL platform doesn't support having more than one replica.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 60%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Mahesh Kurva 5 Reputation points Microsoft Vendor2024-09-17T04:31:52.1666667+00:00 Hi @Devanshi Patel,
Welcome to the Microsoft Q&A and thank you for posting your questions here.
- Manual Failover to Primary: Update connection strings in your application to point to the primary region's read-only endpoint to redirect read-only workloads to the primary region.
- Read-Scale Out with Read-Only Replicas: Create additional read-only replicas within the primary region to handle read-only workloads.
- Geo-Replication: Implement active geo-replication for critical read-only databases to create readable secondary databases in different regions. Manually failover to another region where the geo-replicated database is available in case the secondary region fails.
- Load Balancing: Use a load balancer to distribute read-only traffic across multiple read-only replicas within the primary region to manage the load and ensure availability even if one of the replicas goes down.
- Regular Backups and Restores: Ensure regular backups of your databases and have a plan in place to restore them in a different region if needed. This can be a last-resort option to ensure data availability.
For more information, please refer the document: https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db?view=azuresql.
I hope this information helps. Please do let us know if you have any further queries. -
Devanshi Patel 0 Reputation points Microsoft Employee
2024-09-17T17:02:26.2233333+00:00 Hi.
I am looking for options where updating connection strings is not required for failover of read only replica.
Can you elaborate more on "Read-Scale Out with Read-Only Replicas"? Based on the documentation I have read, it is not possible to have more than one replica as part of failover group.
-
LucyChenMSFT-4874 4,430 Reputation points2024-09-18T01:24:21.7633333+00:00 Hi @Devanshi Patel ,
Thank you for your reaching out!
Could you please tell us what version of your SQL team is using?
-
Devanshi Patel 0 Reputation points Microsoft Employee
2024-09-18T16:58:37.4933333+00:00 I don't understand what you mean by version. We should be using whatever is provided by Azure SQL platform.
-
Devanshi Patel 0 Reputation points Microsoft Employee
2024-09-18T17:11:30.0133333+00:00 Hi. I don't understand what you mean by version. Can you specify where I can get it from?
If you mean tier, we are using Business critical.
Sign in to comment
Sign in to answer