Overview¶
For simplicity, we can say DMS framework.
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:
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:
- For SQL Server : AdventureWorks for SQL Server
- For MySQL : AdventureWorks for MySQL
- 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.Synccould be a little bit intrusive if you’re not using theSqlSyncChangeTrackingProviderprovider :) - 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 ?