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.

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

Popular posts from this blog

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 http://go.microsoft.com/fwlink/?LinkId=260882 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 …

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&#…

Fundamental Books of a Software Engineer (version 2018)

More then six years ago I wrote a blog post about fundamental books that any software engineer (developer) should read. Now it is an excellent time to update this list with new entries.

There are 5 different categories of books, that represent the recommended path. For example, you start with Coding books, after that, you read books about Programming, Design and so on.
There are some books about C++ that I recommend not because you shall know C++, only because the concepts that you can learn from it.

Coding

Writing solid codeCode completeProgramming Pearls, more programming pearls(recommended)[NEW] Introduction to Algorithms

Programming

Refactoring (M. Fowler)Pragmatic ProgrammerClean code[NEW] Software Engineering: A Practitioner's Approach[NEW] The Mythical Man-Month[NEW] The Art of Computer Programming

Design

Applying UML and Patterns (GRASP patterns)C++ coding standards (Sutter, Alexandrescu)The C++ programming language (Stroustrup, Part IV)Object-oriented programming (Peter Coad)P…