SQL Data Sync

Overview

SQL Data Sync is a cloud service that enables you to synchronize data between multiple Azure SQL databases and on‑premises SQL Server instances. It supports bi‑directional or hub‑and‑spoke topologies, conflict resolution, and schema synchronization.

Prerequisites

• Azure subscription with an active Azure portal account.
• An Azure SQL Database or an Azure SQL Managed Instance.
• SQL Server Management Studio (SSMS) 18+.
• Basic knowledge of Transact‑SQL.

Step‑by‑Step Setup Guide

1. Create a Sync Group
   In the Azure portal, navigate to your SQL database → Sync to other databases → New Sync Group. Choose a hub database and give the group a name.
2. Add Sync Members
   Click Add sync member and select each member database (Azure or on‑premises). For on‑premises, you’ll need a Sync Agent installed.
3. Define Sync Schema
   Select the tables and columns you wish to sync. Use the Sync direction options: Bidirectional, Hub to Member, or Member to Hub.
4. Configure Conflict Resolution
   Choose either Hub wins or Member wins. You can also write a custom stored procedure.
5. Schedule Sync
   Set the sync frequency (every 5 minutes, hourly, etc.) or trigger manual sync via PowerShell/CLI.

PowerShell Sample: Triggering a Manual Sync

# Install Azure PowerShell module if needed
Install-Module -Name Az -AllowClobber -Scope CurrentUser

# Connect to your Azure account
Connect-AzAccount

# Variables
$resourceGroup = "MyResourceGroup"
$serverName    = "myhubdbserver"
$syncGroupName = "ProductsSync"

# Trigger a manual sync
Invoke-AzSqlDatabaseSyncGroupSync `
    -ResourceGroupName $resourceGroup `
    -ServerName $serverName `
    -DatabaseName "hubdb" `
    -SyncGroupName $syncGroupName