Skip to main content

Azure SQL Data Sync | Tips and Tricks

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:

  1. Bi-directional (Sync Member to/from Hub)
  2. To the Hub (Sync Members only to Hub)
  3. 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 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.

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.

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

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.


Popular posts from this blog

How to check in AngularJS if a service was register or not

There are cases when you need to check in a service or a controller was register in AngularJS.
For example a valid use case is when you have the same implementation running on multiple application. In this case, you may want to intercept the HTTP provider and add a custom step there. This step don’t needs to run on all the application, only in the one where the service exist and register.
A solution for this case would be to have a flag in the configuration that specify this. In the core you would have an IF that would check the value of this flag.
Another solution is to check if a specific service was register in AngularJS or not. If the service was register that you would execute your own logic.
To check if a service was register or not in AngularJS container you need to call the ‘has’ method of ‘inhector’. It will return TRUE if the service was register.
if ($injector.has('httpInterceptorService')) { $httpProvider.interceptors.push('httpInterceptorService&#…

ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded

Today blog post will be started with the following error when running DB tests on the CI machine:
threw exception: System.InvalidOperationException: The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See for more information. at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName) This error happened only on the Continuous Integration machine. On the devs machines, everything has fine. The classic problem – on my machine it’s working. The CI has the following configuration:

TeamCity.NET 4.51EF 6.0.2VS2013
It seems that there …

Entity Framework (EF) TransactionScope vs Database.BeginTransaction

In today blog post we will talk a little about a new feature that is available on EF6+ related to Transactions.
Until now, when we had to use transaction we used ‘TransactionScope’. It works great and I would say that is something that is now in our blood.
using (var scope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlConnection conn = new SqlConnection("...")) { conn.Open(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = conn; sqlCommand.CommandText = ... sqlCommand.ExecuteNonQuery(); ... } scope.Complete(); } Starting with EF6.0 we have a new way to work with transactions. The new approach is based on Database.BeginTransaction(), Database.Rollback(), Database.Commit(). Yes, no more TransactionScope.
In the followi…