SQL Server change tracking with readonly connection to server and one-way sync to clients.

[phrrngtn]

Once a first scope sync has been done, you will have, on both sides, a scope_info_client table, containing:

I would like a setup for SqlSyncChangeTrackingProvider for one-way synchronization between server and multiple clients where dotmimsync is run with a read-only login on the server side and all state and context is maintained by the client. Assume that change-tracking is set up already on the server and the client will set @last_synchronization_version based on what it reads from the local replica. Absolutely no client state should be written to the server. All applications (with the exception of the synchronization process) accessing the client replica will do so read-only.

I have done some experimentation but it seems like the stored procedures and scope_info and scope_info_client tables get created on the server. I am not sure if I am misunderstanding the documentation or making some kind of category error.

Before I dive into this more (I have spent about 4 hours on writing some test scripts in Python calling into the assemblies via PythonNET), I wanted to check that this is possible. I am a former DBA and have lots of experience with incremental sync based off trigger-maintained temporal backlogs but do not have any direct experience with using change-tracking (as I had stopped being a DBA by the time it came out).

[Mimetis]

I never tested to setup a sync where server is readonly
For sure, server needs several things to be created, lile DMS metadata tables (scope_info ...) and stored procedures (and triggers if not using change tracking)

ALL these things can be done on the server side with an admin SQL account , before any client connects and sync.

When a client sync, server still needs to update metadata, it's up to you to use a connection string with the correct user with the minimum amount of rights to do so.

[phrrngtn]

Thank you for your response. I will code up a solution (in Python) that does not require any explicit state on the server (other than -- of course -- change tracking enabled) and use it as a proof of concept before I try and implement it with DMS. I think that for change tracking -- but not for any of the other mechanisms -- that the configuration information such as scope_info should be able to live on the client or on some third-party system. I have built a number of systems in the past on top of trigger-maintained temporal backlogs on the server but with all the synchronization state on the client (the sync code selects out the maximum timestamp from each local table and uses that to query each backlog for records more recent than that)

I will use the same schema (e.g. scope_info etc) as DSM does and follow up with you to see how to implement the same/similar mechanism on top of DSM.

thanks!