Additional guidance to database upgrade scripts for D365 Finance and Operations. Part 1. General recommendations.

Introduction

As you may know, developing your own data upgrade scripts is something one can do. There are several posts related to this topic in my blog. Also, you can find documentation on this topic. This time, I would like to share my notes and experience with the technical aspects of development and execution of scripts. Some of the points are not described on the Microsoft Docs website.

Data upgrade methods name convention

All methods with data upgrade attributes must have unique names in the system. If you create 2 methods with the same names in the same module in different AXPUPGReleaseUpdate* classes the compiler will not show errors. You will get an error during the data upgrade execution:

Failed operation step '/DataUpgrade/PreSync/ExecuteScripts/ScheduleScripts' 

Cannot create a record in Release update scripts (ReleaseUpdateScripts).

Class ID: YOUR CLASS ID, METHOD NAME.

The record already exists.

   at Microsoft.Dynamics.Ax.MSIL.Interop.throwException(Int32 ExceptionValue, interpret* ip)

   at Microsoft.Dynamics.Ax.MSIL.cqlCursorIL.insert(IntPtr table)

   at Microsoft.Dynamics.Ax.Xpp.NativeCommonImplementation.Insert()

Also, if your methods have unique names, it’s easier to find them in the logs, if we also consider tracking.

Data upgrade script execution isolation

Technically each data upgrade method will be considered a separate batch task. It means that data upgrade scripts are executed separately and "don't know" about each other. If you didn't specify dependencies between data upgrade scripts you cannot be sure about their execution sequence. It is good to know this approach if you are going to develop complex data upgrade scenarios.

Using configuration keys

When the data upgrade is triggered the configuration keys in D365 Finance and Operations will be activated based on the "Enabled by Default" property. If there is a case when the configuration key is not enabled by default however it is needed to use the business logic covered by this key it is possible to enable the required key via code.

ConfigurationKeySet keySet = new ConfigurationKeySet();

SysGlobalCache      cache = appl.globalCache();

 

keySet.loadSystemSetup();

keySet.enabled(configurationKeyNum(RetailCDXBackwardCompatibility), true);

 

SysDictConfigurationKey::save(keySet.pack());

 

// Call SysSecurity::reload with the following parameters:

// _configurationChanged: true,

// _allowSynchronize: false,

// flushtable: true,

//_promptSynchronize: false,

//_syncRoleLicensesOnConfigurationChange: false

SysSecurity::reload(true, false, true, false, false);

Based on the previous point regarding the data upgrade script isolation it is needed to enable the required configuration key in each data upgrade method for the tables and fields under the disabled by default configuration key in order to be on the safe side.

I had several cases when I needed to develop data upgrade scripts for the functionality with disabled configuration keys by default. When I enabled the configuration key in one method and did not enable the same key in another method, I got wrong results from a data consistency perspective.

General performance guidelines

You can find some points copied from the guide related to the Ax 2012 version and adjusted to the D365 version below. In fact, most of these recommendations also apply to D365. Since the performance is a critical part of the upgrade process I believe it’s a good idea to highlight these points once again.

In fact, most companies will perform this task over a weekend, so the entire upgrade process must be able to be completed within 48 hours. 

When you develop a new script, please try to apply it to your upgrade script:

• Use record set functions whenever possible. If the script performs inserts, updates, or deletes within a loop, you should consider changing the logic to use one of the set-based statements. If possible, use these set options to perform a single set-based operation.

• If your script runs delete_from or update_from on a large table where the delete() or update() methods of the target table have been overwritten, the bulk database operation will fall back to record-by-record processing. To prevent this, call the skipDataMethods(true) method to cause the update() and delete() methods to be skipped. Also, you can call the skipDatabaseLog(true) method to improve performance.
• If the business scenario cannot be written as insert_recordset, consider using the RecordInsertList class to batch multiple inserts to reduce network calls. This operation is not as fast as insert_recordset, but is faster than individual inserts in a loop.

• Break down your scripts into smaller pieces. For example, do not upgrade two independent tables in the same script even if there is a pattern in how the scripts work. This is because:

• Each script, by default, runs in one transaction (=one rollback segment) separately. If the segment becomes too large, the database server will start swapping memory to disk, and the script will slowly halt.
• Each script can be executed in parallel with other scripts as it was mentioned above.

• Take care when you sequence the scripts. For example, do not update data first and then delete it afterward.

• Be careful when calling normal business logic in your script. Normal business logic is not usually optimized for upgrade performance. For example, the same parameter record may be fetched for each record you need to upgrade. The parameter record is cached, but just calling the Find method takes an unacceptable amount of time. For example, the kernel overhead for each function call is about 5 ms. Usually, 10-15 ms will elapse before the Find method returns (when the record is cached). If there are a million rows, two hours will be spent getting the information you already have. The solution is to cache whatever is possible in local variables.

• If there is no business logic in the script, rewrite the script to issue a direct query to bulk update the data.