Skip to main content

Sync Group - A good solution to synchronize SQL Databases using Windows Azure infrastructure

By
Working with Windows Azure becomes something that is more and more pleasant. In this post we will see how you can synchronize multiple databases hosted on Azure or on premise using Sync Group.
First of all, let’s start with the following requirement: We have an application that contains a database that needs to be replicated in each datacenter. What should we do to replicate all the content in all the datacenters?
A good response could be Sync Group (SDS). Using this feature we can define one or more instances of SQL Databases (from cloud or on premise) that will be synchronized. For this group we can specify the tables and columns that will be synchronized.
Creating a SDS can be made very easily from Windows Azure portal. This feature can be found under the SQL DATABASES tab – SYNC. I will not start to explain how you can create this group, because it is very easily, you only need to know the databases server address, user names and passwords.
I think that more important than this is the options that we have available on a SDS.

HUB
One of the databases that forms the group needs to be the hub. The hub represent the master node of the group, from where all the data propagates.
Synchronization Direction
Once we add the hub, we can add more databases to the group. In this moment we will need to specify the synchronization direction. In this moment we have 3 options:

  • From the Hub – All the changes that are made in the hub are replicated to the rest of the databases from the group. In this configuration, when data are different, the hub will win. Changes in the databases are not written to the hub
  • To the Hub – Al changes from the hub are not written in the databases. All changes that are made in the databases are written in the hub
  • Bi-directional – The synchronization is made in both ways – from Hub to databases and from databases to hub


Synchronization Rules
From the portal, we have the option to select the tables (and columns) from the hub table that will be synchronized. In this way you don’t need to have databases that has the same schema. The most important thing is to have the tables that you want to synchronize in the same schema/format.
Remarks: We don’t need to replicate the database schema to all the databases. Once we select the tables and columns that we want to synchronize (from Hub schema), all this tables will be replicated in the rest of the group.

Conflict Resolution Policies
When we are creating a hub, we have two option for conflict resolution.

  • Hubs Wins – In this case all the changes that are written to the hub will be persisted and in a case of the conflict, the version that is on the hub will be the ‘good one’
  • Client Wins – In tis case the changes that are written on the slaves (non-hub database) will win and the change from the slave will propagate to the hub and to the rest of the group.

For more information about this conflict resolution policies I recommend to search on MSDN.

Synchronization Frequency
The synchronization between the databases of a group is not made in real time. We can select the time interval when the synchronization needs to be made. This time interval can be between 5 minutes and 1 month. Also, we have a button that can trigger the synchronization action.

On-premise SQL Server
To be able to use this feature on SQL Server you will need to download and install SQL Data Sync. This is a tool that will integrate this functionality on SQL Server.

Logs
All the synchronization action that are between group nodes are logged. Using this information we can determine how long the synchronization action took, what nodes were synchronized and how the action ended.

I think that this feature has a lot of potential. Why? Because database synchronization can be made very easily now. This feature can really add value to your application with minimal costs and headaches.
Labels:

Comments

  1. boldicuNovember 15, 2013 at 11:41 PM

    Did you tried this solution for non trivial databases? Last year I tried to sync 2 GB database and it was a never ending story. Maybe MS improved the performances in the meantime.

    ReplyDelete
  2. Radu VunvuleaNovember 16, 2013 at 8:33 AM

    The test were made with a 10GB database, using From Hub settings.. If you want I can share the performance results for it.

    ReplyDelete
  3. boldicuNovember 18, 2013 at 10:01 AM

    Yes, I would like to see the performance results. I remember the provisioning phase failed for me last year but I think sync framework was in beta at that time.

    ReplyDelete
    Replies
    1. Radu VunvuleaNovember 19, 2013 at 3:45 AM

      In the next 1-2 weeks I will come with a blog post related to this.

      Delete
    2. Radu VunvuleaNovember 20, 2013 at 3:26 AM

      Performance results: http://vunvulearadu.blogspot.com/2013/11/sync-group-lets-talk-about-performance.html

      Delete

Post a Comment

Popular posts from this blog

Windows Docker Containers can make WIN32 API calls, use COM and ASP.NET WebForms

By
After the last post , I received two interesting questions related to Docker and Windows. People were interested if we do Win32 API calls from a Docker container and if there is support for COM. WIN32 Support To test calls to WIN32 API, let’s try to populate SYSTEM_INFO class. [StructLayout(LayoutKind.Sequential)] public struct SYSTEM_INFO { public uint dwOemId; public uint dwPageSize; public uint lpMinimumApplicationAddress; public uint lpMaximumApplicationAddress; public uint dwActiveProcessorMask; public uint dwNumberOfProcessors; public uint dwProcessorType; public uint dwAllocationGranularity; public uint dwProcessorLevel; public uint dwProcessorRevision; } ... [DllImport("kernel32")] static extern void GetSystemInfo(ref SYSTEM_INFO pSI); ... SYSTEM_INFO pSI = new SYSTEM_INFO(...
Post a Comment
Read more

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

By
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.51 EF 6.0.2 VS2013 It see...
8 comments
Read more

Navigating Cloud Strategy after Azure Central US Region Outage

By
 Looking back, July 19, 2024, was challenging for customers using Microsoft Azure or Windows machines. Two major outages affected customers using CrowdStrike Falcon or Microsoft Azure computation resources in the Central US. These two outages affected many people and put many businesses on pause for a few hours or even days. The overlap of these two issues was a nightmare for travellers. In addition to blue screens in the airport terminals, they could not get additional information from the airport website, airline personnel, or the support line because they were affected by the outage in the Central US region or the CrowdStrike outage.   But what happened in reality? A faulty CrowdStrike update affected Windows computers globally, from airports and healthcare to small businesses, affecting over 8.5m computers. Even if the Falson Sensor software defect was identified and a fix deployed shortly after, the recovery took longer. In parallel with CrowdStrike, Microsoft provi...
Post a Comment
Read more