Figure 1: Immediately after provisioning databases to use Azure Data Sync, a base table and its associated tracking table will have a 1:1 ratio of rows. The size (Reserved (KB)) of the tracking table can be significant - sometimes even more than the base table as seen when comparing dbo.PeopleType with DataSync.PeopleType_dss_tracking.
· Run the “Disk Usage by Table” report on the database you want to synchronize.
· Start with the estimation that it may require as much space to store the synchronized tables with their associated tracking data as it takes to store just the table.
· Remember that wide primary keys will increase the storage space required as primary keys are stored in the tracking tables.
· Remember that deleted rows are stored for 45 days in the tracking tables. Therefore, with tables with significant numbers of deletes, you may need significantly more than double the storage space of the base table.
1. Create a table with a primary key on col1.
2. Insert a row with col1 = 1 and col2 = 1.
3. Set this table up as a synchronized table.
4. Drop the primary key and re-create it on (col1, col2).
5. Insert a new row with col1 = 1 and col2 = 2.
Just wanted to add a some other points for consideration (at least as of the current version) :
1. Schema Modification - It's not just the PK that is the only schema modification of concern. An addition/removal of a column as well as change in data types will also invalidate a sync group definition. For example, if you have added a 10-column table to a syncgroup and decide to remove one column, this would break the synchronization.
2. PKs - the current release also cannot handle a change in the PK value. A change in a PK value is not propagated during synchronization.
3. Bulk Inserts - since Change Tracking is done via triggers, any bulk operation (BULK_INSERT or BCP) that doesn't fire triggers will not insert or modify values in the tracking tables.
Good One Pram !
Awesome Work here Pram. Keep Kicking !!!