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:

A custom merge replication is ideal when:

2. Architecture Overview

Components

  1. Publisher Database (Master)

  2. Subscriber Database (Local / Branch)

  3. Sync API (ASP.NET Core)

  4. Sync Metadata Tables

  5. CDC or Trigger-based tracking

  6. Conflict Resolution Engine

3. Tables Included in Replication

You can choose exactly which tables to sync.

Example

Table NameSync?Notes
CustomersYesMaster data
OrdersYesWith conflict rules
ProductsYesStatic data
LogsNoNo 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

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

13. Enhancements for Production