Skip to main content

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

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.

Comments

  1. 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. 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. 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. In the next 1-2 weeks I will come with a blog post related to this.

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

      Delete

Post a Comment

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

Run native .NET application in Docker (.NET Framework 4.6.2)

Scope
The main scope of this post is to see how we can run a legacy application written in .NET Framework in Docker.

Context
First of all, let’s define what is a legacy application in our context. By a legacy application we understand an application that runs .NET Framework 3.5 or higher in a production environment where we don’t have any more the people or documentation that would help us to understand what is happening behind the scene.
In this scenarios, you might want to migrate the current solution from a standard environment to Docker. There are many advantages for such a migration, like:

Continuous DeploymentTestingIsolationSecurity at container levelVersioning ControlEnvironment Standardization
Until now, we didn’t had the possibility to run a .NET application in Docker. With .NET Core, there was support for .NET Core in Docker, but migration from a full .NET framework to .NET Core can be costly and even impossible. Not only because of lack of features, but also because once you…