How to Reliably Sync On-Premise SQL to Cloud Webhooks (Without Losing Data)

#sysadmin #architecture #csharp #dotnet

If you work in enterprise IT, manufacturing, or healthcare, you’ve probably faced this nightmare: You have a legacy, on-premise SQL Server. The business suddenly wants to connect this data to a modern cloud CRM, an external API, or Zapier.

The naive approach? Write a quick Python or C# script that runs on a cron job, queries the database, and sends an HTTP POST request to the webhook.

It works perfectly in testing. But in production? It’s a ticking time bomb.

The Problem: The Internet Blinks

On-premise environments are notorious for network hiccups. If your script tries to push a payload while the network is down—or if the receiving API throws a 503 Service Unavailable—what happens to that data?

If you don't have a robust retry mechanism and a local queue, that payload is gone forever. Data is out of sync, and you wake up to angry emails.

The Architecture of a Reliable Sync

To do this correctly, you can't just write a simple script. You need to build a resilient background worker. Here is the architecture you actually need:

A Windows Service: To ensure the process runs automatically on the server, survives reboots, and runs in the background.
A Local Queue (SQLite): Before sending data to the cloud, you must save the payload locally. If the send fails, the data waits safely in SQLite.
Exponential Backoff (Polly): You can't just spam the destination API if it's down. You need a retry policy that waits 2 seconds, then 4, then 8, etc.

The Implementation (The Hard Way)

If you are building this in .NET, you'll need the Microsoft.Extensions.Hosting.WindowsServices package.

For the retry logic, you'll rely heavily on Polly. Here is a glimpse of what your HTTP client policy should look like:

// Using Polly for Exponential Backoff
var retryPolicy = HttpPolicyExtensions
    .HandleTransientHttpError()
    .WaitAndRetryAsync(5, retryAttempt => 
        TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)), 
        onRetry: (outcome, timespan, retryAttempt, context) => 
        {
            // Log the failure, keep the payload in SQLite queue
            _logger.LogWarning($"Delaying for {timespan.TotalSeconds} seconds, then making retry {retryAttempt}");
        });

Then, you have to build the SQLite repository to handle the queue state (Pending, Processing, Completed, Failed). You have to handle thread safety, ensure you don't send duplicate payloads, and manage the deployment of this custom service to your client's servers.

The "Set It and Forget It" Alternative

Building this architecture takes days. Maintaining it, handling edge cases, and updating it for every new database table takes forever.

I got tired of writing and maintaining brittle custom Windows Services for this exact problem. So, I am building a drop-in, zero-code agent that does it out of the box.

It’s a lightweight Windows Agent where you simply: