Schema Synchronization in Microsoft Dynamics Navision 2015

NAV 2015 provides you with flexibility of deciding when and how your table changes should be synchronized with corresponding SQL tables.

What is table synchronization?



NAV 2013 R2:- Schema synchronization executes automatically on any connection to the Server.

NAV 2015:- Schema synchronization executes on demand by the Server.

Way the Synchronization works:

1. Per Table Synchronization


2. Synchronizing schema changes for all tables

Now let us see how it works:



Add/Modify/Delete Fields (change table definition) we will be prompted for Synchronizing the Schema.

It gives three options description is self-explanatory.

Some time we have to do major changes to the tables which takes lots of time to sync, we can postpone the sync process by selecting Later. Once we are done with all of our changes we can go with synchronization in one go.



We get same options.

New option Check Only….



This option will only report outcome and will not Sync the Schema.

Since we have just added new field so no risk as reported by above check.

Now I will delete one field from the table and let’s see what result comes.



Summary of the 3 options as below:



When data loss is reported while changes in table definition is referred as destructive changes.



You use upgrade codeunits when you make changes to a table definition, either from the Microsoft Dynamics NAV Development Environment or during an upgrade. Upgrade codeunits migrate existing business data from the old table structure into the new table structure. An upgrade codeunit serves the following purposes:

• Provides instructions for how to handle data changes to a table during schema synchronization.


• Provides the logic for migrating existing data in the business data table from the old format to the new format after schema synchronization.

A typical example of when to use an upgrade codeunit is when you remove a field from a table definition and you do not want to lose the existing data in the business data table. Instead, you want to use it somewhere else in the new application.

You implement upgrade codeunits when you make destructive changes to tables in Microsoft Dynamics NAV Development Environment. After you have created an upgrade codeunit to handle table changes, you can use it when you upgrade data from an earlier version of Microsoft Dynamics NAV to the current version. A single upgrade codeunit will typically contain table schema synchronization instructions and data migration logic for multiple tables that have changed from one version of Microsoft Dynamics NAV to another.



I will come back on this topic in another post with more details.

And many more options like PowerShell Sync with above options.

Benefits:-

Perform on demand – transparency -> As developer and system administrator, you are in control when to call the schema synchronization and how to synchronize table changes. You can invoke it, plan for it and schedule for it on a case by case basis. You can also monitor its progress and monitor the state of your database (tenant).

Not blocking other changes – productivity -> Schema Synchronization has become more granular. It can be performed per table object as well as for all table changes at once. When one table is being synchronized you are no longer blocked, and you can continue making changes to other tables.

Opened for additional instructions – flexibility -> Using upgrade codeunits you can provide input for the schema synchronization to do extra tasks, like automatically moving or copying data into upgrade tables, checking the changes or forcefully applying them where necessary. (Will come up with more details on this in my next post)