Synchronizing Database Schema in NAV 2015

Before NAV 2013 R2 the “database schema synchronization” was an unknown term. Either you could do a database schema change, or you couldn’t. There was no such thing as data loss.  If you wanted to change a field type or delete a field (or delete the whole table for that matter), and there was data in that field in the table, you got an error. Data present – no go.

However, with multi-tenancy, and the possibility to mount tenant databases originating from different applications, suddenly there was an issue – how should NAV handle possible differences in database schema for different tenants? Should we force the change at the cost of data loss? And thus the term “database schema synchronization” entered the vocabulary of regular NAV Joes.

But even before multi-tenancy, you could rightfully say that NAV was pretty stubborn. As a developer, shouldn’t you know when a field can safely be let go? By what right did NAV force you to first empty the field before you can change something about it? Just remember all those situations when you had to halt the development process just because you first had to clean up the testing mess before you could apply the change to a table, to be able to continue programming the business logic.

NAV 2015 has a completely different approach to it. Essentially, it completely detaches the development process from the database schema. It makes developers not worry at all about which data may be present (or may be lost) when applying changes to the database schema. It simplifies the development work (something developers should worry about) by detaching it from the data maintenance work (something that developers should better not worry about).

Therefore, in NAV 2015, whenever you save the table, NAV politely asks you this (or something along the lines of this):

image

Apart from giving you three options (that I’ll name in a second), it also explains what the option will do (if anything) to existing data.

These are the options:

  • Now – with validation: with this option, NAV will first check if any data loss would occur. If it would, you get an error. This is the default behavior of all previous versions of NAV.
  • Later: this option allows you to just continue working, and then synchronize all the changes at a later time. It says you can do this from the Development Environment, but you can do this from PowerShell as well, using the Sync-NAVTenant cmdlet. This option allows you to continue your development work (for example, you are able to immediately refer to the changed field in the code of other objects, such as codeunits, before you actually change the physical table definition in SQL Server – which is what happens during schema syncronization).
  • Force: and the dark side of it, at that. This option simply disregards any existing data, and makes the change at the cost of the data loss. And it’s not particularly smart, it always deletes the data in affected fields. For example, changing field type from Code to Text, will wipe the data out of the field (even though no logical data loss was mandated).

Now the developers have a lot of flexibility. If you want to keep the business as usual, just accept the default option, let the NAV check the data and complain if any of it would be lost. However, if you want to continue developing, adding more fields, tables, relations – you name it – then you can simply postpone the changes until a later time. Then, when you are ready to complete the schema synchronization, the Development Environment gives you the following options:

image

(no need to explain them, I hope)

Or, you can run the Sync-NAVTenant cmdlet, and choose any of the appropriate values for the –Mode parameter. Sync-NAVTenant is an old friend from NAV 2013 R2, so there is no need to explain it.

And now for something completely different! If you thought that this was all there is to database schema synchronization, you would be very, very wrong. Even though the possibility to postpone, or to force schema changes in itself is a tremendous help to developers, there is a feature which takes this all to a new level altogether. And let’s leave it at that. Suspense is good, and I’ll blog about this feature tomorrow. See you around!

6 thoughts on “Synchronizing Database Schema in NAV 2015”

  1. Hi Vjeko,

    thanks for sharing. I already came a cross this feature in NAV2015. Can’t wait for you’re next blog about database schema sync.

    Gr Jan

  2. “For example, changing field type from Code to Text, will wipe the data out of the field (even though no logical data loss was mandated).” – OMG, why?? :((((
    I need to change the type from code to same length text, and it apparently kils all data

  3. And the most funny thing is, the official “Changing the Design of Tables” says “you can change the data type of a field from Code to Text even if the field contains data for some records” ((

Leave a Reply