Azure SQL PaaS Failover Scenario and Cost

Question

Azure SQL PaaS Failover Scenario and Cost

ElenaMarlowe-3376 65

Hi,

I have a few questions regarding Azure SQL PaaS failover, pricing, and integration with private endpoint:

If I configure a Failover Group that contains 10 SQL databases, will the entire failover group automatically failover if only one of the databases in the group becomes unavailable? Or does automatic failover only happen if the entire SQL server (or all databases) are unavailable?
I understand that Failover Group supports auto-failover, but it seems that this only applies when the failover is Microsoft-managed, which might take up to an hour or more depending on the outage. If I’m using a custom-managed failover, is there a way to enable automatic failover behavior in that context? Would I need to set up any custom metrics, alerts, or automation (e.g., with Azure Monitor and Logic Apps) to support this kind of proactive failover?
If my SQL databases are all in an elastic pool. After I provision a secondary elastic pool in the secondary region, and enable geo-replication for each database, will the cost of the geo-secondary databases be based solely on the secondary elastic pool's pricing tier, with no additional per-database cost?
Is there any extra charge specific to the Failover Group itself (other than the cost of the geo-secondary databases)?
If I have SQL servers configured with private endpoints, and I’ve already created a secondary SQL server in another region and linked both SQL servers to the same private DNS zone, are there any additional steps required to prepare for failover? After failover, do I need to manually update any connection strings or DNS records, since I know failover group will automatically update connection string, but how about SQL server itself with private endpoint?
Are there any best practices for failover testing in such a setup (e.g., temporarily failing over and validating connectivity via private endpoint)

Thank you!

ElenaMarlowe-3376 65 Reputation points

2025-06-08T09:06:29.6633333+00:00
Hi @Shraddha Pore ,

Can I trigger failover for three failover group in the same SQL server simultaneously, or do I need to complete one before moving on to the next?

As I understand, Failover Groups support auto-failover only in Microsoft-managed scenarios, which may involve up to 1 hour of delay. If I want faster control during a regional outage, can I switch to a custom-managed model on demand? Or no changes can be made during regional outage?

If one database in a Failover Group experiences an outage, can I still restore it using geo-restore? Or do I need to remove it from the failover group first?

Would you recommend putting all databases into a single Failover Group for simplicity, or are there risks such as performance impact or slower failover due to a high number of databases? (e.g., 40 or even more) Since in the doc, it did mention about the number of databases in failover group need to be considered: https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db?view=azuresql#number-of-databases-in-failover-group

Joining databases to a Failover Group doesn’t incur extra charges, since I’m already paying for the secondary elastic pool?

For Private Endpoint, when you say “Private DNS zone is correctly set up,” could you clarify what exact setup or checks are needed?

Thank you!
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-09T08:57:31.2466667+00:00
Hi ElenaMarlowe-3376, Thank you so much for your question. Below is the research you can consider for your queries.

Q1. You can trigger failover for multiple failover groups on the same SQL server simultaneously. Azure does not restrict concurrent failover group operations, though each failover group is an independent operation and Backend operations may take longer if all databases are large or under load.

The maximum number of failover groups on the server is the same as maximum number of databases on a server. As per documentation the maximum number of databases per logical server is 5000.

Q2. No, during a regional Azure-wide outage, you cannot make any changes to the affected region (including triggering manual failover). Because Microsoft owns failover orchestration during regional outages. If a region is fully offline, any operation that requires connectivity to the primary region/server will fail. What you can do before a region becomes completely unreachable:

Monitor for increased latency or partial outages using Azure Monitor

Trigger failover proactively if signs of failure emerge (custom-managed approach) Best you can do:

Combine auto-failover with your own alerts and early warnings, so you can initiate a controlled manual failover before full outage hits.

The only time you can trigger failover yourself is before the region goes completely unreachable.

Q3. You can geo-restore an individual database without removing it from the failover group. However, the restored database will be a new standalone copy and will not automatically reintegrate into the failover group. Manual intervention is required to replace the failed database and rejoin it to the failover group. That said, there are a couple of things to keep in mind. When you do a geo-restore, Azure creates a new, separate copy of the database using the latest geo-redundant backup. This new copy isn’t automatically reconnected to the original failover group — it just lives on its own.

The original database that had the issue will still be part of the failover group unless you remove it manually. If you want the restored version to replace it in the failover group, you’ll need to:

Remove the old, broken database from the group.

Manually add the restored one back in.

Q4. If you’re managing a handful of databases say, under 20 or so putting them all into a single Failover Group can actually make your life easier. It’s straightforward to set up, gives you one DNS listener to manage, and simplifies failover operations.

But once you start dealing with a larger number like 40 databases or more it’s smart to think twice before lumping everything into one group. The reason is during a failover; Azure has to handle the replication state and switch over every database in the group. That coordination takes time. So, the more databases you include, the longer the failover might take. And if any of those databases are under load or large in size, it can slow things down even more.

Another thing to keep in mind is flexibility. Let’s say not all of your databases are equally critical maybe your customer-facing ones have strict uptime requirements, but others (like reporting or batch jobs) can afford a bit more downtime. By splitting them into multiple failover groups, you can control when and how each group fails over, rather than being locked into an “all or nothing” scenario.

Microsoft doesn’t set a hard limit on the number of databases in a failover group, but they do call out that performance and failover times can be impacted as you scale up. Their recommendation is to break things up into smaller, manageable groups if you're dealing with a large number of databases.

Q5. Failover Group is a logical grouping and automation layer for geo-replicated databases across two Azure SQL servers in different regions. It provides automatic failover and a global listener DNS name, but it doesn't cost extra to use. You are billed for the pool as a whole. You can add multiple databases to a pool without incurring additional charges per database, as long as they are part of the pool. joining databases to a Failover Group (FOG) does not incur any extra charges by itself, as long as you're already paying for the geo-secondary resources (e.g. an elastic pool or individual geo-replicated databases).

Q6. For a private endpoint to work correctly in failover scenarios, you need to set up DNS properly. The endpoint’s traffic direction operates via DNS. When a failover transpires, Azure updates the associated DNS record to reflect the current primary database. This mechanism hinges heavily on the DNS record's Time-to-Live (TTL). A lower TTL ensures rapid cache expiration, facilitating quicker traffic redirection during failovers. Ensure below details are correctly linked.

Private DNS Zone: Create a private DNS zone for privatelink.database.windows.net.

DNS Records: Ensure that DNS records for both primary and secondary SQL servers are present in the private DNS zone.

VNet Linking: Link your application VNet to the private DNS zone to ensure proper DNS resolution.

Failover Group Alias: Manually add an A record for the failover group alias (e.g. <failover-group>.privatelink.database.windows.net) to point to the private IP of the secondary server after failover.

You can refer Documentation Also, Documentation

This setup ensures that your application can seamlessly connect to the new primary server via the private endpoint post failover.

Please do not forget to click "UPVOTE the comment” and Yes wherever the information provided helps you, this can be beneficial to other community members. If you have any other questions or still running into more issues, let me know in the comments and I would be happy to help you.
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-10T08:20:40.33+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and happy to help you.
Kanie-0317 100 Reputation points

2025-06-10T14:30:37.03+00:00

Hi @Shraddha Pore ,

Can I have only one elastic pool but several failover groups, the number of elastic pool don't need to align with the number of failover groups, right?

And according to your Q6 reply:

Failover Group Alias: Manually add an A record for the failover group alias (e.g. <failover-group>.privatelink.database.windows.net) to point to the private IP of the secondary server after failover.

If I already create a secondary private endpoint (SQL server) and the private IP already be added in the same private DNS zone, do I still need to manaully add an A record for the failover group alias after failover?

Thank you.
ElenaMarlowe-3376 65 Reputation points

2025-06-10T15:01:01.6933333+00:00

Hi @Shraddha Pore ,

Thanks for your reply, and I'd like to know once I setup failover group, which connection string should I use in my application? Would public access and private access have any differences?
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-10T19:21:08.95+00:00
Hi ElenaMarlowe-3376, Thank you so much for your response.

When configuring your application to connect to an Azure SQL Failover Group, you should use the Failover Group listener in your connection string. This listener provides a stable endpoint that always points to the current primary server, facilitating seamless failover without requiring changes to your application.Use This in Your Application Connection String

<failover-group-name>.database.windows.net

For example, if your Failover Group is named myserver-dev, your connection string would look like:

Server=tcp:myserver-dev.database.windows.net,1433; Initial Catalog=myDatabase; User ID=myUser; Password=myPassword; Encrypt=True; Connection Timeout=30;

Always points to the current primary SQL server. This approach ensures that your application connects to the active primary server, regardless of failovers. Do not use the SQL server name directly (myserver.database.windows.net) it won't redirect during failover.

Public and Private Access: Key Differences

Public Access

DNS Resolution: The Failover Group listener resolves via Azure's public DNS.

Endpoint: Connects over the internet to Azure SQL.

Security: Relies on firewall rules or service endpoints for access control.

Use Case: Suitable for development or non-production environments.

Private Access (Recommended for Production)

DNS Resolution: The Failover Group listener resolves via a private DNS zone (privatelink.database.windows.net).

Endpoint: Connects over a private IP within your Virtual Network (VNet).

Security: Offers enhanced security by keeping traffic within the VNet.

Failover Handling: Azure automatically updates the DNS record during failover.

Use Case: Ideal for production environments requiring secure and reliable connectivity.

For production: Always use Private Endpoints + private DNS for secure and resilient access.

You can refer DocumentationAlso Documentation

Let me know if any of this won't help! Happy to help you.

Please do not forget to click "upvote the comment” and Yes wherever the information provided helps you, this can be beneficial to other community members.

If you have any other questions or still running into more issues, let me know in the "comments" and I would be happy to help you.
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-10T19:51:57.94+00:00

Hi Kanie-0317, Thank you so much for your query and comment.

Q1- Elastic pools and failover groups are separate Azure concepts and don’t need to align one-to-one. You can have a single elastic pool that supports multiple failover groups. They serve different purposes:

Elastic Pools are designed for scaling and cost optimization. They let multiple databases share compute and storage resources efficiently.

Failover Groups, on the other hand, are for high availability and disaster recovery. They replicate databases across regions and handle automatic failover when needed.

So yes, it’s perfectly fine (and common) to have one elastic pool supporting several databases and then group those databases into different failover groups as needed for your HA strategy.

Q2 - If you're using Private Endpoints for both the primary and secondary SQL servers, and those are integrated with a Private DNS Zone (e.g., privatelink.database.windows.net), then Azure will automatically create A records for the server-level endpoints. So, for example:

primary-server.privatelink.database.windows.net → resolves to the private IP of the primary

secondary-server.privatelink.database.windows.net → resolves to the private IP of the secondary

However, the failover group listener endpoint is not automatically managed in the Private DNS Zone. That means after a failover, this alias doesn’t automatically update to point to the new primary’s private IP. You need to manually update it or automate the process. If you don’t, your apps might try to connect to the old primary (which is now secondary) and fail.

Please do not forget to click "upvote the comment” and Yes wherever the information provided helps you, this can be beneficial to other community members.
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-12T09:23:45.8+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details.

happy to help you!
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-13T16:07:40.2866667+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details.

happy to help you!

1 answer

Your answer

ElenaMarlowe-3376 65 Reputation points

2025-06-08T09:06:29.6633333+00:00

Hi @Shraddha Pore ,

Can I trigger failover for three failover group in the same SQL server simultaneously, or do I need to complete one before moving on to the next?

As I understand, Failover Groups support auto-failover only in Microsoft-managed scenarios, which may involve up to 1 hour of delay. If I want faster control during a regional outage, can I switch to a custom-managed model on demand? Or no changes can be made during regional outage?

If one database in a Failover Group experiences an outage, can I still restore it using geo-restore? Or do I need to remove it from the failover group first?

Would you recommend putting all databases into a single Failover Group for simplicity, or are there risks such as performance impact or slower failover due to a high number of databases? (e.g., 40 or even more) Since in the doc, it did mention about the number of databases in failover group need to be considered: https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db?view=azuresql#number-of-databases-in-failover-group

Joining databases to a Failover Group doesn’t incur extra charges, since I’m already paying for the secondary elastic pool?

For Private Endpoint, when you say “Private DNS zone is correctly set up,” could you clarify what exact setup or checks are needed?

Thank you!
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-10T08:20:40.33+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and happy to help you.
Kanie-0317 100 Reputation points

2025-06-10T14:30:37.03+00:00

Hi @Shraddha Pore ,

Can I have only one elastic pool but several failover groups, the number of elastic pool don't need to align with the number of failover groups, right?

And according to your Q6 reply:

Failover Group Alias: Manually add an A record for the failover group alias (e.g. <failover-group>.privatelink.database.windows.net) to point to the private IP of the secondary server after failover.

If I already create a secondary private endpoint (SQL server) and the private IP already be added in the same private DNS zone, do I still need to manaully add an A record for the failover group alias after failover?

Thank you.
ElenaMarlowe-3376 65 Reputation points

2025-06-10T15:01:01.6933333+00:00

Hi @Shraddha Pore ,

Thanks for your reply, and I'd like to know once I setup failover group, which connection string should I use in my application? Would public access and private access have any differences?
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-10T19:51:57.94+00:00

Hi Kanie-0317, Thank you so much for your query and comment.

Q1- Elastic pools and failover groups are separate Azure concepts and don’t need to align one-to-one. You can have a single elastic pool that supports multiple failover groups. They serve different purposes:

Elastic Pools are designed for scaling and cost optimization. They let multiple databases share compute and storage resources efficiently.

Failover Groups, on the other hand, are for high availability and disaster recovery. They replicate databases across regions and handle automatic failover when needed.

So yes, it’s perfectly fine (and common) to have one elastic pool supporting several databases and then group those databases into different failover groups as needed for your HA strategy.

Q2 - If you're using Private Endpoints for both the primary and secondary SQL servers, and those are integrated with a Private DNS Zone (e.g., privatelink.database.windows.net), then Azure will automatically create A records for the server-level endpoints. So, for example:

primary-server.privatelink.database.windows.net → resolves to the private IP of the primary

secondary-server.privatelink.database.windows.net → resolves to the private IP of the secondary

However, the failover group listener endpoint is not automatically managed in the Private DNS Zone. That means after a failover, this alias doesn’t automatically update to point to the new primary’s private IP. You need to manually update it or automate the process. If you don’t, your apps might try to connect to the old primary (which is now secondary) and fail.

Please do not forget to click "upvote the comment” and Yes wherever the information provided helps you, this can be beneficial to other community members.
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-12T09:23:45.8+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details.

happy to help you!
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-13T16:07:40.2866667+00:00

Hi ElenaMarlowe-3376, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details.

happy to help you!

Answer 1

Hi ElenaMarlowe-3376, Thank you so much for your question. Below is the research you can consider for your queries.

Q1. When you set up an Azure SQL Failover Group, automatic failover applies to the entire group, not to individual databases. That means if just one database in the group goes down, it won’t trigger a failover. The system is designed to fail over only when the whole SQL server is unavailable. This approach helps ensure all databases in the group stay consistent and available together. If you need more granular control for instance, automatic failover of a single database you’d want to look at configuring auto-failover groups on a per-database basis. This allows each database to switch over independently, maintaining high availability without waiting on the whole server.

You can refer documentation. Also, Documentation

Q2. Custom-Managed Failover and Enabling Automatic Behaviour:

If you're handling failover yourself (a custom-managed approach), Azure doesn’t offer built-in automatic failover capabilities for individual databases. But you can still build an automated solution using Azure’s monitoring and automation tools. Start by setting up Azure Monitor to keep an eye on your databases. Then, configure alerts to notify you of issues like if a database goes offline. From there, tools like Azure Logic Apps or Azure Automation can be used to trigger the failover process automatically. It takes a bit more setup but gives you full control over how and when failover happens.

Monitoring - Azure Monitor / Log Analytics
Alerts - on metrics or logs like connection errors, CPU, DTU
Automation - Logic Apps, Functions, or Automation Runbooks
Failover Trigger - CLI/API

You can refer Documentation

Q3. Costs for Geo-Secondary Databases in Elastic Pools:

If your databases are in an elastic pool and you enable geo-replication, pricing is tied to the service tier of the secondary elastic pool—not the individual databases. So, you won’t be charged separately for each geo-secondary database as long as they’re part of the pool. Secondary active geo-replication databases are priced at 100 percent of primary database prices. Secondary active geo-replication databases are priced at 100 percent of primary database prices. Also note that The cost of geo-replication traffic between the primary and the online secondary is included in the cost of the online secondary. So In essence, for Replicated Databases, you can calculate these costs as the same as the primary database in Azure pricing calculator

Q4. Extra Charges for Using Failover Groups:

There’s no additional fee just for setting up a Failover Group itself. You’re mainly paying for the underlying resources your databases and the elastic pools they live in. That said, using features like active geo-replication or auto-failover across regions does come with added costs, depending on the number of databases you replicate and where they’re located. It’s worth reviewing Azure's pricing documentation if you're planning for large-scale DR setups.

Q5. Using Private Endpoints with SQL and Preparing for Failover:

If you’re using Private Endpoints with your SQL servers and you’ve already set up a secondary SQL server in a different region, and both are connected to the same private DNS zone, you’re mostly covered. Here are a few things to double-check:

DNS Configuration: Make sure your private DNS zone is correctly set up so that your app always connects to the correct private IP regardless of region.
Connection Strings: When using failover groups, the failover process will automatically update the listener endpoint in your connection string to point to the new primary.
Manual Steps: If you're not using failover groups or if you're accessing the server by its actual name (not via the listener), you may need to manually adjust DNS or connection strings post-failover.

Running a few tests can help confirm that your setup works as expected when switching regions.

You can refer Documentation

Q6. Failover Testing Best Practices: Testing is critical. Don’t wait for a real disaster to find out something’s not configured properly. Here are some practical tips:

Test Regularly: Schedule routine failover tests—both planned and unplanned—to catch issues early.
Monitor Everything: Use Azure Monitor and Log Analytics to keep tabs on performance and availability.
Automate Where Possible: The less you have to do manually during a failover, the better. Automate what you can.
Keep Documentation Updated: Make sure your team knows exactly what to do in a failover scenario. Keep your runbooks current.

You can refer Documentation

I hope this clarifies your all doubts!

Please do not forget to click "Accept the Answer” and Yes wherever the information provided helps you, this can be beneficial to other community members. If you have any other questions or still running into more issues, let me know in the comments and I would be happy to help you.

Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-11T08:05:51.74+00:00

Hi ElenaMarlowe-3376, Please do not forget to click "Accept the answer” and Yes wherever the information provided helps you, this can be beneficial to other community members.

If you have any other questions or still running into more issues, let me know in the "comments" and I would be happy to help you.
Shraddha Pore 445 Reputation points Microsoft External Staff Moderator

2025-06-16T11:00:08.7+00:00

Hi ElenaMarlowe-3376, Please do not forget to click "Accept the answer” and Yes wherever the information provided helps you, this can be beneficial to other community members.

If you have any other questions or still running into more issues, let me know in the "comments" and I would be happy to help you.

Share via

Azure SQL PaaS Failover Scenario and Cost

1 answer

Your answer