Microsoft Dynamics GP 2022 - The Database Upgrade

With summer fast showing in the rear-view mirror and the last part of the year upon us, it only means that we need to start prepping for upcoming upgrades of Microsoft Dynamics GP such as year-end, tax updates and others..

To try and help make this process of upgrading Microsoft Dynamics GP go as smoothly as possible, we've begun to put out our yearly Microsoft Dynamics GP upgrade blog series. This blog goes over how the upgrade of Microsoft Dynamics GP databases works and how to plan and prepare for an upgrade.

Hopefully at this point you have begun looking at our prior Dynamics GP Upgrade blogs that we've been releasing in this blog series:

--Microsoft Dynamics GP  2022 Upgrade Blog Series Schedule

--Microsoft Dynamics GP  2022 Upgrade: Upgrade Planning - Let's Get Started

--Microsoft Dynamics GP  2022 Upgrade - Known Upgrade Issues

--Microsoft Dynamics GP  2022 Upgrade - Version Checks & Upgrades

If you haven't already looked through these blogs, I would highly recommend it if you were beginning to think about upgrading to the Microsoft Dynamics GP Fall 2022 update or any other GP upgrade.

I would also recommend looking through the 'System Requirements for Microsoft Dynamics GP' page and the 'Upgrading to Microsoft Dynamics GP' hot topic page as well, for the version of Microsoft Dynamics GP that you’re planning to upgrade to, as these will also be updated with the Dynamics GP information.

For example, if upgrading to Microsoft Dynamics GP 2018/18.#, any build, reference the following:

System Requirements for Microsoft Dynamics GP | Microsoft Docs

>>Microsoft Dynamics GP 2018/R2 Product Download page / Dynamics GP 2018/18.# DVD:

Product Release Downloads for Microsoft Dynamics GP | Microsoft Docs

>>Upgrading to Microsoft Dynamics GP hot topic:

Upgrading to Microsoft Dynamics GP | Microsoft Docs

>>Microsoft Dynamics GP 2018 Upgrade Instructions guide:

Upgrade checklist - Dynamics GP | Microsoft Docs

Database Upgrade - The Basics!

The upgrade to Microsoft Dynamics GP is the same upgrade process that we've had in prior versions, in that the database upgrade takes place through Microsoft Dynamics GP Utilities. The upgrade manual outlines the step-by-step process for this upgrade. Here are some of the basics.....

1. It is HIGHLY recommended to setup a test environment so you can run a test upgrade of Dynamics GP first. This way, you can verify that there’s no issue with the Microsoft SQL Server version you’re launching GP Utilities against, there isn’t any product version errors with the upgrade path that you’re attempting to run and you’ll also know what, if any, errors you'll run into during the upgrade process, what it'll take to fix them, and how long the total upgrade process will take, so you can better plan on downtime the users will be out of the Dynamics GP application during the live upgrade.
2. Review the upgrade manual and hot topic for the latest known issues and pre-upgrade steps and scripts. The ‘Upgrading to Microsoft Dynamics GP’ hot topics will also tell you what build of Microsoft Dynamics GP you must be upgrading to, based on what build of Microsoft Dynamics GP that you're coming from, and whether your current build is high enough to be able to upgrade directly to Dynamics GP.
3. A frequent support case issue we see is when additional features have been installed for prior versions of Microsoft Dynamics GP but then those features were never installed nor updated on more recent releases, but then the customer plans to use that product again, but after installing it and launching GP Utilities, you get an error message stating ‘problem ascertaining version information’ and referring to a duinstall.log file, which states that a product is at too low a version/build to be upgraded to the new version.

Along with the hot topics for ‘Upgrading to Microsoft Dynamics GP ####’ which states the required version/build of the application you must be on and upgrading to, you can also use scripts like this check for ‘old’ features that may have been installed previously:

--If coming from Dynamics GP 2010:

     Select * from DYNAMICS..DB_Upgrade where db_verMajor <> 11

--If coming from Dynamics GP 2013:

     Select * from DYNAMICS..DB_Upgrade where db_verMajor <> 12

--If coming from Dynamics GP 2015:

     Select * from DYNAMICS..DB_Upgrade where db_verMajor <> 14

--If coming from Dynamics GP 2016:

     Select * from DYNAMICS..DB_Upgrade where db_verMajor <> 16

--If coming from Dynamics GP 2018/18.x:

     Select * from DYNAMICS..DB_Upgrade where db_verMajor <> 18

If any records are returned by these scripts, you’ll want to look at the PRODID value and compare that to your Dynamics.set file of your current Microsoft Dynamics GP install, to see if it’s a product you’re currently using, or a product that you plan to use on the new release in order to verify if it is at a required version/build in order to be able to upgrade.

We also have released a blog for this type of version error as well:

"Problem Ascertaining Version Information" error when installing or upgrading Microsoft Dynamics GP

1. Backup your existing DYNAMICS or system database for Microsoft Dynamics GP, as well as all company databases, any modified forms and/or reports dictionary files at a minimum.

         ***It is also recommended to export all modified forms and modified reports, along with VBA objects showing in the Customization Maintenance window (Microsoft Dynamics GP > Tools > Customize > Customization Maintenance) into a package file. This way, if there is an issue with upgrading the forms or reports dictionary files to the new version, the package file can be used to generate those dictionary files at the upgraded version.

1. Obtain all the Microsoft Dynamics GP product download DVD and patch files that you will need for the new version.
2. Obtain all the code and installs for any third-party or integrating products being used with Microsoft Dynamics GP.
3. Install Microsoft Dynamics GP at the new version/build you plan to upgrade to, including all features that you currently have installed on your current Microsoft Dynamics GP environment, as well as any new features the customer plans to use at the new version.
4. Launch Microsoft Dynamics GP Utilities for the new version and after the version check of the Microsoft SQL Server instance and the GP products themselves, it'll detect that you have databases that need to be upgraded, click Next through the wizard to first upgrade your DYNAMICS/system database and then your company databases after that completes.
5. When the Additional Tasks window appears, the database upgrade should be completed and successful. If the Upgrade Company window returns your company database(s) with a Red X, troubleshooting must take place as it normally means there was an issue with the upgrade of that database.
6. Upgrade any modified forms and/or modified reports dictionary files that you have, if any. The 'Modified Dictionary Upgrade' blog is being released Sept. 16th, so look for that to find additional information on this process.
7. Lastly, login to your new Microsoft Dynamics GP version as 'sa' and verify that you can login to the company database(s) without any issues or errors. You'll also want to add any new registration keys in the Registration window (Administration > Setup > System > Registration) if you're coming from a prior version of Microsoft Dynamics GP to a new major version. ***NOTE: Microsoft Dynamics GP 2018 registration keys will work with Microsoft Dynamics 18.#. You also don’t need to change registration keys if you’re just upgrading to a service pack or hotfix for Microsoft Dynamics GP, which only changes the build number and not the actual version you’re on.

Database Upgrade Tables:

>>DU000010

The DU000010 table in the DYNAMICS/system database is the 'Utilities Update Files' table, meaning that it contains a list of tables that Utilities uses to determine which tables must be upgraded between the version/build of Microsoft Dynamics GP that you're currently on and the version/build that you're upgrading to. This table is populated via an .out file that is in the Microsoft Dynamics GP code directory you're launching Utilities from. Not every table gets upgraded with each service pack or hotfix released, and this table helps Utilities determine just that. GP Utilities uses the current and new version/build information in the DB_Upgrade system table to determine which tables require an upgrade and those that do not.

>>DB_Upgrade

The DB_Upgrade table is also held in the DYNAMICS\system database and is the first table looked at when you launch Microsoft Dynamics GP Utilities. This is the main version table reviewed during the version check process discussed in the previous blog. Once the upgrade begins, Utilities will change the db_verMajor, db_verMinor and db_verBuild columns to the new version/build values that you're upgrading to. Not all features/modules in this table have the same version/build numbers nor do all features/modules get upgraded for each major upgrade, service pack or hotfix, so you may see different results for each of these. The db_status column will also change as the upgrade of the database progresses, beginning at 0 (zero) and moving up at each step, for example 23 indicates the tables conversion process is taking place and 53 is the Load Stored Procedures process with 54 being the 'wrapping up' step right before the upgrade process completes and changes the db_status back to 0 (zero). Once the database is upgraded successfully, the db_verOldMajor, db_verOldMinor and db_verOldBuild column values will be set to match those of the db_verMajor, db_verMinor and db_verBuild columns for each feature/module record, then it updates the DU000020 table.

>>DU000020

The DU000020 table is another table that holds the version information for each feature/module per database that is installed. The versions in the DU000020 table will remain at the previous release until all tables in the database have upgraded successfully and the DB_Upgrade table is showing the same version/build for both versions and the db_status is 0 (zero) again. When a user launches into Microsoft Dynamics GP it is the DU000020 table information that is looked at for the DYNAMICS/system database and all company databases. This is different from Dynamics GP Utilities, which looks at the DB_Upgrade table instead.

**Note: If the upgrade of Microsoft Dynamics GP has completed on all databases, but the GP Utilities doesn't go back to the 'Additional Tasks' window, but instead shows a 'Upgrade These Companies?' window, with the company databases listed with green check-marks next to them, it usually means there is something in the DB_Upgrade table that Utilities sees, that it doesn't know how to handle. This could be a version from a prior install that is too old to upgrade and isn't installed, or something like that.

>>duLCK

The duLCK table is used by Utilities to put a lock on a database while it is being upgraded or used in the upgrade process. This prevents users from logging onto the database during the upgrade process and/or making any changes to it. Once Utilities has completed the upgrade of the database, it will remove the record thus releasing the lock on the database. If Utilities is shut down or closed improperly, for example if the machine reboots or Utilities is killed through Task Manager, this record may remain in the duLCK table and when you re-launch Utilities, there will be a 'padlock' symbol next to the database symbolizing that the database is locked and you'll need to close Utilities, remove this record and then re-launch Utilities again before continuing with the upgrade of this database.

>>DU000030

Lastly, is the DU000030 table, or Utilities Fail List table. When Utilities starts upgrading the tables within each database, it looks at the DU000010 as mentioned above to see what tables need upgrading. For every table that GP Utilities attempts to upgrade, whether successful or not, a record is inserted into the DU000030 table with a status for the upgrade. The 'Status' column shows which tables failed to upgrade and which were successful. Some common Status values for this table are:

0 - The conversion ran to completion without error.

2 - The table being converted or deleted was not found.

15 - The table was already converted since the table's structure matches the destination table definition.

25 - The path to the table could not be determined.

70 - Script Error. An error occurred while running a script. **Normally, this error is a result of another table causing this table to fail**

135 - An error occurred, and the table was renamed back to its original name and structure containing any data within it.

***The DU000030 table, for this reason, should always be empty before you launch Utilities for the first time on an upgrade of Microsoft Dynamics GP, whether it be a major version upgrade, service pack, hotfix or any other update that changes the database version information.

     Before launching Utilities to upgrade the DYNAMICS/system database, make sure this table is empty, otherwise it can potentially cause errors with the upgrade by skipping tables during the upgrade process.

     You can empty the DU000030 table by running this script, before you launch Microsoft Dynamics Utilities for the first time to begin the upgrade of the GP databases:

               Delete DYNAMICS..DU000030

***NOTE: The 'Microsoft Dynamics GP - Upgrade Troubleshooting' blog, being released on Sept. 21st, will discuss in more detail how to troubleshoot errors during an upgrade of Microsoft Dynamics GP.

Server vs. Client/workstation - Where should you upgrade?

You can launch Microsoft Dynamics GP Utilities from the server or from a workstation, so because of this, you are not required to install Microsoft Dynamics GP on the SQL Server. The upgrade can be launched from a server or client workstation installation. The only difference may be a slight slowdown as Utilities communicates with SQL Server back and forth, between the two machines, but this does not add much, if any, overhead, however.

Upgrading all companies at once vs. one or a couple at a time:

Upgrading all company databases at the same time is recommended but doesn't make much difference as far as Utilities is concerned, as it will upgrade each database in the same manner regardless. If you choose to upgrade all company databases at the same time, which is default, Utilities will run through all company databases' upgrade process, one after the other, and won't stop until either all company databases have been upgraded successfully, or an error occurs on one of the databases, that requires troubleshooting to move forward.

If you choose to upgrade one or specific company database(s) at a time, Utilities will upgrade those company database(s) and then once it completes, it will go back to the 'Additional Tasks' window in Microsoft Dynamics GP Utilities, and you will need to close Utilities and re-launch it in order to select other remaining company database(s) to upgrade next or it will show an error message if one should be found.

Using a U.S. and another install of a different region/localization:

You may be using both a 'U.S.' install of Microsoft Dynamics GP as well as another install of Microsoft Dynamics GP with a different region/country selected, such as 'UK/Ireland'. We frequently see this done because with different regions selected, comes different additional products you can install with Microsoft Dynamics GP. For example, we don't have the option to install 'Canadian Payroll' on a 'U.S.' install of Microsoft Dynamics GP, but we do have that feature available to us on a 'Canada' install of Microsoft Dynamics GP.

When we see a multi-country environment where we have more than one install of Microsoft Dynamics GP installed each with a different region/country selected, the recommendation is to run GP Utilities from either the 'U.S.' or other country/region install first and update those modules, then you'll also need to run GP Utilities for any additional instances, in order to update any country/region specific modules, until all modules and features are upgraded accordingly.

Continue to check back with our Microsoft Dynamics GP 2022 Upgrade Blog Series Schedule to learn more about the Microsoft Dynamics GP 18.5 Upgrade!

Hopefully the upgrade blogs in this series will again help give you the information you need, as well as any needed troubleshooting steps, to have a stress-free upgrade implementation with Microsoft Dynamics GP, whether you're a seasoned veteran or a rookie new to the upgrade process and Microsoft Dynamics GP!!