data sync retirement
1 TopicSQL Data Sync Is Retiring: How to Choose the Right Replacement
SQL Data Sync has a retirement date. Your synchronization requirements don't. Data Sync will be retired on 30 September 2027. Most teams already know they have to move off it. What they're less sure about is which replacement will keep the behavior their application relies on. Microsoft lists several alternatives, including Azure Data Factory, Azure Functions, transactional replication, active geo-replication, read replicas and Fabric mirroring. They all move data, but they solve very different problems, so none of them is a drop-in replacement. This post walks through a requirements-first way to choose. What's changing Data Sync synchronizes the tables you select between Azure SQL Database and SQL Server databases, in both directions. Customers mostly use it for three things: Hybrid sync between SQL Server and Azure SQL Database Reporting copies, which move read workloads to another database Globally distributed reads, with copies of the data in other regions As the retirement gets closer, you may find that some operations, such as creating new sync groups, are blocked. That's expected as part of the retirement, not a provisioning fault, so start planning the move rather than troubleshooting the error. Step 1: Take inventory You can't choose a replacement until you know what you have. For each sync group, record its hub, its members, and the tables and columns it syncs. You can list sync groups in the portal or with PowerShell: Get-AzSqlSyncGroup -ResourceGroupName "<rg>" -ServerName "<server>" -DatabaseName "<hub-db>" Get-AzSqlSyncMember -ResourceGroupName "<rg>" -ServerName "<server>" -DatabaseName "<hub-db>" -SyncGroupName "<group>" Data Sync also adds tracking triggers and metadata to every database in the group. To see where it's present: SELECT OBJECT_SCHEMA_NAME(parent_id) AS schema_name, OBJECT_NAME(parent_id) AS table_name, name AS trigger_name FROM sys.triggers WHERE name LIKE '%[_]dss[_]%'; SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID('DataSync'); Next, find out who actually uses the synced data. Check the reports, jobs and applications that read from each member. That will tell you how much latency and drift you can accept. Step 2: Classify each workload Fill in this worksheet once per sync group: Question Example answer Source platform SQL Server 2019 on-premises Destination platform Azure SQL Database Direction One-way / bidirectional Is the destination written to? Read-only / writable Acceptable latency Seconds / minutes / hours Conflict handling needed? None / last-writer-wins / custom Daily change volume ~2 million rows Recovery expectation Resume from last point / full reload acceptable Your answers to direction and writable destination will rule out most of the options straight away. Step 3: Use the requirements to rule options out Requirement Rules out Still in play Bidirectional, both sides writable Geo-replication, read replicas, transactional replication (standard), Fabric mirroring Redesign to a single writer, or a custom solution (ADF / Functions) with your own conflict logic Read-only copy in the same database Everything heavier than needed Read scale-out (read replicas) Read-only copy in another region Read scale-out Active geo-replication, ADF incremental copy SQL Server → Azure SQL Database, near real time ADF batch schedules (if latency is tight) Transactional replication (Azure SQL Database as push subscriber) Azure SQL Database as the source of replication Transactional replication (Azure SQL Database can't be a publisher) ADF, Azure Functions, geo-replication Subset of tables or columns, with transformation Geo-replication, read replicas (whole database only) ADF, Azure Functions Analytics in Fabric – Fabric mirroring One point catches people out: active geo-replication and read replicas are not general-purpose sync. They give you a read-only copy of the whole database. You can't write to the secondary, and you can't pick tables. Bidirectional sync is the hardest case. No managed service replaces it directly. Before you build custom two-way sync, check whether the application really needs to write in two places. Moving to a single writer is often simpler and more reliable. Step 4: One-way incremental copy example For many reporting workloads, one-way incremental sync is all you need. Change tracking keeps it cheap. Run this on the source: ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON); ALTER TABLE dbo.Orders ENABLE CHANGE_TRACKING; On each run, pull only the rows that changed since the last version you stored: DECLARE @last bigint = (SELECT LastVersion FROM dbo.SyncWatermark WHERE TableName = 'dbo.Orders'); DECLARE @current bigint = CHANGE_TRACKING_CURRENT_VERSION(); IF @last < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Orders')) THROW 50001, 'Watermark is older than retention. Full reload required.', 1; SELECT ct.SYS_CHANGE_OPERATION, ct.OrderId, o.* FROM CHANGETABLE(CHANGES dbo.Orders, @last) AS ct LEFT JOIN dbo.Orders AS o ON o.OrderId = ct.OrderId; -- After the destination applies the batch successfully: UPDATE dbo.SyncWatermark SET LastVersion = @current WHERE TableName = 'dbo.Orders'; You can run this from an ADF pipeline or an Azure Function. If a table has a reliable ModifiedDate column and you don't need to track deletes, a timestamp watermark also works. Compare that with a bidirectional requirement. There, you'd also have to detect when both sides changed the same row, choose a winner, and prevent your own writes from echoing back. That's a lot more to build and test, which is why it's worth confirming you really need it. Step 5: Pilot, run both, then validate Don't switch over in one go. Run the new pipeline alongside Data Sync for a while and compare the results. Row counts: SELECT COUNT_BIG(*) AS row_count FROM dbo.Orders; A quick content check (run it on both sides and compare). Checksums can collide, so treat a match as a strong signal rather than proof: SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS table_checksum FROM dbo.Orders; Latency: SELECT DATEDIFF(SECOND, MAX(ModifiedDate), SYSUTCDATETIME()) AS seconds_behind FROM dbo.Orders; Also test recovery. Stop the pipeline for longer than your normal interval, start it again, and confirm it catches up without losing or duplicating rows. Step 6: Decommission Data Sync cleanly Once the new path has been stable through your validation period: Delete the sync group. This stops sync and removes the group's configuration. Uninstall the Data Sync agent from any on-premises SQL Server hosts. Check that the _dss_ triggers and DataSync schema objects are gone from every member. If any are left, drop them in a maintenance window, because leftover triggers add overhead to every write. Remove firewall rules and credentials that only Data Sync used. Migration checklist All sync groups and members inventoried Worksheet completed for each workload Bidirectional needs confirmed or designed out Replacement chosen using the decision table Pilot running in parallel with Data Sync Row count, checksum, latency and recovery checks passed Consumers switched to the new path Sync groups deleted, agents removed, metadata cleaned up Finished well before 30 September 2027 Final thought Turning off Data Sync is the easy part. The real work is understanding what your application expects from it today. Start with the requirements, and the right replacement usually becomes obvious. Reference: SQL Data Sync retirement migration - Azure SQL Database | Microsoft Learn