Azure SQL Data Sync | Tips and Tricks

Full article: http://vunvulearadu.blogspot.ro/2017/12/azure-sql-data-sync-tips-and-tricks.html

Table’s creation

SQL Data Sync creates automatically the tables and columns on the destination database when they don’t exist. There is no need to do this step by yourself. Also, you can add an empty database that will be automatically populated with the database structure and content by SQL Data Sync.

On-premises sync firewall

To be able to communicate with the Sync Agent Gateway that runs on your on-premises machine, you will need to configure port 1433 port for outbound traffic. On your Azure Database, don’t forget to configure the firewall to allow communication with your on-premises system also (custom rule).

Synchronization Time and Flow

The minimal time interval for synchronization is 5 minutes. There is no support for data transformation or validation flows. For any ETL flows you can use with success Azure Data Factory where on top of Pipelines you can define any kind of flows (there is support to run SSIS packages).

The synchronization flows runs all the time from/to Sync Member to the Hub. This means that if you have a bi-directional synchronization configured and new data will appear in the Sync Member, it will required two sync iterations until data will land to the other Sync Members.

This is happening because when the sync runs for the 1st time, data from Sync Member is pushed to the Hub. In the sync iteration 2 the data is pushed from the Hub to the other Sync Members. This means that if you have the data synchronization frequently set to 5 minutes, it will take 10 minutes for data to arrive in all the other Sync Members

Database Structure Provisioning

Even if the tables and columns that needs to be synchronized can be automatically created by the SQL Data Sync in the destination database, no additional database configuration or original structure is recreated in the destination database.

Because of this, the following items are not re-created automatically:

• Views
• Stored Procedures
• Triggers
• Index on XML columns
• CHECK constraints
• Index on other columns except the one that are synchronized
• Columns and tables that are not synchronized

The reality for production environments is that you will never allow a system to create your database structure without having control to the schema. You will always end-up running your database creation script on each node of your cluster.

Full article: http://vunvulearadu.blogspot.ro/2017/12/azure-sql-data-sync-tips-and-tricks.html