DR for DWH solution

Question

DR for DWH solution

Xhevahir Mehalla 45

Hello,

I need to pick up some input what type of DR I need to use for a Data Warehouse build on Azure.

Here's the setup:

Data Source -Oracle OCI (cloud)
ETL tool - Synapse Pipelines
Data Lake gen 2 used to store data as is taken from Oracle db (OCi)
Azure SQL db is the DWH where data is stored/structured.
two web apps (angular/devexpress) which interact with Synapse and Azure SQL.
Two VMs - one used as Self-hosted IR for Synapse and the other used for PBI to connect to Oracle OCI using directquery for some reports.
Use PBI to produce reports
Created a Fabric capacity per env (dev, uat and prod).

Network:

One VPN to access Oracle aci
One VPN to access On-premise network.
Use Vnet and Subnets where we seperate data, apps.
Use Private Endpoint to access resources within the VNET.
Use Private DNS and DNS zones
Use NAT

We have three envs: DEV, UAT and PRod (uat and dev one subscription) and Prod a seperate subscription.

I want to know what DR do I need to build and how much I want to spend on this.

This is a reporting app but nonetheless they still produce reports which go to regulator.

Max they can wait is 24 hours but not more.

I need the DR up to and including a region fauilure.

Thanks

Xhev

Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-01-15T11:31:21.0266667+00:00

Hi Xhevahir Mehalla,

I kindly request you to please share the details requested in the private message for further investigation.

1 answer

Your answer

Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-01-15T11:31:21.0266667+00:00

Hi Xhevahir Mehalla,

I kindly request you to please share the details requested in the private message for further investigation.

Answer 1

Manoj Kumar Boyini 16,150 Microsoft External Staff Moderator

Hi Xhevahir Mehalla,

It sounds like you’re trying to figure out the best disaster recovery (DR) strategy for your Data Warehouse setup on Azure, especially considering you need to ensure continuity even in the event of a regional failure. Here’s a detailed approach you might consider:

1. Understand Your DR Requirements

Since you mentioned a maximum wait time of 24 hours for recovery, you'll need to plan for a DR solution that can meet this Recovery Time Objective (RTO).
Your Recovery Point Objective (RPO) appears to align with the geo-backup scheduling of Azure SQL Database, which performs daily backups. If a shorter RPO is necessary (e.g., 1 hour), you might consider user-defined restore points.

2. DR Options for Azure SQL Database:

Geo-Replication: Enable active geo-replication for your Azure SQL Database to create readable secondary databases in different regions. This allows for automatic failover and enhances availability during disasters.
Failover Groups: Consider using failover groups for automatic cross-region failover, which simplifies the management of the failover process.
Backup and Restore: Regularly configure geo-redundant backup storage to enable geo-restore capabilities.

3. Data Ingestion and Processing with Synapse:

Use Synapse Pipelines for ETL processes, ensuring your data can be replicated to another region if needed.
Set up mirroring for operational data to maintain near real-time data replication, minimizing data loss during a disaster.

4. Support for Reporting and BI Tools:

Power BI automatically benefits from Azure's reliability features, but it's crucial to ensure your data sources are resilient and properly configured for failover, as mentioned.

5. Network Configuration:

Ensure that your VPN configurations are resilient, and consider using Azure Virtual Network (VNet) Peering to maintain connectivity across regions. Leverage Private Endpoints to ensure secure communication that is less susceptible to public internet failures.

6. Cost Considerations:

Design your DR setup while keeping costs in mind. Active geo-replication and failover groups may come with additional costs but improve your availability significantly.
Evaluate the different service tiers for Azure SQL Database as higher tiers might offer better performance and disaster recovery options.

7. Documentation and Further Resources:

For detailed guidelines on these DR strategies, you may refer to the following resources:
- DR for SQL Database
  - Backup and restore dedicated SQL pools
    - DR for Azure Data Platform - Architecture

Follow-Up Questions:

What exact recovery time objective (RTO) and recovery point objective (RPO) are you aiming for?
Are there specific budget constraints you need to keep in mind for your DR strategy?
Have you considered the implications of scaling your Azure SQL Database or Synapse resources based on your expected data growth?
Do you have specific data retention policies that might affect your backup strategies?

Let me know if this helps or if you need more specific guidance tailored to particular aspects of your setup.

Xhevahir Mehalla 45 Reputation points

2026-01-15T11:33:23.6233333+00:00
In general I know this things.

I was looking for more deatils how to do the DR if a regional failures.

what DR do I need I need to do with Apps?

what DR I need do with Azure SQL db?

what DR I need to do with VMs?

Synapse DR?

Network side of things? if VPN goes down in one region how can I point all DNS/apps to the new region.

All of these things I wanted to know how to create DR env.

How much is going to cost me?

how easy will be to fallover and fallback?

All of the above items I wanted to discuss.

Thanks

Xhev
Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-01-15T13:25:53.9566667+00:00

Hi Xhevahir Mehalla,

Thanks for the clarification. Based on your architecture and the requirement to survive a full Azure regional failure with a maximum acceptable downtime of 24 hours, the most appropriate and realistic approach is a Warm Disaster Recovery (DR) design. This gives you regional resilience without the cost and complexity of a full active-active setup, and it is acceptable for regulatory reporting workloads.

Below I’ll explain what DR is required for each part of your platform, how it works during a region outage, how easy failover and fallback are, and what the cost impact looks like.

Applications (Angular / DevExpress)

Your web applications should be deployed in two Azure regions. Both deployments sit behind Azure Front Door, which continuously health-checks the primary region. If the primary region becomes unavailable, Front Door automatically routes users to the DR region. No DNS changes are required during an incident, and users are redirected within minutes.

When the primary region recovers, Front Door automatically routes traffic back. The cost here is simply a second App Service deployment and the Front Door service itself, which is relatively low. This gives you automatic application-level DR with very little operational effort.

Azure SQL Database (Data Warehouse – Critical)

For the data warehouse, backup-only DR is not sufficient for a regulated reporting platform that must tolerate regional failure. The correct and supported approach is to configure Azure SQL Database Failover Groups.

You deploy a secondary database in the paired Azure region and place both databases in a failover group. All applications, Synapse pipelines, and reports connect using the failover group endpoint, not a region-specific server name.

If the primary region fails, Azure automatically (or with one manual approval) fails over to the secondary database. Data replication happens continuously, so data loss is measured in minutes, and recovery time is typically well under an hour. When the primary region is stable again, you perform a controlled manual failback.

This adds the cost of a secondary database (roughly 70–80% of the primary), but it is the most important DR investment in your entire platform.

Virtual Machines (Self-Hosted IR and Power BI VM)

For the two VMs, the recommended approach is to have equivalent VMs available in the DR region. These can either be replicated using Azure Site Recovery or simply pre-created and kept powered off to reduce cost.

During a regional failure, the DR VMs are started, the secondary Self-Hosted Integration Runtime becomes active, and Power BI reconnects to the SQL failover group endpoint. Failback is just as straightforward: stop the DR VMs and restart the primary ones. This process is manual but predictable and well within a 24-hour RTO.

Synapse Pipelines

Synapse pipelines do not automatically fail over between regions. The supported and recommended approach is to deploy a second Synapse workspace in the DR region and store all pipelines in Git or infrastructure-as-code templates.

In a region outage, you redeploy the pipelines to the DR workspace and update linked services to point to the SQL failover group endpoint and the DR Self-Hosted IR. This process is manual but easily automated and usually takes a few hours at most. The cost of maintaining a standby Synapse workspace is very low.

Data Lake (ADLS Gen2)

For the data lake, enable geo-redundant storage (GZRS or RA-GZRS). With this configuration, your data is automatically available in the paired region without any manual intervention during a disaster. This is one of the simplest and most cost-effective DR protections you can enable and should be considered mandatory.

Networking, VPNs, and DNS

On the network side, you need to plan for recreating connectivity, not moving it dynamically. VPN gateways to Oracle OCI and on-premises should have a secondary gateway in the DR region, with documented reconnection steps. Private Endpoints must be recreated in the DR region, using the same Private DNS zones.

Traffic redirection works as follows: application traffic is handled automatically by Azure Front Door, database connections switch automatically through the SQL Failover Group endpoint, and Synapse/VM-based components are switched manually. No emergency DNS edits are required during the outage if this model is followed.

Power BI and Fabric Capacity

Fabric capacity is currently regional, and there is no automatic cross-region failover. The practical DR approach is to maintain a standby Fabric capacity in the DR region. During a disaster, the capacity is started, datasets are rebound to the SQL failover group endpoint, and refreshes are triggered. This is manual, but fully acceptable within a 24-hour recovery window and aligns with current Microsoft guidance.

Cost and Operational Effort

This Warm DR model costs significantly less than active-active while providing strong regional resilience. Storage geo-replication and Front Door are low cost. SQL Failover Groups are the main DR cost driver, but also the most valuable. VM DR and Fabric standby add moderate cost only when activated.

Failover for apps and SQL is automatic or one-click. Synapse, VMs, and Power BI require manual steps, but these are well understood and easily documented in a runbook. Fallback is controlled and predictable.

Final Recommendation

Given your architecture, regulatory reporting requirements, and a maximum tolerated outage of 24 hours, a Warm Disaster Recovery architecture is the best and most cost-effective solution. It provides full regional protection, aligns with Azure platform capabilities, and avoids unnecessary complexity while still meeting regulatory expectations.

Primary Region: Apps behind Front Door, Azure SQL DB, Synapse workspace, VMs (Self-Hosted IR & Power BI), VPN Gateway, Data Lake Gen2.

Secondary Region (DR): Same components, with SQL Failover Group, Synapse redeployment via CI/CD, VMs replicated via Azure Site Recovery, geo-redundant Data Lake, VPN Gateway.

Front Door handles app failover automatically; SQL uses Failover Group; VMs replicate via ASR; Synapse redeploy manually; Data Lake uses GRS.

Hope this helps, Please let us know if you have any questions and concerns.
Xhevahir Mehalla 45 Reputation points

2026-01-19T08:35:58.6566667+00:00
Thanks, this is a very comprehensive reply, and I appreciate your efforts a lot.

I have few things to consider now:

Right now - we are not creating a Front Door as I understood from the MSFT notes is expensive. (Primary region).

The DR will be decided on in the next two months, but the bank is asking options and prices so I should consider the Front Door as a new resource.

DR - do I use the same subscription for DR or a complete a new subscription? Or do I use a different resource Groups for DR - within the same subscription?

Do I need create the DR env now before we go live or when it happens as even when you create the Prod env takes few days, so it looks I have to create the DR env now? Pls suggest!

As lighter version of a DR which I can suggest the bank - what would that be? Backups we have done on another bank - backups od sql db, apps, ZRS for DaTa lake etc?

Can you suggest smth on this as well?

Thanks

Xhev
Pilladi Padma Sai Manisha 8,730 Reputation points Microsoft External Staff Moderator

2026-01-20T06:27:31.5166667+00:00

Hi Xhevahir Mehalla,
Thanks a lot for the detailed follow‑up and the extra context from the bank. Let me keep this as concrete and short as possible.

On Front Door: you don’t have to deploy it now, but it is worth including as an option in the proposal so the bank understands the cost/benefit. Front Door is billed on requests and outbound data and, for a typical business web app, it is usually a small part of the overall Azure bill compared with SQL or Fabric. If they are very cost‑sensitive, you can start without it and rely on Traffic Manager or even a manual DNS switch during DR, accepting a slower and more manual failover for the web apps.

On subscriptions: from an Azure architecture perspective, both “same subscription, separate resource groups per region” and “separate subscription for DR” are valid. For your size and scenario, it is perfectly fine to keep production and DR in the same prod subscription and simply separate them by region‑specific resource groups, for example rg-prod-regionA-* and rg-dr-regionB-*. This keeps billing and management simpler while still allowing clear separation and RBAC.

On when to build DR: because you must handle a full regional outage within 24 hours, you should not wait for a real incident. At minimum, you should define and partially build the DR environment before go‑live: secondary SQL in a failover group, GZRS or RA‑GZRS for the Data Lake, DR VNets and VPN gateways, a DR Synapse workspace and ASR protection for the two VMs. Use templates/CI‑CD so you can expand or recreate DR resources quickly later, but you do want the core pattern and at least one DR test in place up front.

For the “lighter DR” option: you can present the bank with two tiers. Option A is the warm DR we discussed earlier: SQL failover group to the paired region, geo‑redundant storage, DR Synapse workspace, ASR‑protected VMs and optionally Front Door or Traffic Manager. This gives RPO in minutes and RTO in hours, with higher but predictable monthly cost driven mainly by the secondary SQL and any standby Fabric capacity. Option B is a backup‑centric DR: rely on geo‑redundant SQL backups and IaC to rebuild apps, Synapse and VMs in another region only if needed, and use ZRS or GRS/GZRS on the Data Lake depending on how much regional risk the bank accepts. This is cheaper month‑to‑month (mostly backup storage), but RPO can be up to 24 hours and RTO is closer to the full 24‑hour window with more manual work and higher operational risk.

This way the bank gets clear choices: a warm, faster and more reliable DR pattern versus a cheaper, backup‑only pattern with slower recovery and more operational risk, and you can price both using the Azure calculator for their exact sizes.
Xhevahir Mehalla 45 Reputation points

2026-02-24T15:54:00.99+00:00
Hi Pilladi Padma Sai Manisha,Thanks for all your long asnwers.

I think time has come to go back to the bank to ask them what version they want to chose.

They will chose the safest and cheapest option which they don't go together.

Just so I am clear on this:

Full DR will be:

Front Door or Traffic Manager?

Create VPN, gateways on the same subscription - different resource Group/region.

Create Web apps on the new region the same subscription but different RG.

Use ASR to replicate the two VMs into the new RG/region - sam subscription

Azure sql db: You deploy a secondary database in the paired Azure region and place both databases in a failover group.

Data lake Gen 2 (GZRS would be enough).

Apps - re-create apps on the secondary region/RG under the same subscription.

Synapse - Recreate a new workspace on the other region/RG - the same subscription.

Assume they agree to this; how do I test them? and this is called a warm DR?

The lighter version of DR but not including Region failure which one I would use?

Backups of sql db

VMs use ASR

Apps backup, synapse - not much I can do

Data lake I can use ZRS

Any network components that I can apply DR? VPN gateways, Nat gateway.

Please can you confirm my understanding.

Thanks
Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-02-24T22:28:53.8266667+00:00

Hi Xhevahir Mehalla,

You’re thinking about this in exactly the right way. Let me confirm your understanding and clarify a few points so you can go back to the bank with a clear and confident position.

Starting with Front Door versus Traffic Manager: both are valid options. Front Door gives you faster and more seamless failover because it continuously health-checks the application and redirects traffic automatically. Traffic Manager is DNS-based, so it’s a bit slower, but it’s cheaper and still perfectly acceptable for a reporting workload. If the bank wants the safest and most automated option, Front Door is the better choice. If cost is the bigger concern and slightly slower failover is acceptable, Traffic Manager will work fine.

Regarding subscriptions, you don’t need a separate subscription for DR unless the bank specifically requires strong isolation for governance or compliance reasons. For most environments like yours, keeping everything in the same production subscription but separating primary and DR by region-specific resource groups is completely fine and much simpler to manage.

For full regional protection what we’ve been calling Warm DR your summary is correct. In that model, you would already have the DR infrastructure in place before any incident happens. That includes a secondary Azure SQL database configured in a failover group, geo-redundant storage for the Data Lake, a DR VNet and VPN gateway, replicated VMs using ASR, a second Synapse workspace, and mirrored web apps in the secondary region.

If a region fails, SQL would fail over using the failover group endpoint, application traffic would switch via Front Door or Traffic Manager, and the remaining components (like Synapse execution or VM startup) would follow your DR runbook steps. Some parts are automatic, some are manual, but overall recovery should comfortably fit within your 24-hour requirement. That’s what defines it as Warm DR the infrastructure is there and ready, but not running fully active-active.

Testing this setup is important. You would simulate a failover in a controlled way: trigger SQL failover, confirm the apps reconnect, validate DR VMs and Synapse pipelines, and check reporting. Then you would perform a planned failback and document the total recovery time. For a regulatory reporting system, doing at least one proper DR test before go-live is strongly recommended.

If the bank prefers a lighter and cheaper option, then the approach changes. Instead of a SQL failover group, you would rely on geo-backups. Instead of full regional duplication, you would use zone-redundant storage and redeploy applications and Synapse from templates if needed. This reduces ongoing cost, but recovery takes longer and involves more manual work. That model is often called Cold DR or backup-based DR. It does not give the same level of regional protection as Warm DR.

So in simple terms, you’re really presenting two choices to the bank:

A Warm DR option higher monthly cost, faster recovery, lower operational risk.

A Cold or backup-based option lower cost, slower recovery, more operational effort during an incident.

At this stage, your plan to go back to the bank with these two models and let risk appetite drive the decision makes complete sense.

Hope this helps, Please let us know if you have any questions and concerns.
Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-02-25T19:27:28.45+00:00

Hi Xhevahir Mehalla,

Just checking in to see if the information provided earlier was helpful. Please let us know if you need any additional details or assistance, and we’ll gladly continue supporting you.
Xhevahir Mehalla 45 Reputation points

2026-02-26T08:59:06.7733333+00:00
Thanks for your inputs again.

I think I am fine with most of your comments.

I understand the Warm DR what need to be done in order to set it up. It looks to me we need to recreate pretty much everything as we created prod resources on a different resource Groups on a different Region.

This includes everything including all network related stuff (Vnet, subnets, nsgs, Private dns zones, private envpoints) and creating Traffic Manager as a new resource as we all as creating core resources:

sql db

VMs x2

web apps x3/create a plan here as well?

data lake x2

synapse workspace

deploy synapse/sql db/web apps as well.

Public IP addresses x 2.

And leave them as is in inactive mode - meaning nothing running on DR resources. Is this you are suggesting.

And as for the cold DR - I am not clear what steps I need to take.

Please can you state clearly as this is most likely option they want to take. This will help me. Thank you.

If you have any diagram there then it would help. I have something which I might share as well.

In terms of the costs;

Warm DR - do I say this will costs similar to Prod env?

Cold DR - I have said 30% of the value of prod env? is that correct estimate?

Thanks in advance!
Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-03-03T17:57:32.5+00:00
Hi Xhevahir Mehalla,

Yes, Warm DR means you pre‑create everything in the DR region:

VNet, subnets, NSGs, Private DNS Zones, Private Endpoints

DR VPN Gateway

Web Apps + App Service Plan

2 VMs via ASR

Secondary SQL DB in Failover Group

Secondary Data Lake (GZRS/RA‑GZRS)

Synapse workspace in DR region

Traffic Manager or Front Door

Public IPs, NAT, supporting network resources

These resources exist but stay inactive until failover. Yes — this is exactly what Warm DR means.

Cold DR (Cheapest option) – Clear steps
Cold DR = no DR environment exists until a disaster happens (just backups + templates)

You only keep:

SQL: Geo‑redundant backups (restore during DR)

Storage: ZRS / GRS

VMs: ASR (optional but recommended)

Apps: backups + CI/CD templates

Synapse: deploy from Git when needed

Network: deploy VNets/VPN/PEs only during DR

Manual DNS switch

During a real DR event, you rebuild everything in the other region using backups + templates.

RTO = up to 24 hours RPO = up to 24 hours Cost = low

Cost Estimate

Warm DR: ~50–70% of production cost

Cold DR: ~20–35% of production cost (your 30% estimate is correct)
Manoj Kumar Boyini 16,150 Reputation points Microsoft External Staff Moderator

2026-03-04T12:46:58.4233333+00:00

Hi @Xhevahir Mehalla,

Just checking in to see if the information provided earlier was helpful. Please let us know if you need any additional details or assistance, and we’ll gladly continue supporting you.

Share via

DR for DWH solution

1 answer

1. Understand Your DR Requirements

2. DR Options for Azure SQL Database:

3. Data Ingestion and Processing with Synapse:

4. Support for Reporting and BI Tools:

5. Network Configuration:

6. Cost Considerations:

7. Documentation and Further Resources:

Follow-Up Questions:

Your answer