Prepare for Business Central and get better right now: Check for breaking changes in NAV 2018 (and older)

A couple of features and concepts in Business Central and AL are clearly inspired by Microsoft now being a hoster and providing a SaaS solution. That in itself makes more then sense and as I have written a couple of times now, from my point of view all decisions made and directions taken are quite fine. One example is the decision to disallow breaking SQL schema changes in updates.

The TL;DR

Updates without breaking schema changes are quicker and schema synchronization can be done while users are still online, so you have less downtime. This is enforced in Business Central, but you can already use the new PowerShell cmdlet Test-NAVApplicationTable (included in NAV 2018) to test your traditional C/AL code changes even from older releases for accordance to the new upgrade rules.

The details

There isn’t any documentation about that (yet?) but Morten Jensen had a very interesting session at the last Directions conferences about “no downtime – continuous upgrade” which explained Microsoft’s goal to get as close as possible to instant upgrades without any downtime. To quote from his presentation, the rules are basically the following: “Only additive changes are allowed. While it is fine to add fields and tables, other table metadata changes are disallowed. Examples include changing a field’s data type or field relationship, deleting indexes, fields or tables.”

This is the background for disallowing breaking SQL schema changes because it means that the actual schema changes can be done while users are still working in the system and you don’t need buffer tables and data transferal which takes a lot of time. In the end your update will get much quicker. While this is most important in an always online SaaS world, it also is important for current hosters where you might have a data center with a couple of hundred customers who need to get updates, so quicker and with less effort obviously is better.

In AL this is enforced by error messages about “Unsupported field changes” as you can see e.g. here. On prem this is not enforced, but Microsoft made a PowerShell cmdlet available with NAV 2018 CU 3 which you can use to check if your table changes would be supported or not, even while you are still doing old style code modifications in C/AL. For the reasons mentioned above that helps to find out how you might be able to have quicker updates for your solution and it also helps to prepare for bringing your solution to Business Central where you just won’t be able to make those breaking changes anymore. In a couple of places members of the NAV / BC development team have mentioned that a schema deprecation story is in their backlog and the new obsolete field properties are a step in that direction I guess, but it might take some time to fully get there.

How to use the tool

This is very simple: You export all or only selected tables from your old version and do the same for your new version. It is quite quick, so I just went with all tables in our vertical, including the standard Microsoft tables. You put both text exports in different folders, say folder “old” contains the tables from your old version and folder “new” contains the tables from your new version. Now you run the cmdlet like this:

Test-NAVApplicationTable -OriginalPath .\old\ -ModifiedPath .\new\

You will get output showing you the changes that didn’t respect the no schema breaking rules and the cmdlet will return an error if it found one of those

WARNING: Table 5092539 InfomaNAVPad BlobStore, The change is not allowed: Deletion: Modified(Field 20) Element Field 20 Modified WARNING: Table 5092539 InfomaNAVPad BlobStore, The change is not allowed: Deletion: "Modified by"(Field 30) Element Field 30 Modified by WARNING: Table 5010374 Texterkennung Bankbelege, The change is not allowed: PropertyModification: DataLength Element Field 160 Kassenzeichen Test-NAVApplicationTable : Changes in application table(s) are not allowed.

You can see in this case two deleted fields and one field where the length was shortened.

With Get-Help Test-NAVApplicationTable you can also get some help how this works and what the output means. While the cmdlet is only delivered with NAV 2018, you can run it on text exports from older releases (I tested with NAV 2017 but would guess that even older releases should also work). That means that you can get better with your current solution as well as prepare for Business Central right now!

The Author

Tobias Fenster