Failover for secondary read-only endpoint in SQL failover group

Devanshi Patel 0 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.

Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-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 points

2024-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.

1 answer

LucyChenMSFT-4874 4,430 Reputation points

2024-09-19T06:40:27.15+00:00

Hi @Devanshi Patel ,

Thank you for your reaching out and welcome to Microsoft Q&A!

Are you worried about whether the data in the database will be lost if all the servers are unavailable? Please refer to this official documentation, we can get the information:

In addition, we can always add a database of the same Azure SQL Server to an existing failover group. When a new database is added to the existing failover group, it automatically creates a new database in the secondary server with the same name, edition and computes. For more information, please refer to Failover groups overview & best practices.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Please sign in to rate this answer.
Devanshi Patel 0 Reputation points Microsoft Employee

2024-09-19T16:33:08.28+00:00

I already have a secondary server added to my failover group. My concern is how do I ensure read only workloads aren't impacted if either primary or secondary region fails?

One potential option is to have read only workloads connect to failover group read-write endpoint with ApplicationIntent=ReadOnly. Would this ensure that the read calls are always routed to read only replicas in primary region (no impact to perf for read-write workloads)? Are there any other caveats for this approach?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Failover for secondary read-only endpoint in SQL failover group

1 answer

Your answer