Project Summary

Built a fully automated daily pipeline to refresh an Azure reporting database from native SQL Server backups produced by a third-party vendor’s production environment in AWS, using a backup chain Azure SQL Database Serverless has no native way to restore directly. Rather than run a continuously provisioned replication platform for a workload that only needed daily freshness, the pipeline uses a short-lived SQL Server engine to reconstruct the vendor’s latest database state straight from Blob Storage, verifying the correct backup pair by its own metadata rather than trusting filenames, then converts the result into a portable BACPAC and rebuilds the Azure SQL Database Serverless reporting environment every day.

The solution reduced projected monthly operating cost from approximately $1,500 to roughly $70, about a 95% reduction, while completing the full daily refresh in around two hours, with zero query or replication workload against the vendor’s production database.

Problem

A reporting environment depended on data from a third-party vendor’s production SQL Server database, hosted in AWS and completely outside this team’s control. Reporting needed current data every day, but that didn’t require real-time synchronization; the vendor already produced native SQL Server backups on its own schedule, and using those backups as the source meant the reporting pipeline never had to touch the vendor’s production system directly: no agents, no replication, no reporting queries run against it.

The vendor’s backup pattern was a periodic full .bak plus daily differential .inc files, and Azure SQL Database Serverless, the desired reporting platform, can’t consume either format directly. The vendor’s database had to be reconstructed with a full SQL Server engine first, then converted into something Azure SQL Database could actually import.

A fully managed, continuously provisioned platform could have handled the restore, but it was projected at around $1,500 a month for infrastructure that would sit available around the clock to do work needed only once a day. The real architectural question was how little infrastructure it would actually take to reconstruct the vendor’s latest database state once daily and publish it as a reporting database.

Constraints

• The source system was vendor-owned and vendor-managed, with no ability to change its backup process or configuration and no tolerance for any added load on it.
• Reporting needed daily freshness, not real-time synchronization.
• The source arrived as native SQL Server backups (full .bak plus daily .inc differentials), a format Azure SQL Database Serverless can’t restore directly.
• Filenames and blob timestamps couldn’t be trusted on their own to identify which differential backup actually belonged to which full backup; the pairing had to be verified, not assumed.
• An initial managed-service design was projected at roughly $1,500/month, high enough to force a genuinely different approach rather than just paying for the “supported” option.
• The pipeline needed to run unattended, with failures caught during the refresh cycle rather than discovered later as stale or missing reporting data.
• Database-level permissions needed to be recreated automatically after every reporting database replacement.

Discovery and Design

The first real design decision was recognizing this wasn’t a replication problem; it was a daily reconstruction problem. The reporting environment never needed to stay in sync with the vendor’s database throughout the day; it only needed a reliable copy of the most recently completed backup. That distinction is what made it possible to provide a full SQL Server engine only for the short window when native restore semantics were actually needed, instead of keeping one running continuously.

That reframing reduced the whole problem to three operations: reconstruct the vendor’s latest database state, convert it to a BACPAC, and publish that BACPAC into Azure SQL Database Serverless. Everything else in the design exists only to support those three steps as cheaply and simply as possible, including recognizing that SQL Server can restore directly from a Blob Storage URL, which meant the pipeline never needed a file-copy or mount-point step between storage and the restore engine at all.

Architecture

Scheduled Cross-Cloud Ingestion

The pipeline runs on a scheduled Ubuntu Linux VM. During a defined daily window, automation on the VM downloads the vendor’s backup files from AWS S3 and archives them into Azure Blob Storage, organized into folders by week. That weekly organization matters later; the restore step looks at both the current and previous week’s folders as candidates, so a backup that hasn’t landed yet in the new week’s folder doesn’t cause the refresh to fail.

Selecting the Correct Backup by Lineage, Not Filename

Once the week’s backups are in Blob Storage, the pipeline creates a Blob Storage credential on the SQL Server engine itself, a prerequisite for reading anything from Blob directly, including just a backup’s header. With that credential in place, the script lists the candidate .bak/.inc blobs across the current and previous week’s folders and reads each one’s own backup header with RESTORE HEADERONLY FROM URL, rather than trusting the filename or the blob’s upload timestamp to figure out which differential goes with which full backup.

Specifically, it matches a differential backup’s DifferentialBaseLSN against the candidate full backup’s CheckpointLSN, the actual log sequence number (LSN) lineage SQL Server itself uses to know whether a differential can legally apply on top of a given full backup. A file could be renamed, a timestamp could be misleading, or an upload could land out of order; LSN lineage can’t lie about which backups actually belong together. Only once a full backup and a differential are confirmed to match does the pipeline commit to restoring that pair.

An Isolated, Single-Purpose Restore Engine

The restore itself runs inside a temporary Azure Container Instance running SQL Server. The engine originally ran SQL Server 2022 Standard, but after hitting issues traced back to Microsoft’s own source, it was reverted to SQL Server 2019, a case where stepping back a version mattered more than chasing the newest release.

That container has exactly one job: restore the selected full and differential backups directly from their Blob Storage URLs, using the credential already established, and reconstruct the vendor’s latest database state. It’s not the reporting database, it doesn’t serve users, and it doesn’t need clustering, high availability, or persistent storage. Because the restore reads straight from Blob using SQL Server’s own native URL-restore capability, there’s no intermediate file-copy step and nothing to keep synchronized between separate storage locations; once the full backup is restored and the differential applied, the container has done its only job.

Bridging the SQL Platform Gap

From there, SQLPackage, running on the Ubuntu VM and connecting to the restored database in the container, exports it as a BACPAC and uploads it to Blob Storage. That split keeps each piece narrowly scoped: the container’s only job is reconstructing native SQL Server state from Blob, and the VM’s job is orchestration and producing the portable artifact that Azure SQL Database can actually import, which it does directly from the BACPAC’s Blob Storage location, using the same kind of credentialed URL access.

AWS S3
   │
   │ VM downloads, archives by week
   ▼
Azure Blob Storage (weekly folders)
   │
   │ RESTORE HEADERONLY FROM URL
   │ (select full + differential by LSN lineage,
   │  current + previous week's folders)
   ▼
Temporary SQL Server (Azure Container Instance)
   │ RESTORE DATABASE FROM URL
   │ Full + differential restore
   ▼
Latest vendor database state
   │
   │ SQLPackage, run from the VM
   ▼
BACPAC ──► Azure Storage (archive)
   │
   ▼
Azure SQL Database Serverless (import from Blob URL)

A full SQL Server engine is used only for the piece of the workflow that actually requires one, and it talks to Blob Storage directly rather than through any intermediate file staging.

Rebuilding the Reporting Database

The existing reporting database is dropped and rebuilt from the freshly generated BACPAC, a full refresh rather than an incremental sync. That’s a deliberate simplification: the reporting database doesn’t need to track deltas or reconcile state against the vendor’s database, it just becomes a deterministic copy of the vendor’s latest completed backup, rebuilt from scratch every day.

Restoring Identity and Access

Dropping and rebuilding the database daily means its Microsoft Entra ID role and group assignments don’t survive the process, so those get reapplied automatically as part of the same pipeline, before the refresh is considered complete. Without that step, a refresh could succeed technically while quietly breaking reporting access the next morning; treating identity as deployment state, not a manual follow-up, is what makes the daily rebuild sustainable.

Serverless Reporting Tier and Cost Control

The reporting database itself runs on Azure SQL Database Serverless, which auto-pauses compute during periods of inactivity so compute charges track actual reporting use rather than running continuously (storage is still billed while paused, but compute, the expensive part, isn’t). Combined with the container only existing for the restore window and the VM only running during its scheduled hours, the architecture minimizes continuously running compute across the whole pipeline.

That same cost-consciousness extends to how the pipeline gets monitored: logs are uploaded to Blob Storage after each run specifically so they can be reviewed without needing to SSH into or even power on the Linux VM; checking on the pipeline doesn’t require paying to bring compute back online just to look at what happened.

Monitoring and Failure Detection

The full sequence, archiving the week’s backups, selecting and verifying the correct backup pair, restoring, generating the BACPAC, rebuilding the reporting database, restoring identity, and uploading logs, runs unattended once a day, so every stage is logged independently and any single point of failure triggers an immediate notification. A successful connection to yesterday’s reporting database isn’t proof today’s refresh worked; the pipeline monitors the refresh process itself, not just whether the endpoint is reachable.

Engineering Challenges

Avoiding an Overbuilt Solution

The biggest architectural challenge was resisting the assumption that a cross-cloud SQL problem automatically required a continuously running cross-cloud database platform. The workload needed daily freshness, not continuous synchronization; once that distinction was clear, most of the infrastructure normally associated with cross-cloud replication turned out to be unnecessary, and provisioning expensive capability only for the short window it’s actually needed became the main driver behind the eventual cost reduction.

Trusting Backup Lineage Instead of Naming Convention

Early in the design, it would have been easy to just pick “the newest .bak” and “the newest .inc” by filename or upload date and assume they belonged together. That assumption breaks the moment a file is renamed, a clock is off, or an upload lands late, and selecting the wrong pair means wasting a full restore cycle attempting something SQL Server will ultimately reject outright, since the engine won’t apply a differential against a full backup it doesn’t match. Reading each candidate’s actual backup header with RESTORE HEADERONLY FROM URL and validating its backup lineage turns that into an explicit pre-flight check instead of a naming assumption, catching a mismatched pair before committing the restore window to it. Checking both the current and previous week’s folders as candidates, rather than only the current week, was what made this reliable across the week boundary rather than just in the common case.

Converting Native Backup State Into PaaS State

The real technical challenge wasn’t moving files between AWS and Azure; it was bridging two completely different database lifecycle models: native SQL Server backup-and-restore on one side, and Azure SQL Database’s schema-and-data import on the other. The temporary SQL Server container bridges the first gap by reconstructing the database directly from Blob; SQLPackage bridges the second by converting that live state into a BACPAC. Each piece of the pipeline does exactly one job in that translation, nothing more.

Making Daily Drop-and-Rebuild Sustainable

Replacing the entire reporting database every day means anything that only existed as state inside that database has to be deliberately recreated. Permissions were the clearest example; automating the reapplication of Entra ID roles and groups is what kept a daily destructive rebuild from turning into a recurring manual fire drill every morning.

Completing the Refresh Inside a Controlled Window

The entire process, archiving the week’s backups, selecting and verifying the correct backup pair, restoring, generating the BACPAC, rebuilding the reporting database, restoring permissions, uploading logs, completes in about two hours, comfortably inside the VM’s four-hour scheduled window, and at no point does it query or add load to the vendor’s live production system. It only ever reads backup files that system already produces on its own schedule.

Results

• Reduced projected monthly cost from approximately $1,500 (a fully managed-service design) to roughly $70 in practice, about a 95% reduction.
• Delivered a daily cross-cloud reporting refresh despite Azure SQL Database having no native restore path for the vendor’s full-and-differential backup chain.
• Full daily refresh completes in about two hours, with zero query or replication workload against the vendor’s production database.
• Used a full SQL Server engine only for the narrow window that actually required native restore semantics, restoring directly from Blob Storage with no intermediate file-copy step.
• Replaced filename/timestamp guessing with metadata-verified backup selection, matching actual LSN lineage before ever restoring a backup pair.
• Automated re-application of Microsoft Entra ID roles and groups on every rebuild, so the daily drop-and-recreate never silently breaks reporting access.
• Logs uploaded to Blob Storage after every run, so the pipeline can be reviewed without needing to power on or SSH into the VM.
• Per-stage logging with immediate failure notification, catching a broken refresh the same day instead of when a report is quietly wrong.

Technologies

Cross-Cloud & Storage

AWS S3, Azure Blob Storage, native RESTORE FROM URL / RESTORE HEADERONLY FROM URL, cross-cloud data pipeline design

Compute & Restore

Azure Container Instances, SQL Server 2019 (containerized, reverted from an initial SQL Server 2022 Standard attempt), Ubuntu Linux VM, LSN-based backup chain validation

Data Platform

Azure SQL Database (Serverless), BACPAC export/import, SQLPackage

Identity & Security

Microsoft Entra ID, database role and group provisioning, SAS-based Blob Storage credentials

Automation & Operations

Azure Automation, Bash, PowerShell, sqlcmd, scheduled execution, per-stage logging, automated failure notification