Selectively ignore some client changes?

[grandall-hb]

We have a multi-tenant database on SQL Server. We have some mostly-static tables in the master that occasionally get synced "download_only" to the tenants. This works fine. However, some tenants require customizations that necessitate changes to the tenant tables. Rearchitecting the tables to accommodate such customizations isn't in the cards right now. So I'd like to add a SyncClientOverride column to the tables, which prevents a row in the tenant from being overwritten by the master if it's set to 1.

I added a custom parameter to my setup with a Where clause that was essentially (SyncClientOverride = 0 OR [side].sync_row_is_tombstone = 1 But that didn't work because SyncClientOverride is always 0 on the server side.

I also tried it as an conflict interceptor, but interceptors aren't called on download_only tables because there are no conflicts.

So how can I implement this?

[Mimetis]

I'm not sure I understand well. What I understood so far:

• Static tables on client are download only
• You've added a new column in these tables called SyncClientOverride
• if SyncClientOverride is set to 1 (on the client ?) then it should never being overriden by any new row version changed coming from the server

If I'm right, here are my thoughts:

• Did you Deprovision and Provision again your sync setup to be sure you are handling this new column ? It's important to be sure all the stored procedures are regenerated to handle this new column. Check the _changes, _bulkupdate (and so on ...) stored procedures to be sure your new column is part of the sync
• Adding a filter using CustomWhere (as you did) can't work because you are filtering both server and client side. That's why you have only rows with a value of 0, that are downloaded to the client, when getting changes from the server. Also notice that CustomWhere is used when selecting rows, not applying rows.

That being said, the conflict should still work.

I've made a test using AdventureWorks and the ProductCategory table, where I'm using the IsActive boolean column, in the same way as your SyncClientOverride:

agent.LocalOrchestrator.OnApplyChangesConflictOccured(async args =>
{
    var conflict = await args.GetSyncConflictAsync();
    Console.WriteLine("Conflict on client for row " + conflict.LocalRow );

    // if IsActive on client is set to false, then server wins and will override client row
    // if IsActive on client is set to true, client will win and will not be overriden
    if (conflict.LocalRow["IsActive"] != null 
        && bool.Parse(conflict.LocalRow["IsActive"].ToString()) == false)
        args.Resolution = ConflictResolution.ServerWins;
    else
        args.Resolution = ConflictResolution.ClientWins;
});

And it's working, if I've understand well your scenario. Let me know

[grandall-hb]

That's not what I'm seeing. Even when I modify your Interceptor to return ClientWins in all cases, the server overwrites the client data.

[grandall-hb]

Ok, I'm almost there, but I'm seeing the exact opposite behavior. When the interceptor returns ClientWins, then the server overwrites the client. And when the interceptor returns ServerWins, The client row is untouched.

So I've got two tables with schema id, name, SyncClientOverride.

The server table has three rows: 4,ServerWins,0 5,ServerWins,0 6,ServerWins,0

The client table has: 4,ClientWins,1 5,ClientWins,0 6,ClientWins,1

When I run the sync, the interceptor prints to the console: ClientWins ServerWins ClientWins

But the client database has: 4,ServerWins,0 5,ClientWins,0 6,ServerWins,0

If I switch the logic in the interceptor so that it returns ClientWins if SyncClientOverride is false, then on the console I get: ServerWins ClientWins ServerWins

But in the database, I get 4,ClientWins,1 5,ServerWins,0 6,ClientWins,1

That's exactly what I want, but the logic is backwards. Or am I missing something?

[grandall-hb]

I should add that if the interceptor always returns ClientWins, then the server overwrites the client. And if the interceptor returns ServerWins, then the client's rows are left alone.

[Mimetis]

I need a repro sample to see what's you are doing wrong here

[grandall-hb]

So in SyncAgent.cs I see this line:

            // Policy is always Server policy, so reverse this policy to get the client policy
            var reverseConflictResolutionPolicy = serverResolutionPolicy == ConflictResolutionPolicy.ServerWins ? ConflictResolutionPolicy.ClientWins : ConflictResolutionPolicy.ServerWins;

This the issue that on the client, the conflict resolution is reversed? Because this seems to be the behavior.

[Mimetis]

In some circumstances, yes, but it's not your problem, I guess. Create a very sample repro I can use to reproduce your behavior and I will be able to help