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.
When to use SQL Data Sync?
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.
Azure SQL Data Sync Limitations
If you want an uneventful deployment, ensure that the setup has met the minimum prerequisites first. Let us look at the system requirements first.
System Requirements for Azure Data Sync
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:
- A minimum of SQL Server 2005 SP2. However, it works best with SQL Server 2008 (R2 and later) as support for SQL Database was added to SQL Server Management Studio in SQL Server 2008 R2. Download a free trial from SQL Server Evaluations if you don’t have one.
- SQL Data Sync (Preview) only supports full editions of SQL Server and Azure SQL Database. Express versions of SQL Server are not supported.
- A Microsoft Azure account and subscription.
- A SQL Database subscription.
- You need at least one SQL Database deployed. If you need to host SQL Database instances in multiple data centers, you need a SQL Database implemented for each data center.
- Windows Server 2008 or later or Windows Vista or later versions of Windows OS.
- An x86 or x64 computer.
- The latest version of the Sync Agent.
- .NET Framework 4.5 or above
-
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.
- The maximum number of sync groups support is five (5)
- Maximum number of endpoints supported is thirty (30)
- Maximum On-premises endpoints supported is five (5)
- Maximum 12 filters per table being replicated. This is for performance reasons.
Dimension Limit
- The database, table, schema, & column names: 50 characters per name
- Tables in a sync group: 500
- Columns in a table in a sync group: 1000
- Data row size on a table: 24Mb
The SQL Server Agent best practices:
- Install the client agent using the least privilege account with network service access.
- It is best if the client agent is installed on a computer separate from your on-premises SQL
Server computer. - Do not register an on-premises DB with more than one agent.
- Even if syncing different tables for different sync groups.
- Registering an on-premises database
Best Practices before Data-Sync
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.
Sync Group-Update-Limitations
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.
- Changing a column’s data-type.
- Add, remove or modify a column’s filter.
- Remove a column that has a filter
Official Documentation:
Getting started: SQL Data Sync
Data Types supported by SQL Data Sync
SQL Data Sync Data Security
SQL Data Sync Troubleshooting Guide