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 share a useful link 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.

Database upgrade scripts for D365 Finance and Operations. Sequence of upgrading steps.

Every script must be defined in its scope and order within a step. There are the following attributes to deal with it (ordered by execution order during the step):

1. ReleaseUpdateScriptType::StartScript - Tells Upgrade Framework to schedule the script as a start script at the step. The script will run once for each company (DataArea) in the system at the start of the step.

2. ReleaseUpdateScriptType::PartitionStartScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system at the start of the step. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

3. ReleaseUpdateScriptType::SharedScript - Tells Upgrade Framework to schedule the script as a shared script. The script will run only once. It can be used when “SaveDataPerCompany” and “SaveDataPerPartition” properties on the table accessed by the script are both set to “No”.

4. ReleaseUpdateScriptType::StandardScript - Tells Upgrade Framework to schedule the script as a standard script. The script will run once for each company (DataArea) in the system. It can be used when “SaveDataPerCompany” property on the table being accessed by the script is set to “Yes”.

5. ReleaseUpdateScriptType::PartitionScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

6. ReleaseUpdateScriptType::FinalScript - Tells Upgrade Framework to schedule the script as a final script at the step. The script will run once for each company (DataArea) in the system at the end of the step.

7. ReleaseUpdateScriptType::PartitionFinalScript - Tells Upgrade Framework to schedule the script as a partition script. The script will run once for each partition in the system at the end of the step. It can be used when “SaveDataPerPartition” property on the table being accessed by the script is set to “Yes”, and “SaveDataPerCompany” property is set to “No”.

Database upgrade scripts for D365 Finance and Operations. Upgrade steps overview.

The standard application code has many standard data migration scripts. Their main purpose is to migrate data for standard tables. 

The process of data migration has different steps which include Pre-req, Pre-sync, Database sync, Post-sync, Final database sync, stopping and starting various services and many more.

Below you will find a short description of the key steps.

• Pre-req - At this step, the upgrade framework prepares the source database for an upgrade. This step patches the SQL dictionary, applies SQL sequences instead of system sequences, modifies user info and system variables, synchronizes system tables of the database, and performs an additive synchronization of new tables. 

• Pre-sync - At this step, the upgrade framework executes Pre-synchronize upgrade scripts to make metadata changes before step “synchronize tables”. 

For example, pre-synchronize scripts map the database table or fields on the source Microsoft Dynamics AX system to the database table or fields in the target system. This table-to-table and column-to-column mapping prepares the source-to-target upgrade model for data copy from source to target. Other kinds of pre-synchronize scripts disable unique indexes in preparation for data upgrades and avoid duplicating key errors. This action is undone during Post-synchronize step.

In other words, at this stage, there is the full set of metadata of both versions. The database contains tables from Ax 2012 and their data as well as the full set of new D365 tables. During this step, you can copy data from Ax 2012 tables and fields to D365 if the table or field was renamed in the version. When this step is completed, the database data should be whole from a D365 application perspective. Otherwise, you will see synchronization errors at the next stage.

In case it is needed to write scripts to move data from specific Ax 2012 fields to the appropriate D365 field and tables it can be done via direct SQL command because Ax 2012 fields do not exist in D365 version from a D365 code application perspective.

• Database sync - At this step, the upgrade framework executes the database synchronization with  the current application code. When this step is completed all tables that do not exist in D365  application will be deleted and their data will be lost.

• Post-sync –  At this step, the upgrade framework executes post-synchronization upgrade scripts that contain the bulk of the data upgrade scripts. The database contains the fields and tables from D365 version only. So all data from Ax 2012 was lost at this step. 

Usually, company-specific business data is upgraded in post-synchronization upgrade scripts. Post-synchronization also reverses the metadata changes (disables unique indexes) that were made during the pre-synchronize step. During post-synchronization, upgrade scripts run to update existing data when required and populate newly created tables and columns. Running upgrade scripts and any inter-dependencies are controlled by the upgrade framework. Upgrade scripts are implemented for any major version change.

At this stage, it is possible to write scripts to manipulate migrated data in D365 tables via code or SQL commands.

• FinalDBSync – This step does the final database synchronization that synchronizes all remaining objects in the database.

D365 Finance and Operations disable the flight feature in a Tier-1 environment.

When we recently were updating one of our Tier-1 environments to 10.0.31 version we faced an issue with WHS mobile app.

The mobile app reverted to the login screen after trying to go to a specific menu. We tried the following options in order to resolve the issue:

• Delete all user sessions from Warehouse management > Inquiries and reports > Mobile device logs > Work user sessions.
• Delete and recreate the mobile app user configured/used for your tests (Warehouse management > Setup > Worker)

After an investigation we realized that the issue was rooting in Issue 704649 - Warehouse mobile app gives error "The size of the XML request exceeds the maximum valued allowed". This code changes controls with the flight WHSMobileAppXMLSizeValidationFlight.

So we decided to disable this flight by using the SYSFLIGHTING table. The procedure is nicely described in an old post I found here, I will adjust the procedure to our case.

For this purpose it is needed to add a record with the field Enabled = 1 for the kill switch for the flight, meaning for WHSMobileAppXMLSizeValidationFlight_KillSwitch.

Below specific steps to follow to enable the kill switch for the flight:

1. Add a record with this Insert statement for SYSFLIGHTING table, but please replace the appropriate values:

INSERT INTO SYSFLIGHTING VALUES ('FlightName', 1, 12719367, Partition, RecID, 1)

Note: After replacing 'FlightName' with the actual flight name, the values are/should be:

- 1 stands for Enabled

- 12719367 is the Flight service ID

- Partition = partition ID from your environment which can be obtained by querying (select) for any record. Every record will have a partition id which must be copied and used here.

- RecID = same ID as partition(*) (If there is no other record in SYSFLIGHTING table, then it can be one. Or you can can find one by executing "SELECT max(recid)+1 from SysFlighting")

- 1 is for the RecVersion

- actually, the SQL query could also be much simpler, like this

INSERT INTO SYSFLIGHTING ([FLIGHTNAME],[ENABLED],[FLIGHTSERVICEID]) VALUES ('WHSMobileAppXMLSizeValidationFlight_KillSwitch', 1, 12719367)

2 Verify that C:\AOSService\webroot\web.config has the correct DataAccess.FlightingServiceCatalogID. You should find a line with this key and a value of 12719367. If not, update accordingly the file and save it.

Notes:

Having web.config file in the environment is a clear indication that key DataAccess.FlightingServiceCatalogID should have value 12719367 and this value should be used also in the SQL statement from above for the FLIGHTSERVICEID field value.

If web.config file is not present in the environment, but AXService.config file is present (see next note), it is a clear indication that environment is like an On-Premise deployment (which might be the case also for dev boxes or environments that are not Microsoft managed, but still on Microsoft cloud). In this scenario, see the next note with important differences.

Notes: (not having web.config file in the environment):

Verify that C:\ProgramData\SF\AOS_182\Fabric\work\Applications\AXSFType_App641\AXSF.Code.1.0.20200715180202\AXService.config has the correct DataAccess.FlightingServiceCatalogID. You should find a line with this key and a value of 0. If not, update accordingly the file and save it.

The AXService.config file path might be different (accordingly to your installation).

Having AXService.config file in the environment is a clear indication that key DataAccess.FlightingServiceCatalogID should have value 0 and this value should be used also in the SQL statement from above for the FLIGHTSERVICEID field value.

So, the SQL query should be like this:

INSERT INTO SYSFLIGHTING ([FLIGHTNAME],[ENABLED],[FLIGHTSERVICEID]) VALUES ('WHSMobileAppXMLSizeValidationFlight_KillSwitch', 1, 0)

3. Restart IIS and batch service

P.S. The issue was reported to Microsoft and it can be solved in 10.0.33 version.

D365 Finance and Operations. How to get the RecId sequence of the table via X++.

In D365 RecID there is a SQL system sequence now. SQL itself maintains the IDs. I found a post on this topic. 

Using SQL commands described there, I created an X++ method in order to get the sequence name of the table.

It can be useful if you develop a data upgrade script and it is needed to align the sequence when data is moved ‘as is’ to the target D365 table.

public str getSequenceProcedureName(TableName   _tableName)

{

   str         result;

   str         ret;

   TableId     tableId;

 

   if (this.isTablePresent(_tableName))

   {

       str sqlQuery = strFmt(@"

                SELECT ID from TABLEIDTABLE

                    JOIN sys.sequences ON sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)

                    WHERE TABLEIDTABLE.NAME = '%1'",

                    _tableName);

 

       result  =  ReleaseUpdateDB::statementExeQuery(sqlQuery);

       if (result)

       {

          tableId = str2Int(result);

 

          if (tableId)

          {

             ret = strFmt('SEQ_%1',tableId);

          }

       }

    }

 

    return ret;

}

D365 Finance and Operations. How to get the numeric value of the extensible enum via X++.

There are 2 enum types in the system from an extensibility perspective – extensible and non-extensible. The difference is briefly explained here. There is a short recap below:

In D365FO enums that are not extensible keep the numeric value of each element in the metadata. 

However, extensible enums do not use metadata to store their numeric values. Instead, these values are stored in the database and are assigned dynamically by the database synchronization process.

The ENUMIDTABLE table stores the base enum ids, while the ENUMVALUETABLE table stores the numeric values assigned to every element of the base enum. While the ENUMIDTABLE table contains records for every base enum in the system, ENUMVALUETABLE is only populated for extensible enums.

In case we need to get the numeric value of the extensible enum the following method can be useful:

public Integer getEnumValue(str     _enumName,

                            str     _enumValueName)

{

   Integer     ret;

 

   if (_enumName && _enumValueName)

   {

      str sqlQuery = strFmt(@"SELECT ENUMVALUE from ENUMVALUETABLE

                            join ENUMIDTABLE on enumid = id

                            and ENUMIDTABLE.NAME    = '%1'

                            and ENUMVALUETABLE.NAME = '%2'",

                            _enumName,

                            _enumValueName);

 

       str result   =  ReleaseUpdateDB::statementExeQuery(sqlQuery);

       if (result)

       {

           ret = str2Int(result);

       }

   }

      

   return ret;

}