Last weekend it was time for me to brew up a strong coffee, clear my schedule and upgrade our Dynamics NAV database from 2016 to 2018. This was a big operation for me and many colleagues as we also upgraded to SQL2017 and Windows server 2016.

The good thing about using a new server environment was that I got to do a trial run with the production conversion. Invaluable it turned out as we found some issues that we had not had converting our Acceptance database. The mail culprit here was an old SQL index that played silly buggers on our item table. I had run into a similar issue before on another database. Removing the index fixed it.

For the Dynamics NAV upgrade I used the steps Microsoft advised in their article https://docs.microsoft.com/en-us/dynamics-nav/upgrading-the-data. I found some issues though so I amended this list for my personal use. With success, I upgraded our ~800Gb database in about 4 hours with zero issues.

For the Powershell lovers among you, sorry, no fancy scripts. I work for an end-user and I don’t mind executing the steps by hand as we only upgrade once a year.

For your information, here is the full list with citations for the sources of my fixes. As always, if you have questions or improvements let me know. Enjoy and happy migrating!

Before the upgrade:

  1. Code merge. Use the scripts Waldo made
  2. Install Dynamics NAV 2018
  3. Create upgrade FOB. Don’t forget upgrade objects

Upgrade:

  1. Backup database
    1. Auto create statistics off in SQL Database.
    2. Database recovery on simple
    3. Shrink database and files
  2. Uninstall OMA http://www.msdynamics.de/download/file.php?id=5665&mode=view http://www.msdynamics.de/viewtopic.php?f=68&t=31064
  3. Open the database with the 2016 object designer and run a 2016 service tier for the database.
  4. Unlock all objects
  5. Build all application objects https://docs.microsoft.com/en-us/dynamics-nav/how-to–build-server-application-objects
  6. Sync database schema https://docs.microsoft.com/en-us/dynamics-nav/how-to–synchronize-the-tenant-database-with-the-application-database
  7. 2018 Dev license in the database
  8. Stop all service tiers
  9. Delete all objects except tables
  10. In SQL:
    1. Clear Statistics, https://markbrummel.blog/2017/12/14/nav2018-upgrade-issue-with-sync-navtenant/
    2. Clear SQL tables before upgrade, delete entries in tables [Debugger Breakpoint] and [Server Instance]. Delete all locks as somehow deleting them in the object designer is not enough. See the sql script below.
    3. Check for open sessions with sp_who. Kill them if necessary.
  11. Open NAV2018 Object designer as Administrator https://forum.mibuso.com/discussion/69166/sync-error-invalid-column-name-base-hash
  12. Open the database in NAV 2018 object designer. Convert the database when prompted.
  13. Load FOB
  14. Compile all objects with met Schema Sync on later, when using rtm then clear C:\Users\<USER>\AppData\Local\Temp\Microsoft Dynamics op DY-RAS2 after compile. This is a bug in compiling reports that is fixed in CU1.
  15. Check version of new objects and make sure the upgrade objects are in the database.
  16. Connect a NAV2018 service tier
  17. Open Administration Shell as administrator on the service tier
  18. Schema sync “Sync-NAVTenant –ServerInstance <ServerInstanceName>”
  19. Data upgrade “Start-NavDataUpgrade <ServerInstanceName> -Force”
    1. Check progress with Get-NavDataUpgrade -Progress
  20. Delete the objects that need to be deleted. There are a few objects in NAV2016 that are not used in NAV2018. For us these were (might not be the same for you, due diligence advised!):
    1. Table 1305
    2. Table 1510
    3. Table 470
    4. Table 5150
    5. Table 5181
    6. Table 5371
    7. Table 5372
    8. Table 824
    9. Table 825
    10. Table 826
    11. Table 827
    12. Table 828
    13. Table 829
    14. Table 830
    15. Table 8640
    16. Table 9192
  21. Delete upgrade objects
  22. Register Clientcontrol add ins
  23. Install Extensions
  24. Update Dynamics NAV web client config
  25. SQL
    1. Turn on auto create statistics
    2. Datarecovery on full (or your preferred setting)
    3. Backup
  26. Load customer licence
  27. Do a happy dance

And here is the sql script I used to clear the necessary tables:

DELETE FROM [database].[dbo].[Server Instance]
DELETE FROM [database].[dbo].[Debugger Breakpoint]

use [database]
UPDATE [Object] SET [Locked] = 0, [Locked By] = '';

About the Author René Brummel

My driving force is the will to learn. Every day brings its unique challenges, my goal is to rise to each challenge and learn from it. My professional expertise is the streamlining and optimization of business processes. My experience ranges from software development (I know AL, C/AL, C#, .NET, Visual Basic etc) and implementation to business processes optimization, project management, training, and coaching. At the heart of what I do lies my vision, “sustainable change comes from conscious choice, made out of free will”. In my spare time I am a member of Bikers Against Child Abuse. B.A.C.A. helps abused children to no longer be afraid of the world in which they live.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s