Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures

[johnfoll]

I am not sure if I was using DotMim.Sync correctly, but to use the SqlSyncChangeTrackingProvider I had to run some code calling await agent.SynchronizeAsync(SyncType.ReinitializeWithUpload) among other things, which set it up so that I could write a test program that continuously ran and allowed new items to be synced from one database to another. I got this from the example that was using SqlSyncProvider.

I was able to add items and have it work, and maybe update some items and have it work. But I was having some issues with something not replicating correctly, and I clicked delete on that row in the datebase, then it eventually caused my Visual Studio program running the dot mim sync code to blow up - because it was calling some Stored Procedure that was trying to use a _tracking table which didn't exist.

I had set the databases in SQL Server to using Change Tracking and worked through some examples/whatever to make sure change tracking was working. If you look at the Stored Procedures they are definitely using the MS Sql Server change tracking. I read somewhere in your documentation that the SqlSyncChangeTrackingProvider method is not supposed to use the _tracking tables. I had worked through a simple example using SqlSyncProvider which worked for what it did, but did not work properly for having multiple databases being synced to the same database because of the way it was creating tables. So I was trying to get the SqlSynChangeTrackingProvider method to work. It was coming closer to what I needed, but needs to be reworked since the _tracker tables were not there. Calling agent.SynchronizeAsync(SyncType.ReinitializeWithUpload) for sources using SqSynChangeTrackingProvider does not create any _tracking tables or triggers. It creates some stored procedures for each table, but some of them are referencing _tracking tables which do not exist.

I could rework some of these stored procedures if I am given the time to do so for my work project, but am not sure if this is the best course. I would have to understand why it is that since it is working with the Sql Server Change Tracking exactly why it needs the _tracking tables.

What is the correct way to set up the SqlSyncChangeTrackingProvider for first time use between two different databases? Should agent.SynchronizeAsync(SyncType.ReinitializeWithUpload) be called to set things up?

[Mimetis]

When you try different provider, you need to be sure that you have DeprovisionAsync everything before using a new provider.
Don't do that in production.
If it's for test purpose, just test on fresh clean database (without any sync stopred proc / triggers etc ..)

IF you can't call DeprovisionAsync before changing a provider, be sure you have deleted:

• all sync stored proc
• all sync tables triggers
• all tracking tables
• all table value type

Using agent.SynchronizeAsync(SyncType.ReinitializeWithUpload) will download Everything again and again from the server database
It's usefull when you want to reinitialize a client database, not for a daily use.

[johnfoll]

I was using entirely new database with freshly built tables. When I look at the stored procedures that were created, they are using the Sql Server Tracking sql. Also no triggers or tracking tables were created, which is why it failed. Was I supposed to use agent.SynchronizeAsync(SyncType.ReinitializeWithUpload) to setup up the databases for every two way sync pairs? Or agent.SynchronizeAsync(SyncType.Reinitialize)?

[Mimetis]

There is no triggers withe CT.
That's normal.
What is the error?

[johnfoll]

There error was a bunch of Stored Procedures where trying to use _tracking tables. When I tried deleting a row in one of the tables, and ran my sync process it blew up because it couldn't find the tracking table. But if the tracking tables were required, it should have been built when I called agent.SynchronizeAsync(SyncType.ReinitializeWithUpload), but they were not. Seems like a bug, either that or the documentation needs to spell out clearly what can be done and what cannot be done. It should not create a stored procedure to an _tracking table object it didn't create and expect to use it.

[johnfoll]

There were no triggers created in the databases I was using with SqlSyncChangeTrackingProvider. What is different that might be breaking it, is that I have multiple database syncing with one hub server database, and 1 client database is also a hub that can sync to the main hub server database. I have setup multiple syncs running in an async task. These are just for testing purposes, but not that far different than what we will have in production. In production we might possibly several hundred sql server express instances (on different computers) syncing to a central sql server. But for my test I have: 1) Hub Server, 2) Client1, 2) Client2 3) Client3 Grandchild database. We are probably stretching this beyond the original intent of DotMimSync. There are multiple sync connections. Some Bidirectional, some download only and some upload only with a lot of the connections. I am putting DotMimSync through the “wringer” to try to see if it can do what we need. In addition to this, we will have some Client computers that may be offline a lot of the time, and will need to sync up later when they get a connection, or via export/import file sneakernet. So we are trying to build a complex setup. From: Sébastien Pertus [mailto:notifications@github.com] Sent: Tuesday, February 9, 2021 1:55 PM To: Mimetis/Dotmim.Sync <Dotmim.Sync@noreply.github.com> Cc: johnfoll <jrf4health@gmail.com>; Author <author@noreply.github.com> Subject: Re: [Mimetis/Dotmim.Sync] Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures (#434) There is no triggers withe CT. That's normal. What is the error? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#434 (reply in thread)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMIK6RQKTIZ7OPQ6CKOVULS6GHJTANCNFSM4XKGR6MA> . <https://github.com/notifications/beacon/ADMIK6TGSBM2YV2UQOCWWATS6GHJTA5CNFSM4XKGR6MKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAACWQUY.gif>

[johnfoll]

By the way, thanks for your prompt responses, Sebastien. From: Sébastien Pertus [mailto:notifications@github.com] Sent: Tuesday, February 9, 2021 1:55 PM To: Mimetis/Dotmim.Sync <Dotmim.Sync@noreply.github.com> Cc: johnfoll <jrf4health@gmail.com>; Author <author@noreply.github.com> Subject: Re: [Mimetis/Dotmim.Sync] Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures (#434) There is no triggers withe CT. That's normal. What is the error? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#434 (reply in thread)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMIK6RQKTIZ7OPQ6CKOVULS6GHJTANCNFSM4XKGR6MA> . <https://github.com/notifications/beacon/ADMIK6TGSBM2YV2UQOCWWATS6GHJTA5CNFSM4XKGR6MKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAACWQUY.gif>

[johnfoll]

I had two separate database that had triggers in them and were using sqlsyncprovider, but these are not part of what I was needing help with – they are different database. From: Sébastien Pertus [mailto:notifications@github.com] Sent: Tuesday, February 9, 2021 1:55 PM To: Mimetis/Dotmim.Sync <Dotmim.Sync@noreply.github.com> Cc: johnfoll <jrf4health@gmail.com>; Author <author@noreply.github.com> Subject: Re: [Mimetis/Dotmim.Sync] Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures (#434) There is no triggers withe CT. That's normal. What is the error? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#434 (reply in thread)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMIK6RQKTIZ7OPQ6CKOVULS6GHJTANCNFSM4XKGR6MA> . <https://github.com/notifications/beacon/ADMIK6TGSBM2YV2UQOCWWATS6GHJTA5CNFSM4XKGR6MKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAACWQUY.gif>

[Mimetis]

So, do you still need some help ?

[johnfoll]

I am trying to work my way through it. I don’t know DotMim.Sync can work correctly for what we need, or if it only works in simple sync scenarios. From: Sébastien Pertus [mailto:notifications@github.com] Sent: Tuesday, February 9, 2021 2:20 PM To: Mimetis/Dotmim.Sync <Dotmim.Sync@noreply.github.com> Cc: johnfoll <jrf4health@gmail.com>; Author <author@noreply.github.com> Subject: Re: [Mimetis/Dotmim.Sync] Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures (#434) So, do you still need some help ? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#434 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMIK6WT6SBA3MTSBZSCTCDS6GKH3ANCNFSM4XKGR6MA> . <https://github.com/notifications/beacon/ADMIK6UETKQX2U5NQRCIJTDS6GKH3A5CNFSM4XKGR6MKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAACWRFA.gif>

[johnfoll]

I cleared out all of the procedures and scope_info* tables in two databases, there were no triggers of any kind and ran this code below for a table called Repl2Way in both databases and the code below created 9 stored procedures: dbo.Repl2Way_bulkdelete contains a join to Repl2Way_tracking, which doesn't exist.
DotMimSync had been installed into the Visual Studio project with: Install-Package Dotmim.Sync.SqlServer -Version 0.5.7
Here is the actual version of DotMimSync used:
< package id="Dotmim.Sync.Core" version="0.6.0" targetFramework="net48" / >
< package id="Dotmim.Sync.SqlServer" version="0.6.0" targetFramework="net48" / >
< package id="Dotmim.Sync.SqlServer.ChangeTracking" version="0.6.0" targetFramework="net48" / >

Code Fragments:

class Program
{
    public static string GetDatabaseConnectionString(string dbName) =>
        $"Data Source=(localdb)\\mssqllocaldb; Initial Catalog={dbName}; Integrated Security=true;";

    static async Task Main(string[] args)
    {
        DbSyncTest syncTest = new DbSyncTest();
        await syncTest.SynchronizeAsyncWithReinitializeWithUploadChangeTracking_C2_IC_Repl2Way();
    }

}

Separate class file:
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics.CodeAnalysis;
using System.Threading.Tasks;
using Dotmim.Sync;
using Dotmim.Sync.Enumerations;
using Dotmim.Sync.SqlServer;
using MclDbSyncLib.SyncLib;

namespace DbSyncTestFX4_8
{
public class DbSyncTest
{
string testC2ServerHubConStr = @"Data Source=(localdb)\MSSQLLocalDB;Database=C2ServerHub;Integrated Security=True;";
string testICClient1ConStr = @"Data Source=(localdb)\MSSQLLocalDB;Database=ICClient1;Integrated Security=True;";

   public async Task SynchronizeAsyncWithReinitializeWithUploadChangeTracking_C2_IC_Repl2Way()
   {
        //Please note: This method for some unknown reason will not upload the data from the Client to the server,
        //even though the direction is upload! Why? Maybe because it is the client? 
        //I don't think we want both database to be the client and the server hub! That probably wouldn't work!
        SqlSyncChangeTrackingProvider hubProvider = new SqlSyncChangeTrackingProvider(testC2ServerHubConStr);
        SqlSyncChangeTrackingProvider client1Provider = new SqlSyncChangeTrackingProvider(testICClient1ConStr);
        SyncOptions opt = new SyncOptions();
        opt.CleanFolder = true;
        opt.DisableConstraintsOnApplyChanges = false;
        var syncSetup = new SyncSetup(new string[] { "Repl2Way" });

        foreach (var tbl in syncSetup.Tables)
        {
            tbl.SyncDirection = SyncDirection.Bidirectional;
        }

        SyncAgent syncAgent = new SyncAgent(client1Provider, hubProvider, opt, syncSetup, "C2_IC_Repl2Way");
        System.Console.WriteLine(await syncAgent.SynchronizeAsync(SyncType.Reinitialize));
    }

}
}

[Mimetis]

or if it only works in simple sync scenarios

LOL, for sure, it does not work with any sync scenario. This framework is all about making some good old random Console Output :)

Seriously, you should probably use another framework to fit your needs (and be a little bit more grateful et respectful, my 2 cents)

I guess you can take a look at Sql Server Replication or even some softwares like DbConvert, Sync Framework or SymmetricDS.

Or maybe do your own sync framework and share with people. WOULD LOVE to do some constructive feedbacks on it !

Bye,

Console Output Slave Maintainer

[johnfoll]

I see something that is going on. Rel2Way is actually a cte, not a real table.

ALTER PROCEDURE [dbo].[Repl2Way_bulkdelete]
@sync_min_timestamp bigint,
@sync_scope_id uniqueidentifier,
@changeTable [dbo].[Repl2Way_BulkType] READONLY
AS
BEGIN
-- use a temp table to store the list of PKs that successfully got deleted
declare @dms_changed TABLE ([ID] [uniqueidentifier], PRIMARY KEY ( [ID]));

DECLARE @var_sync_scope_id varbinary(128) = cast(@sync_scope_id as varbinary(128));

;WITH
CHANGE_TRACKING_CONTEXT(@var_sync_scope_id),
[Repl2Way_tracking] AS (
SELECT [p].[ID],
CAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id],
[CT].[SYS_CHANGE_VERSION] as [timestamp],
CASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]
FROM @changeTable AS [p]
LEFT JOIN CHANGETABLE(CHANGES [Repl2Way], @sync_min_timestamp) AS [CT] ON [p].[ID] = [CT].[ID]
)
DELETE [Repl2Way]
OUTPUT DELETED.[ID]
INTO @dms_changed
FROM [Repl2Way] [base]
JOIN [Repl2Way_tracking] [changes] ON [changes].[ID] = [base].[ID]
WHERE [changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL OR [changes].[update_scope_id] = @sync_scope_id;

[johnfoll]

I was able to get multiple sync scenarios done with DotMimSync. I needed to be careful with the combinations of sync pairs to keep it working. I can do upload sync to multiple sync pair and even one case where the client of one sync pair is the server hub of another sync pair. I think the problem might be with the snapshot isoloation, after reading up on Sql Server Change Tracking. I notice that this can be set in the Visual Studio Database project. I may need to play around with that at some point. I can use DotMimSync for the majority of my majority of DB syncing I think. I don’t have a full topology of all of the computers that need to be synced and the tables and the sync direction yet – our project is still in early development. I have been learning a bit about the Sql Server Change Tracking to get better at it too. I think DotMim.Sync is a good system and is a good choice for many projects needs. I think we will probably use it. Thanks for all your hard work! I am sorry if my comments were viewed as being too negative. Cheers! From: Sébastien Pertus [mailto:notifications@github.com] Sent: Tuesday, February 9, 2021 5:07 PM To: Mimetis/Dotmim.Sync <Dotmim.Sync@noreply.github.com> Cc: johnfoll <jrf4health@gmail.com>; Author <author@noreply.github.com> Subject: Re: [Mimetis/Dotmim.Sync] Version 0.5.7 SqlSyncChangeTrackingProvider Bug Using _tracking tables in Stored Procedures (#434) or if it only works in simple sync scenarios LOL, for sure, it does not work with any sync scenario. This framework is all about making some good old random Console Output :) Seriously, you should probably use another framework to fit your needs (and be a little bit more grateful et respectful, my 2 cents) I guess you can take a look at Sql Server Replication <https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication> or even some softwares like DbConvert <https://dbconvert.com/> , Sync Framework <https://www.codemag.com/Article/1907101/Synchronizing-Databases-Using-Microsoft-Sync-Framework> or SymmetricDS <https://www.symmetricds.org/docs/overview> . Or maybe do your own sync framework and share with people. WOULD LOVE to do some constructive feedbacks on it ! Bye, Console Output Slave Maintainer — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#434 (reply in thread)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMIK6XP4SUQJ6ZTRVPJ7ZLS6G5ZFANCNFSM4XKGR6MA> . <https://github.com/notifications/beacon/ADMIK6RCP54TB45FKLVI7TDS6G5ZFA5CNFSM4XKGR6MKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAACWUEQ.gif>

[Mimetis]

one case where the client of one sync pair is the server hub of another sync pair.

Be careful, this is not supported