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

How to audit an Azure Cosmos DB

By
In this post, we will talk about how we can audit an Azure Cosmos DB database. Before jumping into the problem let us define the business requirement: As an Administrator I want to be able to audit all changes that were done to specific collection inside my Azure Cosmos DB. The requirement is simple, but can be a little tricky to implement fully. First of all when you are using Azure Cosmos DB or any other storage solution there are 99% odds that you’ll have more than one system that writes data to it. This means that you have or not have control on the systems that are doing any create/update/delete operations. Solution 1: Diagnostic Logs Cosmos DB allows us activate diagnostics logs and stream the output a storage account for achieving to other systems like Event Hub or Log Analytics. This would allow us to have information related to who, when, what, response code and how the access operation to our Cosmos DB was done. Beside this there is a field that specifies what was th...
Post a Comment
Read more

Cloud Myths: Cloud is Cheaper (Pill 1 of 5 / Cloud Pills)

By
Cloud Myths: Cloud is Cheaper (Pill 1 of 5 / Cloud Pills) The idea that moving to the cloud reduces the costs is a common misconception. The cloud infrastructure provides flexibility, scalability, and better CAPEX, but it does not guarantee lower costs without proper optimisation and management of the cloud services and infrastructure. Idle and unused resources, overprovisioning, oversize databases, and unnecessary data transfer can increase running costs. The regional pricing mode, multi-cloud complexity, and cost variety add extra complexity to the cost function. Cloud adoption without a cost governance strategy can result in unexpected expenses. Improper usage, combined with a pay-as-you-go model, can result in a nightmare for business stakeholders who cannot track and manage the monthly costs. Cloud-native services such as AI services, managed databases, and analytics platforms are powerful, provide out-of-the-shelve capabilities, and increase business agility and innovation. H...
Post a Comment
Read more