Wrapping entire Sync in a single transaction

[Tlocke1]

Hello!

We would like to guard against connectivity issues that vary per user situation.
Would it be possible to wrap an entire sync session in a single transaction such that if anything happens between syncing the first table and the last table, no records would make it to the server?
(Essentially an all or nothing scenario.)

Warm Regards

[VagueGit]

Great idea. Any thoughts on how this would work with batches? As I understand DMS, a large number of changes may be batched into separate json files which are individually posted to the server via https.

If a single upsert in a batch fails, the batch fails. This doesn't seem to cause other batches to fail. However subsequent attempts to sync seem to retry batches that have already failed. Your suggestion would cause subsequent batches to fail, as well as rolling back previous successful batches. If my understanding is correct, DMS would then retry all those batches next sync. This could cause an app to effectively hang. I may be mistaken.

This suggestion would require all batches to be included in a single database transaction on the server. Is that the idea?

I wonder about the impact on the web server and the database server. Say for example a product catalog is rolled out to thousands of users. DMS can feed those updates to the server a batch at a time, lowering overhead on the servers. How would that scenario be handled?

We're experimenting with DMS and run those big updates inside a transaction on a local db before syncing to the server. This means the all-or-none has already been dealt with.

It may be that a large update that succeeds on the server, fails on one of more clients. Should that sync down be transactional?

Maybe transactional sync should be optional and directional? Lots of thinking to do here and maybe lots of work by better developers than me 😄

[Tlocke1]

Hi @Mimetis, would it be possible to create a context for the remote server, begin a transaction, and then hand off the context to DMS to sync and if there are errors, rollback the remote server's initial transaction?

@VagueGit, interesting ideas. I'm not sure how that would be handled.

[Mimetis]

Hello @Tlocke1

Basically, everything is transactional, even if we don't have one single transaction, since we have several participants in a sync.
We can count basically 4 transactions in the complete workflow. 2 on clients (reading then applying) and 2 on server (applying then reading)

2 transactions are basically just reading data. No data are modified / inserted/ deleted
2 transactions are for applying changes on server or client

Here is a simplified workflow, focusing only on the sync mechanism (I will not detail here everything around provisioning or dedicated methods):

1. Client gets changes to send to server, in one transaction.
2. Server applies client changes, and resolve any conflicts in one transaction
3. Server get server changes + conflicts resolved, in one transaction
4. Client applies changes from server in one transaction.

Now what happened in different situation:

• During Phase 1 : Nothing complicated here, the next sync will work correctly.
• During Phase 2 : Server failed to apply client changes. Since nothing is sent back to the client, client will not have the confirmation, and then will not commit anything. Next sync will restart from beginning
• During Phase 3 : Server failed to get changes, but has applied clients changes. client has no confirmation of the changes applied on server. On next sync, client will resend the same changes. Server will ignore them or will raise conflicts, that will be automatically resolved on client
• ** During Phase 4**: Client failed to apply server changes. Like Phase 3, Client will send back the last changes, and will get back correct changes from server

Regarding these different scenario, you should not have any "un-synchronized" situation due to a failed transaction.

Make sense ?

[Tlocke1]

Hello @Mimetis ,

Thank you for your reply. I'm testing through these scenarios and will give you some feedback.

Quick question, My test servers are on a private network and not using Active Directory.
In this scenario:
serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);

Is it possible to get serverProvider to honor the following connection string?
"Server=SYSTEMXYZ;Database=DMS_TEST;User Id=SomeUser;Password=SomePassword;"

Currently it throws an exception:
Dotmim.Sync.SyncException: 'Cannot open database "DMS_TEST" requested by the login. The login failed.
Login failed for user 'SomeDomain\SomeUser'.'

This is not how it will be used in production. ";trusted_connection=true;Integrated Security=true;" works fine in that scenario.

So it looks like it is ignoring the connection string User Id and Password fields and using AD anyway.
adding ";trusted_connection=false;Integrated Security=false;" to the connection string did not help.
Any ideas?

Warm Regards

[bibo4]

I was able to get it working using SQL authentication, my config was something like:
"SqlConnection": "Server=mylaptop\SQLEXPRESS;User Id=SomeUser;Password=SomePassword;Database=SomeDatabase;"

I would suggest testing the credentials using some third party tool like SQL Server Management Studio. The 'cannot open database' message makes it sound as if the user account was able to logon to the server, but does not have permission to the specific database on the server

[Mimetis]

Both authentication system are working, actually.

Your are using an old version of connection string. My advice is to use this kind of format:

• Sql Auth: "Data Source=Host\SqlExpress;Initial Catalog=SomeDb;User Id=SomeUser;Password=SomePassword;"
• Windows Auth : "Data Source=Host\SqlExpress;Initial Catalog=SomeDb;Integrated Security=true;"

[Tlocke1]

Hello @Mimetis,

Thanks for the help, that all worked great.
I'm knee deep in DMS testing and its going well. :-)

Thought I'd share one strange discovery, though it may be a little esoteric in nature.

We are using SQL server change tracking on the main server and the LocalDB client side.
I came across an example of "marking" changes that you make to the data.
Look at this example I ran on the client:

DECLARE @originator_id varbinary(128);
SET @originator_id = CAST('SpecialUpdate' AS varbinary(128));

WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE Invoices
SET Comments = 'Testing'
WHERE CustomerID BETWEEN 2 AND 30;

This did put in the word Testing in the Comments column for about 3k records.
You can see the results this way:
SELECT
inv.InvoiceID, inv.Comments, inv.LastEditedWhen,
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_OPERATION,
CAST(c.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES Invoices, 0) AS c
INNER JOIN Invoices AS inv ON inv.InvoiceID = c.InvoiceID

The odd thing is, DMS did not pick up any changes.
If I simply run the following:
UPDATE Invoices
SET Comments = 'Testing'
WHERE CustomerID BETWEEN 2 AND 30;

It works as expected and DMS picks up the changes.
Thought you should know, but not sure if this is where I should have let you know :-)

Warm regards

[Mimetis]

Indeed, DMS is using the SYS_CHANGE_CONTEXT value to store the client / server GUID who updated the value for the last time.
I don't want to go to deep in the details but let's say that:

Each time DMS check for rows to send from client to server, it checks if this value (SYS_CHANGE_CONTEXT) is empty.
If not, that means the rows has been updated by someone else, and client never touched it.
So far, no need to upload the row.

In your case, you are storing a value in the SYS_CHANGE_CONTEXT. That means (from DMS perspective) that it does not need to treat this row.

My advice: If you are using DMS with change tracking, dont use SYS_CHANGE_CONTEXT.