Overview

_images/Smallicon.png
Dotmim.Sync (DMS) is the easiest way to handle a full synchronization between one server database and multiples clients databases.
Dotmim.Sync is cross-platforms, multi-databases and based on .Net Standard 2.0.
Choose either SQL Server, SQLite, MySQL, MariaDB and (hopefully, I hope soon…) Oracle or PostgreSQL !

For simplicity, we can say DMS framework.

No need to handle any configuration file, or code generation code or whatever.
Just a few lines of code, with the list of tables you want to synchronize then call SynchronizeAsync() and you’re done !

Nuget packages

Basically, DMS is working with sync database providers, that are available through nuget, from the Visual Studio interface:

packages

Obviously, you can add them through your command line, assuming you are developing with Visual Studio Code, Rider or even Notepad :)

# Adding the package required to synchronize a SQL Server database:
dotnet add package Dotmim.Sync.SqlServer
# Adding the package required to synchronize a SQL Server database, using Change Tracking feature:
dotnet add package Dotmim.Sync.SqlServer.ChangeTracking
# Adding the package required to synchronize a MySQL database:
dotnet add package Dotmim.Sync.MySql
# Adding the package required to synchronize a MariaDB database:
dotnet add package Dotmim.Sync.MariaDB
# Adding the package required to synchronize a SQLite database:
dotnet add package Dotmim.Sync.Sqlite

For instance, if you need to synchronize two MySql databases, the only package you need to install, on both Server and Client side, is Dotmim.Sync.MySql.

On the other side, if you need to synchronize a SQL server database, with multiple SQLite client databases, install Dotmim.Sync.SqlServer (or Dotmim.Sync.SqlServer.ChangeTracking) on the server side and then install Dotmim.Sync.Sqlite on each client.

Note

The package Dotmim.Sync.Core is the core framework, and is used by all the providers. You don’t have to explicitely add it to your projects, since it’s always part of the provider you’ve just installed.

The last two packages available, Dotmim.Sync.Web.Client and Dotmim.Sync.Web.Server are used for a specific scenario, where you server database is not accessible directly, but instead is available and exposed through a Web Api, built with ASP.Net Core or ASP.NET.

All packages are available through nuget.org:

Tutorial: First sync

First sync

This tutorial will describe all the steps required to create a first sync between two relational databases:

  • If you don’t have any databases ready for testing, you can use:
  • The script is ready to execute in SQL Server (or MySQL Workbench). It contains :
    • A lightweight AdvenureWorks database, acting as the Server database (called AdventureWorks)
    • An empty database, acting as the Client database (called Client)

Hint

You will find this sample here : HelloSync sample

You can see this sample as well, live, hosted on dotnetfiddle : Hello Sync On dotnetfiddle

Warning

In the code sample below, we are using a special provider called SqlSyncChangeTrackingProvider. This provider is using the CHANGE_TRACKING feature from SQL SERVER.

Before running this code, use this SQL statement on your server database to enable the Change Tracking:

ALTER DATABASE AdventureWorks SET CHANGE_TRACKING=ON
(CHANGE_RETENTION=2 DAYS,AUTO_CLEANUP=ON)

Otherwise, if you don’t want to use the Change Tracking feature, just change SqlSyncChangeTrackingProvider to SqlSyncProvider

// First provider on the server side, is using the Sql change tracking feature.
var serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);

// IF you want to try with a MySql Database, use the [MySqlSyncProvider] instead
// var serverProvider = new MySqlSyncProvider(serverConnectionString);

// Second provider on the client side, is the [SqliteSyncProvider] used for SQLite databases
// relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqliteSyncProvider(clientConnectionString);

// Tables involved in the sync process:
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product",
    "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" );

// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);

do
{
    // Launch the sync process
    var s1 = await agent.SynchronizeAsync(setup);
    // Write results
    Console.WriteLine(s1);

} while (Console.ReadKey().Key != ConsoleKey.Escape);

Console.WriteLine("End");

And here is the result you should have, after a few seconds:

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 3514
        Total changes  applied on client: 3514
        Total changes  applied on server: 0
        Total changes  failed to apply on client: 0
        Total changes  failed to apply on server: 0
        Total resolved conflicts: 0
        Total duration :00.00:02.125

It took 2 seconds on my machine to make a full synchronization between the Server and the Client.

Second sync

This first sample took 2 seconds to make a full sync between a Server and a Client.

It’s a little bit long (I’m kidding… no), because, under the hood, the Dotmim.Sync framework, on the first sync only, will have to:

  • Get the schema from the Server side and create all the tables on the Client side, if needed. (yes, you don’t need a client database with an existing schema)
  • Create on both side all the required stuff to be able to manage a full sync process, creating tracking tables, stored procedures, triggers and so on … be careful, Dotmim.Sync could be a little bit intrusive if you’re not using the SqlSyncChangeTrackingProvider provider :)
  • Then eventually launch the first sync, and get the 2752 items from the Server, and apply them on the Client.

Now everything is configured and the first sync is successfull.

We can add 101 items in the ProductCategory table (on the server side, Adventureworks):

Insert into ProductCategory (ProductCategoryID, Name)
Select newid(), SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7)
Go 100

From the same console application (indeed, we have a do while loop), same code, just hit enter to relaunch the synchronization and see the results:

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 100
        Total changes  applied on client: 100
        Total changes  applied on server: 0
        Total changes  failed to apply on client: 0
        Total changes  failed to apply on server: 0
        Total resolved conflicts: 0
        Total duration :00.00:00.059

Boom, it’s …. fast, isn’t it ?