Before we talk about the Azure SQL Data Sync Limitations, we will need to understand the prerequisites first. The Azure Data Sync has been around for a while (October 2011), and it got better over the years. At the time of writing this, it was still in preview, and we expect this to be released shortly.
The new version supports On-premises SQL Server instance to act as a subscriber, which we think is excellent. For starters, if you are from the SQL Server On-premises world, and have tickled with SQL Server replication services, you will feel right at home. The Azure SQL Data Sync feature is a hybrid of Merge & Transactional replication. At least, that is what we think.
Image Source: Microsoft
However, the setup complexity is less, and you should be up and running in a few minutes. This was not the case with the on-premises edition. Anyways, let us look at the system requirements first.
Hybrid Data Synchronization – Data synchronized between your on-premises databases and Azure SQL Databases. For customers who would like to put some of their application in Azure
Distributed Applications – Separate different workloads across different databases. Run a reporting or analytics workload separate from the production workload
Globally Distributed Applications – To minimize network latency and have your data in a region close to where is primary used
However, you must remember that “Data Sync” is not a recommended solution for Disaster Recovery, Read Scale, ETL (OLTP to OLAP) or migration scenarios.
If you want an uneventful deployment, ensure that the setup has met the minimum prerequisites first. Let us look at the system requirements first.
The Data Sync is a cloud feature, and there is not much to set up. However, for the On-premises SQL Server, here are the system requirements:
The table must have a primary key. It must not be changed, and it cannot be: sql_variant, binary, varbinary, image, XML
Snapshot isolation must be enabled
An identity column must be a primary key
Supported precision is only to the second when used as PK: time, DateTime, datetime2, datetimeoffset
Names of objects cannot contain “.”, “[,“ “]”
Azure Active Directory authentication is not supported
Unsupported data types: FileStream, SQL/CLR UDT, XMLSchemaCollection, Cursor, RowVersion, Timestamp, Hierarchyid
Data Sync can’t sync read-only or system-generated columns
Now, that we have the system requirements clarified, let us look at some of the limitations.
When you create a sync group, be sure that only one of the databases contains data before the first synchronization. If multiple databases include data, each current row is treated as a data conflict, even if the data is identical. Data conflicts significantly slow down a synchronization. Depending on the size of your DB, if multiple databases are populated with data, the first sync can take days or weeks. Conflict resolution also requires numerous round trips between the databases. If your databases are in different data centers, these round trips can add significantly to your ingress and egress charges.
The subsequent changes to a database schema cannot be applied to a sync group. If you want to consolidate any of these changes to your sync group, you need to delete and re-create the sync association.
Getting started: SQL Data Sync
Data Types supported by SQL Data Sync
SQL Data Sync Data Security
SQL Data Sync Troubleshooting Guide
Do you have any insight if azure data sync is ever coming out of preview mode? We have been struggling to use it since ~2012 and it remains the single biggest issue we have with Azure, and our biggest caveat when we discuss if people should use Azure vs AWS.
Comments are closed.