Building a Custom Merge Replication for Selected Tables Only (SQL Server + .NET)

Rajesh Gami
3h
87
0
0

Article

This article walks through how to implement your own lightweight merge-replication system—synchronizing only selected tables, handling conflicts, and ensuring incremental sync between two databases (HQ ↔ Branch or Cloud ↔ On-Premise).

This is not SQL Server native Merge Replication.
This is a custom-built system that is easier to control, version, secure, and monitor.

1. Why Custom Merge Replication?

Most organizations avoid SQL Server’s built-in Merge Replication because:

It is complex and rigid
Hard to debug
Heavy on performance
Slow with large tables
Doesn’t allow per-table custom logic
Doesn’t play well with cloud deployments (Azure SQL)

A custom merge replication is ideal when:

Only selected tables should sync
Incremental sync is required
Conflict resolution rules need business logic
Offline apps must sync later
APIs should sync to SQL Server

2. Architecture Overview

Components

Publisher Database (Master)
Subscriber Database (Local / Branch)
Sync API (ASP.NET Core)
Sync Metadata Tables
CDC or Trigger-based tracking
Conflict Resolution Engine

3. Tables Included in Replication

You can choose exactly which tables to sync.

Example

Table Name	Sync?	Notes
Customers	Yes	Master data
Orders	Yes	With conflict rules
Products	Yes	Static data
Logs	No	No sync needed

4. Required Metadata Tables

Create a metadata table for each replicated table.

4.1 SyncVersion Table

CREATE TABLE SyncVersion (
    TableName NVARCHAR(100),
    LastSyncedAt DATETIME2
);

4.2 ChangeTracking Table

Tracks inserts, updates, deletes.

CREATE TABLE SyncChanges (
    ChangeId BIGINT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(100),
    PrimaryKeyValue NVARCHAR(200),
    ChangeType CHAR(1), -- I / U / D
    ChangedAt DATETIME2 DEFAULT SYSDATETIME(),
    RowData NVARCHAR(MAX) -- JSON
);

5. Implement Triggers for Change Capture

Insert Trigger

CREATE TRIGGER trg_Customers_Insert
ON Customers
AFTER INSERTASINSERT INTO SyncChanges(TableName, PrimaryKeyValue, ChangeType, RowData)
SELECT 
    'Customers',
    CAST(i.CustomerId AS NVARCHAR(20)),
    'I',
    (SELECT * FROM inserted i FOR JSON AUTO)
FROM inserted i;

Update Trigger

CREATE TRIGGER trg_Customers_Update
ON Customers
AFTER UPDATEASINSERT INTO SyncChanges(TableName, PrimaryKeyValue, ChangeType, RowData)
SELECT 
    'Customers',
    CAST(i.CustomerId AS NVARCHAR(20)),
    'U',
    (SELECT * FROM inserted i FOR JSON AUTO)
FROM inserted i;

Delete Trigger

CREATE TRIGGER trg_Customers_Delete
ON Customers
AFTER DELETEASINSERT INTO SyncChanges(TableName, PrimaryKeyValue, ChangeType, RowData)
SELECT 
    'Customers',
    CAST(d.CustomerId AS NVARCHAR(20)),
    'D',
    (SELECT * FROM deleted d FOR JSON AUTO)
FROM deleted d;

6. Building the Sync API (ASP.NET Core)

6.1 Controller Route

POST /api/sync/pull
POST /api/sync/push

6.2 API: Pull Changes (HQ → Branch)

[HttpPost("pull")]
public async Task<IActionResult> PullChanges([FromBody] SyncRequest req)
{
    var changes = await _db.SyncChanges
        .Where(c => c.ChangedAt > req.LastSyncDate)
        .OrderBy(c => c.ChangeId)
        .ToListAsync();

    return Ok(new {
        Changes = changes,
        ServerTime = DateTime.UtcNow
    });
}

6.3 API: Push Changes (Branch → HQ)

[HttpPost("push")]
public async Task<IActionResult> PushChanges([FromBody] PushPayload payload)
{
    foreach (var change in payload.Changes)
    {
        await _syncService.ApplyChange(change);
    }

    return Ok(new { Status = "Success" });
}

7. Applying Changes on Subscriber (Conflict Logic)

Conflict Rule Example

“Latest timestamp wins, except when status changes to ‘Approved’ — approvals override everything.”

public async Task ApplyChange(ChangeModel change)
{
    if (change.ChangeType == "I")
        await InsertRow(change);

    else if (change.ChangeType == "U")
        await UpdateRow(change);

    else if (change.ChangeType == "D")
        await DeleteRow(change);
}

8. Client-Side Sync Agent (Windows/Angular/.NET)

Key responsibilities

Call /pull
Apply server changes
Track local changes
Call /push
Resolve conflicts

Example simplified sync loop

var serverChanges = await Api.Pull(lastSyncDate);
ApplyServerUpdates(serverChanges);

var localChanges = GetLocalChanges();
await Api.Push(localChanges);

9. Synchronization Workflow Diagram

Branch DB --> Push API --> HQ DB (apply changes)
HQ DB --> Pull API --> Branch DB (apply changes)

10. Scheduling Sync

Windows Task Scheduler

Run every 5 minutes:

dotnet SyncAgent.dll

In Web Application (BackgroundService)

public class SyncWorker : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await Sync();
            await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
        }
    }
}

11. Database Stored Procedures

Fetch Changes

CREATE PROCEDURE usp_GetChanges
    @LastSync DATETIME2
ASSELECT * FROM SyncChanges
WHERE ChangedAt > @LastSyncORDER BY ChangeId;

Apply Change

CREATE PROCEDURE usp_ApplyChange
    @TableName NVARCHAR(100),
    @PrimaryKey NVARCHAR(100),
    @ChangeType CHAR(1),
    @RowData NVARCHAR(MAX)
ASBEGIN
    IF @ChangeType = 'D'
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = 
            'DELETE FROM ' + @TableName + ' WHERE Id = ' + @PrimaryKey;
        EXEC(@sql);
        RETURN;
    END

    DECLARE @sql NVARCHAR(MAX) = 
        'MERGE ' + @TableName + ' AS t
         USING (SELECT * FROM OPENJSON(@RowData) WITH (...)) AS s
         ON t.Id = s.Id
         WHEN MATCHED THEN UPDATE SET ...
         WHEN NOT MATCHED THEN INSERT ...;';
         
    EXEC sp_executesql @sql, N'@RowData NVARCHAR(MAX)', @RowData;
END

12. Optimizing Large Sync Operations

Strategies

Sync per-table, not global
Sync only changed rows (incremental)
Compress payload using GZip
Use batching (100–1000 records per block)
Drop indexes during massive insert, re-create later
Archive old changes every month

13. Enhancements for Production

Encryption on payload (AES)
HTTPS only
JWT authentication
Local queue when internet is offline
Retry mechanism
Row-level versioning (GUID or Timestamp)
Sync Dashboard (failed, pending, applied, conflicts)