We’re at that time of year again where many partners and customers are beginning to look at the Microsoft Dynamics GP 2018 R2 and upgrading the databases to that version.
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 2018 Upgrade blogs that we've been releasing since Sept. 4th:
--Microsoft Dynamics GP 2018 R2 and Year-End Update Upgrade Blog Series Schedule
--Microsoft Dynamics GP 2018 R2 and Year-End Update: Lifecycle and Upgrade Services
--Microsoft Dynamics GP 2018 R2 and Year-End Update: Upgrade Planning - Let's Get Started
--Microsoft Dynamics GP 2018 R2 and Year-End Update - Known Upgrade Issues
--Microsoft Dynamics GP 2018 R2 and Year-End Update - Version Checks & Upgrades
If you haven't already looked through these blogs, I would highly recommend it if you are beginning to think about upgrading to Microsoft Dynamics GP 2018 R2 or any other GP upgrade.
I would also recommend looking through the 'System Requirements for Microsoft Dynamics GP 2018' page and the 'Upgrading to Microsoft Dynamics GP 2018' hot topic as well, as these will also be updated with the Dynamics GP 2018 information:
>>System Requirements for Microsoft Dynamics GP 2018:
>>Microsoft Dynamics GP 2018/R2 Product Download page / Dynamics GP 2018 DVD:
>>Upgrading to Microsoft Dynamics GP 2018/R2 hot topic:
>>Microsoft Dynamics GP 2018 Upgrade Instructions guide:
Database Upgrade - The Basics!
The upgrade to Microsoft Dynamics GP 2018 R2 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. Setup your test environment so you can run a test upgrade of Dynamics GP 2018 R2 first. This way, you'll 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 upgrade.
2. Review the upgrade manual and hot topic for the latest known issues and pre-upgrade steps and scripts. The hot topic will also tell you what build of Microsoft Dynamics GP 2018 R2 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 2018 R2.
**NOTE: You can only upgrade to Microsoft Dynamics GP 2018 R2 from these version/builds:
--Dynamics GP 2015 R2:
14.0.1059 or higher
--Dynamics GP 2016
16.0.0620 or higher
--Dynamics GP 2018
18.00.0400 or higher
This information is also covered in the ‘Upgrading to Microsoft Dynamics GP 2018’ hot topic mentioned above, and you’ll want to refer to the hot topic for continually updated information as more builds of Microsoft Dynamics GP 2015 and GP 2016 are released, as well as Microsoft Dynamics GP 2018.
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 2018 R2 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 topic for ‘Upgrading to Microsoft Dynamics GP 2018 R2’ 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 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:
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.
4. 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.
5. Obtain all the Microsoft Dynamics GP 2018 R2 product download DVD and patch files that you will need for the new version.
6. Obtain all the code and installs for any third-party or integrating products being used with Microsoft Dynamics GP.
7. Install Microsoft Dynamics GP 2018 R2 to include 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.
8. Launch Microsoft Dynamics GP 2018 R2 Utilities and after the version check, 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.
9. 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.
10. Upgrade any modified forms and/or modified reports dictionary files that you have, if any. The 'Modified Dictionary Upgrade' blog is being released Sept. 13th, so look for that to find additional information on this process.
11. Lastly, login to Microsoft Dynamics GP 2018 R2 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 either Microsoft Dynamics GP 2015 or Microsoft Dynamics GP 2016 to Microsoft Dynamics GP 2018 R2.
Database Upgrade Tables:
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.
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.
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.
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.
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 upgraded, 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:
***NOTE: The 'Microsoft Dynamics GP 2018 R2 - Upgrade Troubleshooting' blog, being released on Sept. 18th, 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 2018 R2 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 different 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 company database at a time, Utilities will upgrade that company database 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
Continue to check back with our Microsoft Dynamics GP 2018 R2 Upgrade Blog Series Schedule to learn more about the Microsoft Dynamics GP 2018 R2 Upgrade!
Please click here to watch the video: VIDEO!! VIDEO!!