Since I joined Fenwick Software in Oct 2015, I’ve completed five Dynamics NAV upgrade projects. Such projects typically start with the code upgrade or application upgrade which is followed by the data upgrade. In this blog, I’m sharing with you my take on the data upgrade process. Here are the top 10 rules that made my upgrade projects successful:
- Follow the official script – The latest data upgrade script can be retrieved from the Microsoft MSDN web sites (e.g. Upgrading the Data to Dynamics NAV 2017).
- Understand the steps – Following the right upgrade steps is a necessity, however we also need to have a good understanding of what is happening in NAV, so we can properly verify the result of each step and resolve errors that might occur during the upgrade. Blindly following the steps is a potential risk for the upgrade project.
- Take notes – I always start my upgrade by putting all the upgrade steps in OneNote. This allows me to track details against it (e.g. comments, statuses and durations) to add additional custom upgrade steps (e.g. post-upgrade setup) and to share the Notebook with the project team. This might seem overkill for some, however, there are many good reasons for keeping a detailed record. The main idea is to make an upgrade process as repeatable and predictable as possible and to eliminate any surprises that might occur during the cutover.
- Measure durations – Measuring the durations of each upgrade step is especially recommended when upgrading a large database. The information gained will not only help us to prepare a precise cutover plan, it is also a key input for upgrade optimisation. NAV offers standard commands (e.g. Get-NAVDataUpgrade) that return the status and duration of all upgrade functions that were invoked during the upgrade process.
- Optimise – If the total upgrade time exceeds the customer acceptable downtime window we have to find innovative ways to optimise the upgrade. Strategies range from replacing upgrade functions with T-SQL scrips and deleting obsolete data before the upgrade (e.g. Change Log and Archive tables) to improving server performance by adding memory or faster drives. We encountered an interesting challenge on the latest upgrade project, where the data upgrade took more than 3 days to complete. After measuring durations, we discovered that this was caused by the upgrade function which is renaming and updating User IDs. Simple optimisation cut down the duration of this function from 68 hours to 5 hours.
- Perform backups – We recommend that a full database backup be done after every major data upgrade step. Compression of backups should be enabled to increase the speed and save the disk space that will be required. In addition, database recovery model can be changed to Simple to free up log space. With this setup, we lose the ability to do a point in time recovery, therefore the most recent restore point will be the latest complete backup.
- Take the latest CU – Let’s say we are upgrading from NAV 2009 to NAV 2017. It is necessary to first upgrade data to NAV 2013 or to NAV 2015. My recommendation here is to upgrade to NAV 2015, simply because it is more mature version (e.g. contains upgrade codeunits and improved schema synchronisation). In addition, we should always take the latest cumulative update (CU) available, because it potentially contains upgrade toolkit bug-fixes.
- Use Upgrade Codeunits – Upgrade Codeunits were introduced with NAV 2015. Understanding and utilising upgrade codeunits is by far the most important data upgrade skill. Firstly, upgrade codeunits provide instructions for synchronising schema changes (e.g. to migrate data into upgrade tables) and secondly, upgrade codeunits contain upgrade functions, which migrate data from the upgrade tables into the new locations and also perform other data manipulations.
- Use Force only when required – Upgrade codeunits should provide explicit instructions for each table on how to handle data changes during the schema synchronization. These instructions are defined in TableSyncSetup functions. Synchronising the schema with option Force should only be used in rare cases (e.g. when deleting upgrade tables).
- Delete obsolete tables – One of the last data upgrade steps is to delete upgrade objects. In addition, we should also delete all old unused tables that were brought across from previous version to keep the upgraded database tidy and clean.
Microsoft used to release a new version of NAV every few years and the best practice for customers was to upgrade every second release. This has dramatically changed since Microsoft moved to annual version releases and monthly cumulative updates. In order to leverage new functionality NAV customers have to re-think upgrades and shift to frequent upgrade and update cycles.
Fortunately, the good news is that upgrading is no longer the time consuming and painful process it was in the past. Microsoft has enhanced the power and flexibility of the tools it provides to help us do upgrades more efficiently. The Fenwick Software team is highly specialised in Dynamics NAV upgrades. We are following the latest upgrade best practices and using automated tools which have saved our clients significant amounts of time and money. Get in touch if you would like more information around NAV 2017 or to get a free quote for your NAV upgrade project.