Databases

Building a Windows Service for Bidirectional SQL Server ↔ Supabase Sync

Srinivasan Sankaranarayanan

— 3 min read
Share

This is the technical sequel to my SQL Server to Supabase post. There I argued that coexistence — both databases live, data flowing between them — deserves its own workstream. This is what that workstream looks like as an actual build: a VB.NET Windows Service that syncs bidirectionally between an on-premise SQL Server and a Supabase Postgres instance.

Why VB.NET? Because that's what the existing on-premise codebase is, the team maintains it fluently, and a sync service is the wrong place to introduce a new language. Boring technology, deliberately chosen.

The service skeleton

A Windows Service gives you exactly what a sync process needs: starts with the machine, runs unattended, restarts on failure via the Service Control Manager. The skeleton is a timer loop around a sync cycle:

Public Class SyncService
    Inherits ServiceBase

    Private WithEvents SyncTimer As New Timers.Timer(30000) ' 30s cycle

    Protected Overrides Sub OnStart(args() As String)
        SyncTimer.Start()
    End Sub

    Private Sub OnTimerElapsed(sender As Object, e As Timers.ElapsedEventArgs) _
        Handles SyncTimer.Elapsed
        SyncTimer.Stop()   ' prevent overlapping cycles
        Try
            RunSyncCycle()
        Catch ex As Exception
            EventLog.WriteEntry("SyncService", ex.ToString(), EventLogEntryType.Error)
        Finally
            SyncTimer.Start()
        End Try
    End Sub
End Class

The stop/start around the cycle matters — if a cycle runs longer than the interval, you do not want a second cycle starting on top of it. One cycle at a time, always.

Change tracking: don't invent it

The core problem in any sync is answering "what changed since last time?" On the SQL Server side, don't build this yourself with triggers and audit tables — SQL Server has native Change Tracking (lighter than Change Data Capture, sufficient for sync):

ALTER DATABASE MyDb SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE dbo.Investments ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = OFF)

Each sync cycle then asks for changes since the last synced version:

SELECT ct.SYS_CHANGE_OPERATION, ct.Id, i.*
FROM CHANGETABLE(CHANGES dbo.Investments, @LastSyncVersion) ct
LEFT JOIN dbo.Investments i ON i.Id = ct.Id

On the Postgres side, the equivalent is a updated_at timestamptz column maintained by a trigger, plus a soft-delete flag — Postgres has no built-in change tracking, so the timestamp-watermark pattern is the standard answer. Store both watermarks (SQL Server's version number, Postgres's last-seen timestamp) in a small sync-state table so the service survives restarts without re-syncing the world.

Talking to Supabase from VB.NET

Skip the Supabase client libraries — they're JavaScript/Dart-first. From .NET, go straight to Postgres with Npgsql, connecting to Supabase's connection pooler endpoint. It's just a Postgres connection string, and you get full ADO.NET semantics your VB.NET code already uses:

Using conn As New NpgsqlConnection(SupabaseConnString)
    conn.Open()
    Using cmd As New NpgsqlCommand(
        "INSERT INTO investments (id, name, amount, updated_at)
         VALUES (@id, @name, @amount, now())
         ON CONFLICT (id) DO UPDATE
         SET name = EXCLUDED.name,
             amount = EXCLUDED.amount,
             updated_at = now()
         WHERE investments.updated_at < EXCLUDED.updated_at", conn)
        ' add parameters, execute
    End Using
End Using

That ON CONFLICT ... DO UPDATE ... WHERE is doing real work: it's an upsert that only applies if the incoming row is newer — conflict resolution enforced in the statement itself.

Conflict resolution: pick a rule you can explain

Bidirectional sync means the same row can change on both sides between cycles. There is no clever answer — there is only a rule, chosen deliberately:

Last-writer-wins on a timestamp is the honest default, with one crucial refinement: designate a system of record per table. In our context, on-premise SQL Server owns compliance-critical tables (its writes always win); Supabase owns tables that are cloud-native by nature. Symmetric last-writer-wins across every table sounds fair and audits terribly — when a compliance officer asks why a value changed, "whichever side wrote last" is not an answer you want to give.

The details that bite

Deletes. A hard delete leaves nothing to sync. Use soft deletes (an is_deleted flag) on both sides, sync the flag like any column, and purge later with a maintenance job.

Identity collisions. If both sides can insert, integer identity columns will collide. Move shared tables to UUIDs, or partition ranges per side. UUIDs are the less clever, more reliable option.

Batching. Sync in fixed-size batches (500–1000 rows), commit the watermark after each batch — a crash mid-cycle then costs one batch, not the whole cycle.

Observability. Write a one-row summary per cycle (rows in, rows out, conflicts, duration) to a log table. When sync "seems slow" three months later, this table is the difference between an answer and an archaeology project.

What I'd emphasize

The sync loop is a weekend of code. The durable engineering is in the decisions around it: native change tracking over homegrown triggers, watermarks that survive restarts, a conflict rule you can defend to an auditor, and soft deletes from day one. Get those four right and the service becomes the most boring component you run — which is exactly what you want from infrastructure that moves compliance data.

Read more