Skip to content

bobydo/Azure-2ways-sync

BranchesTags

Repository files navigation

Multi-source two-way data synchronization using Azure Functions (C#), Azure SQL, and GitHub Actions.

Demonstrates: two-way sync, conflict resolution, optimistic concurrency (ETag / RowVersion), configurable source priority, field merge, retry, echo-loop prevention, and dead-letter — the same concepts used in real Power Automate + Dataverse projects.

Architecture

YOUR MACHINE                          AZURE
─────────────────────                 ──────────────────────────────────────

SQL Server LocalDB                    Azure Function (C# .NET 8)
  └── db-sync-local                     SyncTrigger.cs
      ├── LocalRecords  ◄──────────┐    • Validates payload
      ├── SyncOutbox    ──────┐    │    • Reads CanonicalRecords
      └── (trigger)           │    │    • Calls ConflictResolver.Resolve()
                              │    │    • Upserts with RowVersion (ETag)
LocalConnector (C# console)   │    │    • Retries once on ETag mismatch
  PUSH: SyncOutbox ───────────┼────►    • Writes OutboundChanges
  PULL: OutboundChanges ◄─────┼────┘    • Logs conflicts to ConflictLog
        (TargetSource=LocalDB)│
                              │        Azure SQL (db-sync)
                              │          ├── SourceRecords      inbound events
                              │          ├── CanonicalRecords   source of truth
                              │          ├── ConflictLog        manual review queue
                              │          ├── SourcePriority     configurable priority
                              └──────────└── OutboundChanges    outbound queue

  CRM / ERP / Portal (simulated)
      └── POST /api/sync ───────────► Azure Function (same path as LocalDB)

  CI/CD
      Pull Request ─► build job (deploy.yml) — must pass before merge
      Push to main ─► build job → deploy job (deploy.yml, needs: build)

Two-Way Sync Flow

1. You INSERT/UPDATE a row in LocalRecords
        │
        ▼
2. SQL trigger fires → inserts row into SyncOutbox (Sent=0)
        │
        ▼
3. LocalConnector PUSH: reads SyncOutbox → POSTs to Azure Function
        │
        ▼
4. Azure Function resolves conflict → updates CanonicalRecords
        │
        ▼
5. Azure Function inserts OutboundChanges for every OTHER source
        │
        ▼
6. LocalConnector PULL: reads OutboundChanges (TargetSource='LocalDB', Sent=0)
        │
        ▼
7. Applies changes to LocalRecords (trigger suppressed via SESSION_CONTEXT)
   Marks OutboundChanges.Sent = 1

Echo loop prevention: When the connector writes inbound changes to LocalRecords, it sets SESSION_CONTEXT('sync_inbound') = 1. The trigger detects this and skips SyncOutbox, breaking the loop.

Retry cap: Failed POSTs increment SyncOutbox.RetryCount. After 5 failures the row is skipped permanently and can be inspected for manual fix.

Conflict Resolution Ladder

Source priority is stored in dbo.SourcePriority — change it in the DB without redeploying code.

Incoming write arrives
        │
        ▼
1. No existing record? → NewRecord (clean insert)
        │
        ▼
2. Incoming Version > canonical? → VERSION_WIN (write it)
        │
        ▼
3. Incoming Version < canonical? → STALE_SKIP (discard)
        │ same version
        ▼
4. Non-overlapping fields? → FIELD_MERGE (merge both)
        │ same version, overlapping fields
        ▼
5. Source priority wins → PRIORITY_WIN  (CRM=1 > ERP=2 > Portal=3 > LocalDB=4)
        │ same priority, same field conflict
        ▼
6. MANUAL → logged in ConflictLog, returns HTTP 409

Project Structure

azure-sync-demo/
├── .github/workflows/
│   └── deploy.yml              # Job 1: build C#  →  Job 2: deploy to Azure (needs: build)
├── infra/
│   └── setup.sh                # Azure CLI: creates all resources in one run
├── src/
│   ├── SyncFunction/           # Azure Function (C# .NET 8 isolated worker)
│   │   ├── SyncFunction.csproj
│   │   ├── Program.cs
│   │   ├── SyncTrigger.cs      # POST /api/sync — entry point
│   │   ├── ConflictResolver.cs # deterministic 6-step resolution ladder
│   │   ├── host.json
│   │   ├── local.settings.json # local dev config (fill in SQL_CONNECTION_STRING)
│   │   └── Models/
│   │       ├── SyncRecord.cs       # incoming JSON body
│   │       └── ConflictResult.cs   # resolution output
│   └── LocalConnector/         # C# console app — two-way sync bridge
│       ├── LocalConnector.csproj
│       ├── Program.cs          # push + pull polling loop (every 30s)
│       └── appsettings.json    # fill in AzureConnectionString + FunctionUrl
├── sql/
│   ├── 01_create_tables.sql    # Azure SQL: all 5 tables + SourcePriority seed data
│   ├── 02_seed_data.sql        # 5 conflict scenarios (CUST-001 to CUST-005)
│   ├── 03_verify_sync.sql      # verify results + outbound queue status
│   └── local_server_setup.sql  # LocalDB: LocalRecords, SyncOutbox, trigger
└── README.md

Getting Started

Prerequisites

Tool Purpose Install
Azure CLI Create cloud resources https://learn.microsoft.com/cli/azure/install-azure-cli
.NET 8 SDK Build C# projects https://dotnet.microsoft.com/download/dotnet/8
Azure Functions Core Tools Run function locally npm install -g azure-functions-core-tools@4
VS Code MSSQL extension Run SQL scripts Install from VS Code Extensions

SQL Server LocalDB is included with Visual Studio. Verify with: sqllocaldb info

Step 1 — Create Azure Resources

az login
chmod +x infra/setup.sh
bash infra/setup.sh

Prints FUNCTION_URL and SQL_CONNECTION_STRING. Save both.

Step 2 — Create Azure SQL Tables

Open sql/01_create_tables.sql in VS Code, connect to your Azure SQL server via the MSSQL extension, and press Ctrl+Shift+E.

Then run sql/02_seed_data.sql the same way to load 5 conflict test scenarios.

Step 3 — Run the Function Locally

cd src/SyncFunction
# Paste SQL_CONNECTION_STRING into local.settings.json first
func start

Function listens at http://localhost:7071/api/sync.

Step 4 — Set Up Local Two-Way Sync (optional)

# 1. In VS Code MSSQL: connect to (localdb)\MSSQLLocalDB, run:
#    sql/local_server_setup.sql

# 2. Fill in src/LocalConnector/appsettings.json:
#    AzureConnectionString  ← SQL connection string from setup.sh
#    FunctionUrl            ← printed by setup.sh

# 3. Start the connector:
cd src/LocalConnector
dotnet run

The connector polls every 30 seconds. Insert a row in LocalRecords to trigger a sync cycle.

Step 5 — Verify Results

Open sql/03_verify_sync.sql in VS Code and press Ctrl+Shift+E.

Expected canonical state after seed data:

ExternalId LastSource Resolution
CUST-001 CRM PRIORITY_WIN (CRM beats ERP, same version)
CUST-002 CRM STALE_SKIP (Portal v1 lost to CRM v2)
CUST-003 ERP FIELD_MERGE (name from CRM, email from ERP)
CUST-004 CRM MANUAL (logged — same version, same field, different value)
CUST-005 CRM NewRecord (first write, no conflict)

Step 6 — Deploy to Azure via GitHub Actions

  1. Push repo to GitHub.
  2. Add these GitHub Secrets (Settings → Secrets → Actions):
Secret Value
AZURE_CREDENTIALS az ad sp create-for-rbac --name "sync-demo-deploy" --sdk-auth --role contributor --scope /subscriptions/<sub-id>/resourceGroups/Azure_Sync_Demo
AZURE_FUNCTION_APP_NAME e.g. func-sync-demo-mfnpdr
SQL_CONNECTION_STRING Printed by setup.sh
FUNCTION_URL Printed by setup.sh
  1. Push to main — deploy.yml builds then deploys automatically.

Failure Handling

Scenario Cause Outcome
StaleSkip (HTTP 200) Incoming version older than canonical Discarded silently
FieldMerge (HTTP 200) Non-overlapping fields Merged into canonical
Manual (HTTP 409) Unresolvable conflict Logged in ConflictLog, needs human fix
ETag mismatch Concurrent writer changed record mid-flight Retried once automatically
HTTP 400 Bad JSON or missing fields Rejected, not retried
HTTP 500 Unexpected error Check Application Insights logs
RetryCount >= 5 Connector POST permanently failing Skipped — query SyncOutbox WHERE RetryCount >= 5
Echo loop Inbound write re-triggering outbox Blocked by SESSION_CONTEXT('sync_inbound') in trigger

To replay a MANUAL conflict after human review:

curl -X POST "$FUNCTION_URL" \
  -H "Content-Type: application/json" \
  -d '{"externalId":"CUST-004","source":"CRM","version":6,"fullName":"David Brown","email":"david.corrected@crm.com"}'

Changing Source Priority

No code changes needed — update dbo.SourcePriority directly:

-- Promote LocalDB above ERP:
UPDATE dbo.SourcePriority SET Priority = 2 WHERE Source = 'LocalDB';
UPDATE dbo.SourcePriority SET Priority = 3 WHERE Source = 'ERP';

How This Relates to Dataverse

If a future project uses Power Automate + Dataverse, only the API layer changes — the logic stays identical:

This demo Real Dataverse project
WHERE RowVersion = @etag OData PATCH with If-Match: "<etag>" header
Azure SQL returns 0 rows on ETag mismatch Dataverse returns HTTP 412
GitHub Actions triggers the flow Power Automate HTTP action calls the Function
dbo.CanonicalRecords Dataverse Table (entity)
dbo.ConflictLog Custom Dataverse Table or SharePoint list
dbo.OutboundChanges + LocalConnector Power Automate cloud flow pushing back to sources
dbo.SourcePriority Dataverse config table or Environment Variable

Power Automate + Dataverse + Azure Function Pipeline

Full pipeline using Client and Order as a real-world example.

1773064885079


---

### Order dependency: what happens if Client doesn't exist yet

CRM sends Order ORD-001 (clientId = CUST-001) │ ▼ Azure Function receives Order │ ├─ Check: does CUST-001 exist in CanonicalRecords? │ │ NO ──► Return HTTP 409 "dependency not met" │ │ │ ▼ │ Power Automate catches 409 │ │ │ ▼ │ ┌─────────────────┐ │ │ Azure Queue │ ◄── reprocess queue │ │ Storage │ (or SyncOutbox.RetryCount │ │ │ in this demo) │ │ { │ │ │ "orderId": │ │ │ "ORD-001", │ │ │ "retryAfter": │ │ │ "30s", │ │ │ "retryCount": │ │ │ 1 │ │ │ } │ │ └────────┬────────┘ │ │ retry after delay │ ▼ │ Power Automate picks │ it up again → POST │ to Azure Function │ YES ──► Write Order to CanonicalRecords ✓ Queue outbound to Dataverse


---

### Reprocess queue: this demo vs real Power Automate project

| | This demo | Power Automate + Dataverse |
|---|---|---|
| Retry storage | `SyncOutbox.RetryCount` in SQL | Azure Queue Storage or Service Bus |
| Retry trigger | Connector polls every 30s | Queue message with `visibilityTimeout` delay |
| Dead letter | `RetryCount >= 5` skipped | Dead-letter queue → alert to team |
| Dependency wait | Skip + retry next cycle | Re-queue message with delay |

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages