Data Upgrade Performance for Microsoft Dynamics AX (Axapta)

1. Application Area

Microsoft Dynamics AX (Axapta) as a very flexible and scaleable product is used by large companies with significant number of items and transactions. For really huge data volumes, the flexibility might start reducing the system performance for some operations. And vice versa, improving performance could reduce the flexibility. Operations of that kind are to be analyzed in order to achieve the compromise between two issues. Product version upgrade is not often used but very important operation for the customer. It must be fulfilled relatively quickly and reliable, during the time when users don’t create new transactions in the system. It often means that the company business activities are to be stopped during the upgrade process. So, the performance of the upgrade process plays one of the most important roles here. Standard data upgrade procedure integrated in Dynamics AX (Axapta) doesn’t fully conform to the performance requirements. So, standard Dynamics AX (Axapta) upgrade procedure was significantly re-implemented in order to increase the performance.

2. Upgrade Conditions

The necessity of alternative data upgrade was caused by the Dynamics AX (Axapta) version upgrade for one of our customers. On the day of upgrade, the database size was in the range of 120-150 Gb. Dynamics AX (Axapta) standard procedure estimated the upgrade job as of several weeks with relatively good hardware. From the experience of smaller upgrade for database of 20 Gb that took around 70 hours the estimation of several weeks seemed to be most likely correct. This duration of the job was not acceptable for the customer. Maximum allowed duration was ordered to be 50-70 hours.

3. Own Data Upgrade vs Standard Upgrade Procedure

Analysis of standard upgrade procedure has revealed that this is exactly the place where the flexibility is not so important and it is possible to neglect it in favour of performance of the job. The decision was made to re-implement several weakest places of the upgrade.

  • Reorganization of upgrade logic of Inventory Dimensions fields for almost all tables.
  • Reimplementation of the most Dynamics AX (Axapta) upgrade procedures using SQL statements, executed directly on server side.
  • Reorganization of upgrade logic of Collection Letters, Project Transactions, and some others.
  • Creating of new Indexes in some tables being upgraded.

Supplementary Activities

  • SQL Server performance optimizations configurations.
  • Dynamics AX (Axapta) companies organization, backup of partly updated database to establish restore points.
  • Dynamics AX (Axapta) tables maintenance to prevent synchronization and upgrade errors.
  • Revising and correcting errors of standard Dynamics AX (Axapta) upgrade procedure.
  • Logging complete detailed upgrade workflow as an addition to standard Upgrade Check List.

4. Dataflow Diagram

Figure 1: Performance optimization

5. Results

Realization of alternative data upgrade procedure made it possible to convert Dynamics AX (Axapta) database with the size of 120 Gb during 20 hours. Performance of separate jobs was increased 20..40 times compared to original estimation of standard upgrade.