Azure Data Sync
What is Data sync:
SQL Data Sync is Azure service which allows you to synchronize data across
multiple Azure SQL databases and on-premises SQL Server databases .
How it works:
SQL Data Sync uses a hub- spoke topology to synchronize data.
[Link] Sync Works By Tracking Data Changes By Using Insert, Update And Delete
Triggers In A Separate Table In The User Database. Then Sync Db Takes Care Of
Merging The Tracked Data To All Other Member Databases
Note: Data Sync Related Tables Will Be Created In Syncdb ,Source And Destination
Databases
Basic Requirements:
[Link] have to open outbound TCP port 1433 in the firewall for data sync client
agent communicate with the server
[Link] your Public IP into firewall white list
[Link] the Allow access to Azure services to ON
4. Need Primary key for every tables which is going to participate in data sync
Limitations:
1. Currently Azure SQL Data Sync does not support Azure SQL Database
Managed Instance
2. Data sync doesn't support Azure active directory authentication
3. Data sync is not supported for SQL Server express editions
4. Data sync doesn't support tables with the same name but different schema
a. Ex:[Link]
b. [Link]
5. schema changes are not automatically synced.
6. we have to manually add column/remove column in destination database
when a new column is created or dropped in metadata sync group. and also
Need to refresh schema in the portal.
7. Check datatypes are supported or not by data sync
8. A table participating in data sync can't have an identify column that's not
the primary key
9. The datetime data type cannot be used for a primary key
[Link] triggers on the source tables aren't provisioned.
[Link] names of objects (databases, tables, and columns) can't contain the
printable characters period (.), left square bracket ([), or right square
bracket (]).
[Link] tables in one sync group is 500
[Link] columns that one table can have in one sync group is 1000
15.
[Link] number of sync groups any database can belong to is 5 a.
17.
[Link] number of endpoints in a single sync group is 30 a.
[Link] up to 24 MB in size can be synced
[Link] sync interval is 5 minutes
[Link] Sure .Net 4.5 or above Installed (for data sync client agent)
[Link] and stored procedures aren't created on the destination database.
[Link] with User Defined Data Types aren't supported
[Link] on XML type columns, CHECK constraints aren't provisioned.
[Link] Sync can't sync read-only or system-generated columns
A. Computed columns
B. System-generated columns for temporal tables.
Demo Environment: Azure sql database to Onpremise [Bidirectional]
Azure SQL Databases Bi Directional On Premise SQL Server
Sqlazure Testdb
syncdb
[Link] Admin\Ban2017
Common Reasons why data sync get fails?
1. Check data sync agent is in online or not.
2. Check sufficient drive space is there or not on which drive you installed
data sync agent
3. If data sync service account got deleted and disabled or service stopped
4. By changing the data sync agent token key
5. For not following limitations
Clean Up The data Sync: By any reason if you don't like the data sync. Then its a
best approach to delete data sync related table by using some scripts.
Note: All the Limitations will changed from time to time
All The Best
