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:
- You create an extension (version 1.0) that contains a custom table with some fields and some keys and publish it. All is ok.
- 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.
- 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:
- Mark the changed field with ObsoleteState = Pending or Removed.
- Create a new table (or new fields or keys) with the new data type or values.
- Create an Upgrade codeunit that moves the old data to the new data.
- 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.
Ciao Stefano, prima di tutto complimenti per il tuo blog.
Mi domando se mi puoi aiutare con una domanda che riguarda i parametri necessari in poweshell per connettermi a D65 BC Saas (sial Sandbox che Live).
Se in -Tenant specifico lo stesso GUID che uso in VS Code, powershell mi chiede di specificare ServerInstance, che non ho idea come trovare.
Se uso il nome AD ricevo l’errore “Cannot convert value to type “Microsoft.Dynamics.Nav.Types.TenantId”
Ogni suggerimento e’ altamente apprezzato
Non ho capito… non puoi connetterti via Powershell ad un tenant online (ambiente SaaS). Powershell è disponibile solo in ambiente on-premise (o docker sdandbox).
Grazie per la conferma, avevo frainteso il tuo riferimento al “local sandbox”.
Quindi non esiste alcun modo per cancellare gli schema delle versioni precedenti di un extension su SaaS… una svista alquanto seccante da parte di Microsoft, speriamo nelle prossime release
Nella sandbox online puoi deployare da VS Code con schemaupdatemode: recreate. No PowerShell. A breve si potrà usare anche una nuova direttiva schemaupdatemode: forcesynch che permetterà modifiche allo schema del db senza perdere dati.
Hi, what about increasing length on a field for a Customized table, do you need to do the same? the only way is to create a new field? on that table
This is the actual situation:
1. Field length change = acceptable, unless it’s a PK field (in which case there is no story other than to create a new table + avoid relying on our variable length fields as their PKs)
2. Table removal = not allowed. The table needs to remain. It can be obsoleted to prevent usage, but cannot be physically removed
3. Field removal = not allowed. Similar story to #2
• Use the obsolete attributes to prevent mistakenly using fields/tables that should no longer be referenced (sanity check on correctness of self + dependents)
• Maintain schema forever
Thanks for the comment. its a PK too, creating new table 🙂