Dynamics 365 Business Central: handling breaking schema changes on production

I saw many requests on forums in the last days related to one of the most noisy topic when working with Extensions in Dynamics 365 Business Central (SaaS version): how to handle breaking schema changes.

The tipical scenario is the following:

1. You create an extension (version 1.0) that contains a custom table with some fields and some keys and publish it. All is ok.
2. Then, you create a new version of the same extension (2.0) where you change some field data types (or some field properties) or you change some table’s keys.
3. You publish the new 2.0 version and…

ERROR: “Unsupported table change” message or (for keys) the message is something like “An unsupported key change was detected. Keys cannot be changed, removed, or reordered. New keys can only be added to the end of the list“.

Previously (in the C/AL era) if you change a field property on a table or if you change a table’s key and you’re sure that this will not occour in a data loss, you could export the fob and import them in the production environment using the force option and all works.

Now in the cloud environment things are different.

Extensions V2 do not support breaking schema changes across synchronized versions of the extension. Uninstalling and unpublishing the 1.0 extension will not remove the schema elements coming from an extension.

In your local sandbox environment you can “clean” your environment in the following ways:

• From Visual Studio Code by using “schemaUpdateMode”: “Recreate” in launch.json
• From Powershell by executing the following command:

Sync-NavApp -Name <name of extension> -Version <Version of extension> -Tenant <Tenant name> -Mode Clean

This will remove all the schema elements across all versions of the extension (and also all the data). The next time you will deploy your extension, the system will not found an “old schema” to compare agains the new schema and all your changes will be considered as new.

What about in the production environment?

In a production environment, once an extension is synchronized to a tenant then all the next versions (upgrades) must have a schema that is backward compatible. To handle this scenario (that can occour, I agree…) you can:

1. Mark the changed field with ObsoleteState = Pending or Removed.
2. Create a new table (or new fields or keys) with the new data type or values.
3. Create an Upgrade codeunit that moves the old data to the new data.
4. Use the new field or keys.

The ObsoleteState can have these values:

• Pending: the element will be obsolete in a future release. This setting has no effect on the current use of the table, field, or key in code.
• Obsolete or Removed: the element is not deleted from the database; however, references to the the element in code are only allowed in upgrade codeunits. References from other objects will result in a runtime error.

If you need to change a primary key of a table from a previously deployed 1.0 extension, I think the best way is to create a new table and mark the old table as obsolete. In your Upgrade codeunit, move the data from the old table to the new table.

Lesson learned after this post? That in the SaaS era, you have to deploy in a production environment more and more carefully than before and only when your design is really ready!

UPDATE 31/12/2019: Please remember also what explained in this post.