In this post we will talk about things that you should know and take into account when you want to use SQL Data Sync from/to Azure SQL.
What is SQL Data Sync
It’s a synchronization service offered around Azure platform that is allowing us out-of-the-box to synchronize multiple databases that can be inside Azure or on-premises. SQL Data Sync it is one of the services that makes our lives 10x time easier.
The coolest thing on this service is that you can make the configurations from Azure Portal. This is useful especially when you want to try the service or make a fast configuration. From UX perspective, the experience is wonderful and when you want to automate the process, you can use PowerShell.
Base Concepts
Synchronization Group represents a groups of databases that you want to keep in sync. You’ll have all the time a Master database used to sync between all the other databases. In the Master-Slave topology, the Slaves are called Sync Members and the master is called Hub.
For on-premises sync you will need to install an agent on the SQL Server machine. This it is used to sync the local database with Azure ones. There are 3 types of synchronization options that are available:
For each Sync Group we have the ability to specify what are the tables and columns that we want to sync. This allows us to sync only the tables that we want and to ignore the rest of the tables that we have inside the database.
In the case of a conflict, there are 2 policies that you can configure:
There is no support to add any custom trigger or logic behind it. In the case you need something more, than it might be a smell that you are doing something more like an ETL, where Azure Data Factory or a similar solution are much better
Tips and Tricks
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:
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.
SQL Data Sync Tables
Additional to your tables, SQL Data Sync creates it’s own tables that are used to track what data was synchronized, when and what is the delta. You should never delete or modify this tables, otherwise you might block the synchronization process.
Agent Keys
This are keys that are generated inside Azure Portal and used on on-premises agents to authenticate the agent inside Sync Group. Each agent key can be used only for one agent and key invalidation can be done inside the Azure Portal. These keys are not necessary when you use Azure SQL Database, but are required for SQL Server instances that are running inside Azure VMs.
Performance impact
Except the operations that are done by your own system(s) to the database, additional operations like insert, update and delete are done by SQL Data Sync. Once you activate this feature you might need to review the performance requirements of your database and see if you need to change the DTU level.
Why Snapshot isolation needs to be enable?
The unique snapshot of each transaction it is stored inside tempdb and used by SQL Data Sync to track changes on your data. It is required to activate this feature before using SQL Data Sync.
Limitations
There are some limitations from naming and data types that you need to consider:
Conclusion
SQL Data Sync it is one of the features of a cloud provider that can save you from implementing a complex synchronization mechanism that needs to be managed and maintained. It’s a simple and powerful functionality that will make teams happy and save a lot of money on the customer side.
What is SQL Data Sync
It’s a synchronization service offered around Azure platform that is allowing us out-of-the-box to synchronize multiple databases that can be inside Azure or on-premises. SQL Data Sync it is one of the services that makes our lives 10x time easier.
The coolest thing on this service is that you can make the configurations from Azure Portal. This is useful especially when you want to try the service or make a fast configuration. From UX perspective, the experience is wonderful and when you want to automate the process, you can use PowerShell.
Base Concepts
Synchronization Group represents a groups of databases that you want to keep in sync. You’ll have all the time a Master database used to sync between all the other databases. In the Master-Slave topology, the Slaves are called Sync Members and the master is called Hub.
For on-premises sync you will need to install an agent on the SQL Server machine. This it is used to sync the local database with Azure ones. There are 3 types of synchronization options that are available:
- Bi-directional (Sync Member to/from Hub)
- To the Hub (Sync Members only to Hub)
- From the hub (Hub only to Sync Members)
For each Sync Group we have the ability to specify what are the tables and columns that we want to sync. This allows us to sync only the tables that we want and to ignore the rest of the tables that we have inside the database.
In the case of a conflict, there are 2 policies that you can configure:
- Sync Member wins
- Hubs wins
There is no support to add any custom trigger or logic behind it. In the case you need something more, than it might be a smell that you are doing something more like an ETL, where Azure Data Factory or a similar solution are much better
Tips and Tricks
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 happeningbecause 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.
SQL Data Sync Tables
Additional to your tables, SQL Data Sync creates it’s own tables that are used to track what data was synchronized, when and what is the delta. You should never delete or modify this tables, otherwise you might block the synchronization process.
Agent Keys
This are keys that are generated inside Azure Portal and used on on-premises agents to authenticate the agent inside Sync Group. Each agent key can be used only for one agent and key invalidation can be done inside the Azure Portal. These keys are not necessary when you use Azure SQL Database, but are required for SQL Server instances that are running inside Azure VMs.
Performance impact
Except the operations that are done by your own system(s) to the database, additional operations like insert, update and delete are done by SQL Data Sync. Once you activate this feature you might need to review the performance requirements of your database and see if you need to change the DTU level.
Why Snapshot isolation needs to be enable?
The unique snapshot of each transaction it is stored inside tempdb and used by SQL Data Sync to track changes on your data. It is required to activate this feature before using SQL Data Sync.
Limitations
There are some limitations from naming and data types that you need to consider:
- No AD (Active Directory) authentication support
- No identify column that is not primary key can be specified
- Characters like ‘.’ ‘[‘ ‘]’ cannot be used inside table, columns and database names
- Datatypes like TimeStamp, Cursor, Hierarchyid, XMLSchemaCollection and FileStream are not supported
- The maximum size of a row can be 24MB
Conclusion
SQL Data Sync it is one of the features of a cloud provider that can save you from implementing a complex synchronization mechanism that needs to be managed and maintained. It’s a simple and powerful functionality that will make teams happy and save a lot of money on the customer side.
Comments
Post a Comment