Add new columns to old Table

[maxrodro]

Good morning friends, today I want to know what is the correct way to create new columns in a production environment that does not affect the synchronization of my old clients.

Today I added a new column to a table, then I did the DeprovisionAsync and ProvisionAsync to that table to make the adjustments in the procedures, I use SqlSyncChangeTrackingProvider, but on the side of my clients it gave synchronization problems in their app that works with SQLite.

What can I do so that this does not affect until I can publish a new version of the app that uses this new column and that the app does DeprovisionAsync and ProvisionAsync to the table with the new column?

Or could it be that I did something wrong?

Could you send me the correct steps on how to make new columns to old projects.

I am using the latest version of Dotmin.

Error!!!
As I see it is because the number of columns is greater than the one that the app has in the synchronization.

[Mimetis]

Did you do the required steps in order ?

• Update Server database columns
• Update DMS with Deprovision / Privision
• Update Client database columns
• Update DMS with Deprovision / Privision

[Mimetis]

In my opinion, your best option would be to:

• On Server, declare your SyncSetup table with the list of columns without the new added column
• Deprovision / Provision
• On Client, declare your SyncSetup table with the list of columns without the new added column
• Deprovision / Provision
• Alter your table and add your column in your server database
• Run sync until you have migrated all your clients with the new column
• Once all clients are migrated, add the column to your setup on both side

That's being said, thanks for the feedback, I guess the migration is not easy.
I will think about it, and see if we can make something a little bit easier.

[maxrodro]

Good morning @Mimetis

I think the best solution is to make the adjustment in the Dotmin, which ignores if it has more additional columns of the local schema (client app), so the new column is added on the server, and each app that is updated uses that column.

We require an option that does not affect production because there are scenarios where the publication is made today and very few update it, it can even take weeks until all clients with the app have the latest version.

[maxrodro]

@Mimetis

It doesn't work that way, we require that the server has the column so that some apps with the version with the new column can synchronize and tests can be performed; Any other suggestions on the subject?

[Mimetis]

I think the best solution is to make the adjustment in the Dotmin, which ignores if it has more additional columns of the local schema

I'm currently looking at it.
It implies a lot of changes in the internal DMS core mechanism system.

The main thing is that I need to unplug the automatic DMS migration on the client side.
Today, we can resume the process like this:

1. DMS get the schema from remote server
2. DMS make a comparison locally between the local schema tables (store in DMS scope_info table) vs the remote schema tables (downloaded every time DMS make a sync)
3. If schema is not corresponding, a Migration (internal DeprovisionAsync then ProvisionAsync) is done to update the DMS json schema field in sync_scope table, stored proc, triggers, tracking tables and so, on locally.

Usually before (1.) You have already made your database alter and then DeprovisionAsync / ProvisionAsync on the server.
I understand that doing the alter schema on the client is tricky (before (2.) since you are not sure your client is up to date with the last version once you have done the server migration)

Today, the time between you made the update on the server and the time you have updated the client is a big issue since the sync is not working

That means that we should disable the automatic migration on the client when a schema coming from the server is different from the schema client stored locally and allow a row coming from the server to be a little bit different from the local schema (with new or removed columns in the payload)

That means also that DMS must be able to allow sync to the local database with a schema different from the schema coming from the server:

If we have a new column in a table from the schema coming from the server:

• DMS should be able to understand that we have one more column and transform this row into a compatible row locally (by removing the cell in each row corresponding to the new column) (this case is the most simple one, I guess)

If we have a column dropped in a table from the schema coming from the server

• DMS should be able to understand that we have a column removed and transform the rows from the incoming payloads into a compatible row locally (by add a null the cell corresponding to the new column)
• BE REALLY CAREFUL ! I suppose that If the column is not allowing null values in the local table, the will fail at some point...

If we have an altered column in a table from the schema coming from the server
Damn... I don't know...

• I guess we are in the two previous cases, where we have a deleted column from the server (that will have a null value locally until migration is done) and also a new column that will be removed...

If we have a dropped table coming from the server

• DMS should be able to sync all the others tables

If we have a new table coming from the server

• DMS should be able ignore it and sync all the others tables

That way, you will be able to add / remove columns on the server, apply the migration to DMS (DeprovisionAsync / ProvisionAsync) and still having a client working, at least with a side effect on the new / removed column, until you have applied your migration to the database locally (and on DMS afterward)

@gentledepp, @maxrodro, @VagueGit, @vunder, @gb0o your thoughts on disabling the automatic migration on the client ? (and pleas do not say "just add the automatic migration as an option" ! )

[maxrodro]

Hello everyone,

My comments:
Disabling the automatic migration would help to have a more detailed control of the schema at the column level, I think we can maintain that with the migration of the tables, but once we define the columns to be synchronized, this helps the synchronization to be faster in data subjects by the columns and not all the ones that the scheme generates.

What would be my idea of ​​a migration based on a Server and an APP (Android, Apple).
** Server **

1. Server> Initial without DMS
2. Server> The DMS Setup is done, including the tables to be synchronized and their columns. At the time, the DeprovisionAsync and ProvisionAsyn is done as it is currently done.

** Client (App) - First synchronization **

1. Client> Starts with server schema synchronization, and creates initial database with server schema.
2. Client> Synchronizes the data with the columns indicated in the client Setup.

** Client (App) - New Column **

1. Server> Add the new column on the server, DeprovisionAsync and ProvisionAsyn as currently done
2. Client> In the new version of the App, the column is manually created in the ALTER TABLE database and this new column is added to the DMS setup and the DeprovisionAsync and ProvisionAsyn is performed
3. Client> In the synchronization it should already take that new column to send the data, I clarify that these steps are for columns that allow NULL.

I believe that having control of the columns to work with will be easier to remove or add to the synchronization level.

Perhaps to make this adjustment it will be easier to obtain the columns of the Setup and these are the ones that you must synchronize.

A point that should be emphasized is that the server that has priority in its tables and columns to be synchronized, a client cannot have more columns in the Setup than in the server.

Having said all this, I think that would be the way to have a starting point, there are issues that you mention that I do not touch, but if I delete a column from the server, I think that the correct way is to delete it from the client Setup, it is not sent anymore and wait that all clients have the latest version and finally weeks or months later remove the column from the server.

For changes of names in the columns it is a very complex scenario to carry out, it is better to create a new column to do the data transfer and finally to delete the column at different times.

The last option if it is a complex change at the DMS level is always to compare the schemas at the table and column level, if a change is detected, DeProvisionAsync and ProvisionAsyn are made automatically.

I hope my comments help.

[VagueGit]

I don't know if this will be helpful for you, but we've struggled with migration too, using SQLite on the client and Dotmim.Sync.SqlServer.ChangeTracking on the server.

Deprovision/Reprovision on the server can keep the user waiting longer than they like and that means they might close the app.

In preparing for our latest migration I am experimenting with this process;
The new version of the client app checks both the local db version and the server db version.

if (localDbVersion < 15)
{
    if (serverDbVersion < 15)
    {
        StatusMsg = "Server upgrade required.";
        UpgradeServerDatabase();
        StatusMsg = "Server upgrading. Patience ...";
        await DeprovisionServerAsync();
    }
    // this is the most reliable way to get the latest db locally
    DeleteLocalDb();
    StatusMsg = "Server uptodate";
}

Deprovision without Reprovision returns much faster than Deprovision/Reprovision.

The app now has an uptodate server but no local db. It proceeds as a new install and syncs, allowing DMS to provision the local and server databases.

A new install can also be quite slow so we sync one table at a time, following the relational heirarchy. This gives us a sync scope for each table and allows us to give progessive feedback to the user as each table syncs.

By syncing one table at a time we've managed to avoid using snapshots on the server.

[maxrodro]

Thank you very much for your comment, I will evaluate it with our development team.

[gentledepp]

Well I already though this through, but did not find the time to implement it.

supported changes aka wishlist

There are essentially four change that should be supported by DMS (or "it would be awesome if DMS supported them" ;-)):

1. add a new column
2. remove an existing column
3. add a new table to a scope
4. remove an existing table from a scope
   Changing a column type will never work. In such a case you will have to remove the column and add a new one. Otherwise, keeping the schema backward compatible is impossible imho.

two strategies

When migrating a schema, there are essentially two ways to handle old clients.
a) do not migrate the schema of the old clients - then you need to make sure you can map/translate the sent records between the two schemas
b) always update the schema of old clients once the sync for the first time - in this case, DMS does not have to translate, but the application developer needs to ensure, that it takes dynamic schema changes into consideration.
For example: If there is a table Product with columns Id|Name and a new column Price is added like Id|Price|Name, then the client will have an issue if it uses queries like select * from products since it will suddenly receive an unexpected third column in the result set.
In any case, the schema version should imho always be dictated by the server.

Regarding the two options:

• (b) is harder for the user of DMS
• (a) is harder for DMS.

Therefore, i would like to opt for (a). (sorry @Mimetis :-D)
But for some, (b) would be actually nicer - since this is a much more dynamic approach. 🤷‍
Maybe we could provide this as a configuration in the sync scope: EnableDynamicClientSchemaUpdates: true|false to enable (b)

migration strategy (a)

doing the migration on the server

Going with (a) in both cases (adding (1) and removing a column(2)) DMS should detect that there is actually a change in the sync setup.
So I guess we cannot just use DeprovisionAsync and ProvisionAsync since doing so, DMS has no clue that it is actually editing an existing sync configuration
So there should be a MigrateAsync(string migrationName) I guess. That way we can give the migration a unique name (just like in EntityFramework). DMS would then track the schema versions (name along with the schema and the time stamp when this migration was applied in some separate table.)
This is important so that DMS can actually detect, that a column was added or removed.

• When (1) a column was added, it must throw an exception, in case that column is not nullable. The reason is, that there will still be clients out there that have the old schema, and will therefore send no values for this column.
  An alternative would be to provide a default value (sql default) that DMS on the server can use, when applying rows from an old client.
• When (2) a column is removed, it also must throw an exception if the column is not nullable. In this case, the reason is that the client would otherwise receive no value for a non-nullable column and therefore fail to sync.

handling the migration on the client side

Now, when an old client receives records with an additonal column that it does not know, it needs to silently discard it.
When it then actually sends changes to the server and the records are missing the new column, DMS needs to detect that and ensure, that id does not just provide a null value.
Instead, it should just not update that new column - otherwise we could loose data!
Only if the record is an insert, the server should provide a null (or a default) value for it.

This is actually a point where I still think how to solve this when you are using bulk-operations, as you then need to define the bulk-type up-front.
Since clients do not send so many changes (usually), I thought the easiest option would be to use bulk-operations for syncs with the new schema only, and fall back to per-row-updates in case the data comes from an old client.
This may then not perform the fastest, but you do not have to keep (potentially) dozens of bulk-types for the various versions in your database to support each version.

migrating old clients

What is then still very important, is the actual migration of the client.
This should happen, whenever the client is ready.
Let's assume that one week after the server was updated, some user downlaods the latest client version from google play/app store/microsoft store/whatever...

Then, the next time it synchronizes, the server should detect (as the client sends the name of its latest migration), that client c1 is now running the latest bits and can handle the new schema.

• if there is a new column, the server should re-initialize that table, since the client now needs the data of that column that it previously did not know nothing about
• if there is a new table that was added in the migration, then the server should now send that tables data (with 'initialsync=true') so that the client gets all that tables data.
• if a record was removed or a table was removed, this should essentially be handled by the client. So DMS should only drop the triggers and tracking tables of that table. If the user wants to delete the data, he will have to do so himself.

migration strategy (b)

doing the mgriation on the server

should essentially work the same way...

handling the mgiration on the cliend side

since the client schema will always be kept at the same version, as the server schema, there is nothing to handle in particular.
Only the first time the client synchronizes, it may still provide records with the old schema - which DMS has to translate/map therefore as described in strategy (a)

migration old clients

when the client synchronizes, it should send along the name of the (latest dotmim sync schema) migration it is currently using.
The server can then detect, that the client needs an update, and send along the latest schema version for the client to update.
In the simplest case, this can always be handled like an ReInitializeWithUpload of the whole scope. This is the simplest solution and should be acceptable in most of the scenarios I guess.

Since this is the "dynamic" mode, I would that any column/table that was dropped by the migration should also be dropped on the client side.

Does this make sense to you guys?
Which option would you like to see? (a) or (b) or both?

[maxrodro]

Good day @gentledepp ,

I think that both works for multiple cases, but option A is the best so that everything works without much interaction from the developer.

[maxrodro]

Hi @Mimetis , what have you thought of these comments, have you thought how to make an improvement without affecting the core of DOTMIM

[maxrodro]

Thank you very much, we appreciate your effort with this great project

[Mimetis]

I'm currently working on it, but it implies some major changes in the DMS workflow...

Sample

Here is a quick sample I'm currently working on.
It is relying on scopes to make a client migration
This scenario is pretty simple

• We have a DefaultScope with 1 table (ProductCategory) and all columns synced (ProductCategoryID, Name, rowguid, ModifiedDate)
• We have clients syncing correctly for days
• We want to remove 2 columns (rowguid, ModifiedDate) on the server
• We want all the clients to continue working correctly even if they don't have already removed these 2 columns
• New clients will create the ProductCategory without these two columns.

The idea is to:
On the Server :

• DO NOT remove columns from the server until all clients have removed them
• Create a new scope called v1 without the columns rowguid and ModifiedDate for all new clients
• Still having the scope DefaultScope with all columns for all old clients

On the Client:

• Remove the two columns on a new software update / migration.
• Optionaly : Deprovision the old scope DefaultScope
• Provision a new scope called v1 without the two columns
• Sync normally

Here is the source code I'm using right now to test:

var progress = new SynchronousProgress<ProgressArgs>(s =>
{
    Console.ForegroundColor = ConsoleColor.Green;
    Console.WriteLine($"{s.ProgressPercentage:p}:  \t[{s.Source[..Math.Min(4, s.Source.Length)]}] {s.TypeName}:\t{s.Message}");
    Console.ResetColor();

});

// Server provider
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));

// Client 1 provider
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));

// First startup Setup with ALL columns
var setup = new SyncSetup(new string[] { "ProductCategory" });
setup.Tables["ProductCategory"].Columns.AddRange(
    new string[] { "ProductCategoryID", "ParentProductCategoryID", "Name", "rowguid", "ModifiedDate" });

var options = new SyncOptions() { ProgressLevel = SyncProgressLevel.Information };

// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, options, setup);

// Make a first sync to get the client schema created and synced
var s = await agent.SynchronizeAsync(progress);
Console.WriteLine(s);

// Step 1 : creating a new scope on server without columns "rowguid", "ModifiedDate"
setup = new SyncSetup(new string[] { "ProductCategory" });
setup.Tables["ProductCategory"].Columns.AddRange(
    new string[] { "ProductCategoryID", "ParentProductCategoryID", "Name"});

// Creating a new scope called "V1" on server
var orchestrator = new RemoteOrchestrator(serverProvider, options, setup, "v1");
await orchestrator.ProvisionAsync();

// add a product category on server (just to check we are still able to get this row)
await AddProductCategoryAsync(serverProvider);

// Optional : Deprovision client old scope
// var clientOrchestrator = new LocalOrchestrator(clientProvider, options, setup);
// We just need to deprovision the stored proc
// await clientOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures);

// Removing columns from client
await RemoveColumnsFromClientAsync(clientProvider);

// Provision the "v1" scope on the client with the new setup
var clientOrchestrator = new LocalOrchestrator(clientProvider, options, setup, "v1");
await clientOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures);

// create a new agent and make a sync on the "v1" scope
agent = new SyncAgent(clientProvider, serverProvider, options, setup, "v1");

var s2 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s2);

Changes to consider

No automatic DMS client upgrade anymore

Are you agree if DMS is not making any automatic internal migration when a SyncSetup coming from a server is not the same as the SyncSetup stored locally ?

I mean, if a server is adding a new column, today, any client is automatically upgrading the sync metadata (stored proc, triggers and so on...) with that new column
Today since we want to allow both clients without this new column and new client with this new column, to work correctly, that means DMS will not migrate the old client with the new Setup.
Once you have updated the client schema (adding the column) you will have to call DeprovisionAsync / ProvisionAsync again

New mechanism for scopes

Today, we can have multiple scopes, but theses scopes have to share the same columns for a table that is in common in the two scopes
Now, we would be allowed to have multiples scopes for a single client, with separated stored procedures:

For example, here, we have a DefaultScope with all columns for the ProductCategory, and a second scope called v1 that contains 2 columns less.

It allows you to continue syncing the client with the DefaultScope and all columns, until you have removed them, and then use the v1 scope.

No columns triggers

The triggers will not make any verification on each column, to see if you made an update on a column with the same value.
A row where you update a column with the same value (that means you did not any update, eventually) will be considered as an updated row and will synced next time.

Eventually this behavior is the same behavior as Sql Server Change Tracking.

Today, we are making a column comparision in the update trigger, like this

UPDATE [side] 
SET 	[update_scope_id] = NULL -- since the update if from local, it's a NULL
	,[last_change_datetime] = GetUtcDate()

FROM [ProductCategory_tracking] [side]
JOIN INSERTED AS [i] ON [side].[ProductCategoryID] = [i].[ProductCategoryID]
JOIN DELETED AS [d] ON [d].[ProductCategoryID] = [i].[ProductCategoryID]
WHERE (
	ISNULL(NULLIF([d].[ParentProductCategoryID], [i].[ParentProductCategoryID]), NULLIF([i].[ParentProductCategoryID], [d].[ParentProductCategoryID])) IS NOT NULL
	 OR ISNULL(NULLIF([d].[Name], [i].[Name]), NULLIF([i].[Name], [d].[Name])) IS NOT NULL
	 OR ISNULL(NULLIF([d].[rowguid], [i].[rowguid]), NULLIF([i].[rowguid], [d].[rowguid])) IS NOT NULL
	 OR ISNULL(NULLIF([d].[ModifiedDate], [i].[ModifiedDate]), NULLIF([i].[ModifiedDate], [d].[ModifiedDate])) IS NOT NULL
)

Since we can't know which scope (with different columns) is triggered, we have now to trigge all columns, like this:

UPDATE [side] 
SET 	[update_scope_id] = NULL -- since the update if from local, it's a NULL
	,[last_change_datetime] = GetUtcDate()
FROM [ProductCategory_tracking] [side]
JOIN INSERTED AS [i] ON [side].[ProductCategoryID] = [i].[ProductCategoryID]

That means if a column is updated on a row, and the table belongs to a scope (but the setup is Not containing this column) well the row will be considered as updated.

I'm still working on it, but would loved to hear your feedback

[maxrodro]

Hi @Mimetis , I agree that when a column must be deleted from a table, the client must first do it and later on the server until all the clients have the latest version, so I don't see a problem.

It seems better to me that better at the Triggers level where no verification is performed, this is what I think, I don't know if someone else requires this verification.

And finally if a client does not have the column that is taken as modified, I think it is not a problem.

[gentledepp]

1. DO NOT remove columns...

DO NOT remove columns from the server until all clients have removed them

Removing a column on the server should never be done by DMS. This could break many things like EntityFramework migrations and the like!

2. automatic DMS client upgrade

No automatic DMS client upgrade anymore

I guess that is ok. But maybe you can keep this as a configuration option?

1. automatically upadte client schema (the "old" option)
2. use your new "migration" feature.
   this could be indicated by a flag in the scopesettings :-)

3. scope naming

Create a new scope called v1

This seems to be the perfect fit for my "migration strategy (a)" ! 🥳
For ease of maintenance, it would be cool

1. if there was a __dmsmigrationhistory table that contains all migrations and the time they went through
2. maybe to add a default naming to the scopes that is prefixed with YYYYMMDD, so that we get some ordering into mssql server:

dbo.ProductCategory_20220101_defaultscope_bulkdelete
dbo.ProductCategory_20221402_v1_bulkdelete

Otherwise, I imagine having many migrations would make it hard to see through all those stored procedures.
Does that make sense to you?

4. Deleting obsolete scopes

At some point, we need to consider dropping support for old versions of the scope to get rid of obsolete stored procedures and the like.
For this, the table __dmsmigrationhistory would be helpful, because it could contain a date (and a timestamp) of when the migration took place.

Then we can say: After 1 year, we do not care anymore and require all our clients to be updated.
If a client thereafter wants to synchronize with an obsolete scope version, the server should return a meaningful error, so that the client can show an error message to the user. Thereafter, it has to reinitialize with a newer scope.
As we do have the last time a client synced (the rowversion) in DMS available on the server side (right, @Mimetis?) we can also see if all clients have already updated to the latest schema and safely drop old ones too.

@Mimetis I do not mean: DMS should handle all this! But it would be nice if it stored all data so that we can solve such scenarios in our applications as well

5. No column triggers

No column triggers

Triggering updates only for columns that are used in a scope is an important feature. This is because there may be columns in a table that will never be synchronized and should therefore also not be used to detect changes.

I would rather propose to use a union of all scopes columns to verify if a record has changed or not.
That way an update is only triggered, if any column changed, that is actually used in some sync scope!

[gentledepp]

One more thing came to my mind!

Let's assume that there is a client c1 running the initial sync configuraion customers_v1
The server v1 also runs that sync configuration.
It consists of the table customers with the following columsn: |Id|Name|

Now, on the server we introduce a new column to the customers table and thus create the new sync configuration customers_v2 with the columns |Id|Name|Address|
This column is nullable of course. That way c1 can still use the old configuration customers_v1 without an issue.
It works this way:

• A customer |1|John Doe|Somestreet 1 is added on s1
• c1 synchronizes and gets only |1|John Doe|.
• c1 may update this record to |1|Jonathan Doe|
• c1 synchronizes and sends those changes back - supplying the sync configuration customers_v1 version name in the request
• thanks to the header, s1 can decide to use the old sync configuration customers_v1 as well, so that the column Address is kept untouched.
• so in the end, the record on s1 is |1|Jonathan Doe|Somestreet 1|
  Notice that the value of the address column was not overwritten to be null which is the intended effect!

c1 upgrades to customers_v2

Now lets assume c1 is installing the new version of the app, which now supports customers_v2
It is likely, that c1 still has some local changes that were tracked using customers_v2 and thus do not have the Address column yet.
☠️ if it would now use the new customers_v2 configuration to send the local changes, it would provide a null value for the Address resulting in this column to be cleared!
Instead, the process should be:

• we specify the new customers_v2 configuration in the code.
• when c1 is updated, it detects that the configuration was updated to customers_v2
• therefore, it synchronizes with reinitializeWithUpload, but it uses customers_v1 to collect the changes. This is because it knows that a column was added and it therefore needs to re-sync that table to get those column values!!
• howver, it also provides a header indicating to s1 that it can now accept customers_2 data
• the server will therefore apply the changes using customers_v1 thus not touching the Address column
• ... but it will send the data back using the new configuration customers_v2
• after the data was applied to c1 with customers_v2, it will mark customers_v1 obsolete and only use customer_v2 henceforth

c1 uses customers_v2 but s1 still runs the old customers_v1 ⚡

Also, it can be the other way round: A newer client version using customers_v2 tries to synchronize with a server still running the old version customers_v2 and knowing nothing about that new version v2!
In such cases the server must throw a fatal error indicating that no changes can be processed by a client if the client sends a sync configuration name that is unknown to the server!

[maxrodro]

Hello,

What did you think of our comments, are they too complex for the Dommim core?

Tell us what you have thought of all this, have you thought of other solutions that can be adapted to today's mobile device issues?

[Mimetis]

I'm currently working on the next version, allowing to work with multiple scopes at the same time
It's a lot of work and refactoring, but I guess I should have something running by the end of the next month

[maxrodro]

Hi @Mimetis

How have you been with this improvement of the Dotmin? Was the issue complicated at the code level?

[Mimetis]

It's in progress, you can have a look on the branch https://github.com/Mimetis/Dotmim.Sync/tree/v0.9.5
A lot of changes in this version. More flexible, with a complete rework of the scopes handling

Some tests are still failing, and I did not test the backward compatibility yet.
Use it on a test project

[maxrodro]

Hi @Mimetis

My team hasn't had time to test this version. We're working on an important project for the company where every resource counts. I'd like to know how this version went for you. Have you been able to dedicate hours to having the product stable?

[Mimetis]

I will have more time now. I hope to have a released version by the end of the month