Database upgrade scripts for D365 Finance and Operations. Troubleshooting data upgrade script errors in a development environment.

Overview

When you run a data upgrade process it can end with errors and it would be nice to know the reasons. I’d like to sharea couple of really useful links (here and here) that I have been using for my investigations. In addition, I would like to share my personal experience as well.

If the data upgrade runbook fails, you can see the following message on your screen:

If you use the query on the AXDB database to determine which step the upgrade servicing is on, you can get the following results, for instance:

In this case, it would be good to know the reason for the error. There are different options for this purpose.

Review the runbook logs

Logs should be located in a sub-folder under the deployable package. Drill into the logs folder to find the logs for the runbook step you're on, and review errors. In our example for the third step, it can be:

C:\....\DataUpgradePackage\RunbookWorkingFolder\MajorVersionDataUpgrade-runbook\localhost\AOSService\3\Log

When you open “dataupgrade_err” file you will find a system error message and this information can be used for the investigation. Some of the issues are well known and there is a solution for them.

The most common reason is various types of synchronization errors. There is an example below:

In order to fix such errors it is needed to populate data by properly upgrading scripts during the data upgrade process. 

Also, you may see an error like:

Cannot create a record in Release update scripts (ReleaseUpdateScripts). Class ID: 23613, “The method name”.

The cause of this error – there are 2 class methods with the same name among “ReleaseUpdateDB” classes.

If you see an error like:

Cannot execute the required database operation. The SQL database has issued an error.

Usually, the cause is a wrong data upgrade script and the log file contains enough details to find the reason.

View details about a script error via SQL query

To upgrade scripts run in X++ using a batch process that the runbook installer starts. In Application Explorer in Visual Studio, some classes you can see prefixed with ReleaseUpdate. Let’s say an upgrade script fails during the runbook process. In that case, you can learn more about the reason for the error by opening Microsoft SQL Server Management Studio and running the following code to query ReleaseUpdateScriptsErrorLog.

SELECT * FROM [AXDB].[dbo].[RELEASEUPDATESCRIPTSERRORLOG]

View details about a script error via Event Viewer

If you face an error during database sync or in Sync and Post Sync scripts, you can go to Windows Event Viewer and find the errors in the appropriate section. 

Database sync and final database sync details are logged in section Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX DatabaseSynchronize.

Sync and Post Sync script details are logged in section Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX DataUpgrade.

Skip failed scripts

You can skip all scripts that have failed a number of times and move to the next viable scripts. This functionality helps with the troubleshooting process. This process is 100% manual, so you're less likely to skip the scripts unintentionally.

In ReleaseUpdateConfiguration table, there is a field ScriptRetryCount. The value in this field controls how many times the runbook process will rerun scripts before it ignores them. When the runbook is running, the system updates ReleaseUpdateScriptsErrorLog.ErrorCount field every time a specific script fails. A new row is created for each script.

In DataUpgrade package folder, under ..\AosServices\Scripts\, (In our example C:\...\DataUpgradePackage\AOSService\Scripts) there is a script IgnoreBlockingScripts.ps1. Run this script from an Administrator Windows PowerShell window to skip all scripts where ScriptRetryCount=ErrorCount. Then rerun the failed runbook step so those scripts will be ignored. ReleaseUpdateScriptsErrorLog. The ignored field will also be set for each script that is skipped. Therefore, you can easily identify skipped scripts later.

Let’s have a look at the example. Data upgrade script «*******_Invent» has an error. Data upgrade process failed. If we query ReleaseUpdateScriptsErrorLog table:

SELECT * FROM [AXDB].[dbo].[RELEASEUPDATESCRIPTSERRORLOG]

We can get a result:

If we rerun the runbook and query ReleaseUpdateScriptsErrorLog table one more time, we will get the result:

In order to ignore «******_Invent» script we need to run IgnoreBlockingScripts.ps1 script from .\AosServices\Scripts folder (In our example C:\...\DataUpgradePackage\AOSService\Scripts)

NOTE: The script must be executed with administrative permissions; otherwise, it won’t work.

Before running the script, $webroot variable should be specified. Its value should be equal to the path of AOSService website. On the screenshot below the option for the Tier-1 machine is presented:

When all necessary changes are done, the script can be executed. If the script was executed correctly, ReleaseUpdateScriptsErrorLog.The ignored field will be set to “1”.

After that, the failed runbook step can be rerun via -rerun step command.