emeadaxsupport: Writing Data Upgrade Scripts Part 1: Understanding the components of the process

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...e-process.aspx
==============

This post is the first of a series of entries that will help build knowledge of how to write data upgrade scripts in the most effective manner. This first post will highlight the key terminology involved and the typical tasks that a developer should be going through as they think about migrating custom data to Microsoft Dynamics AX 2012. Future posts will be walkthroughs of the most common examples of creating data upgrade scripts from start to finish. The first two examples will be upgrading a table with a financial dimension and upgrading a table with address information. Please feel free to comment on the blog with other examples you would like to see after that.

Recommended reading prior to this blog

In order to keep the size of the blog entries a little more manageable, I plan on trying to enhance existing content and bring out the key points of the process rather than repeat concepts described in our published documentation. The following documents contain much more detailed information and definitions of key terms, and should be used for reference and/or read ahead of time:

How to Write Data Upgrade Scripts for Microsoft Dynamics AX 2012

Microsoft Dynamics AX 2012 Upgrade Guide

Understanding the standard flow for creating data upgrade scripts

Most customers who use Microsoft Dynamics AX will have modified the data structure in some way in order to meet the needs of their business. With the upgrade to Microsoft Dynamics AX 2012, the database normalization that was done by Microsoft with the core Dynamics AX tables will likely require some level of upgrade script to be considered for some of these custom table changes which exist in our customer's environment. For each new/customized table, developers are going to need to go through the following checklist to determine the impact to upgrading to Microsoft Dynamics AX 2012

1. Does the process that is using this new/custom table still have relevance in the Dynamics AX 2012 environment
2. Were there any core Dynamics AX 2012 changes that will require me to adapt my data structure
3. What will this table schema look like within Dynamics AX 2012
4. Does this table require any update scripts or will it map automatically through the BCP process - if the answer is that no upgrade scripts are required, then the rest of the list can be ignored
5. What type of upgrade scripts are required - validation, source transformations, target processing, etc.
6. What dependencies exist for my upgrade script - which scripts do I require to be processed first and which must wait until this set of scripts executes
7. Do configuration keys have any impact on the functionality required for the upgrade script
8. How do I validate the success of the upgrade scripts throughout the process

Next, we will explore each of those checklist items in more detail, keeping in mind that this type of analysis is required for every new or customized table in the system.

Is this table still relevant?

With the massive amount of change and new functionality introduced with Microsoft Dynamics AX 2012, every developer should take the opportunity to review their customizations. They should evaluate if a particular customization or modification to a process is still required or if the core product now has enough functionality to meet the needs of the business.

Evaluating need to adapt to a change in Microsoft Dynamics AX 2012 functionality or database schema

Once the decision has been made to maintain a customization, the developer's next action is to investigate if any changes to the core Dynamics AX database schema or functionality will have any effect on the new/modified table. From a developer's perspective, the best source of information about the most common areas of change can be found by reviewing the documents found on MSDN related to Microsoft Dynamics AX 2012 White Papers: Code Upgrade.

The documents on MSDN are categorized by feature area, and will generally describe key schema changes, new or modified class/function usage descriptions, and impact to the data upgrade process. Some of the most common examples of change will be with financial dimensions/accounts and with how we store address data.

If the developer is investigating how to upgrade a change to an existing Dynamics AX table, there is a very strong likelihood that the schema of that table has changed. The developer will need to determine if their custom fields should remain in the existing table or need to be moved to another table - one common example will be modifications to the LedgerTrans table, which no longer exists, and the InventTrans table, which was split into multiple tables. Whitepapers found at the link above describe the changes that were made by Microsoft and suggest how developers should adapt their code.

Define the table schema for Microsoft Dynamics AX 2012

After determining that the table structure needs changing, the next step in this process is to define the new table schema in the Dynamics AX 2012 development environment. This should be done as part of the overall code upgrade process that is described in the Upgrade Guide and other Code Upgrade Whitepapers that have been linked previously in this article. The general practice would be to take any existing fields which are going to change radically and to rename those fields as DEL_ fields and leave them in the table. Then add the new replacement fields to the table definition. Using a configuration key that flags the DEL_ field as an upgrade object is also a common practice.

For example, if the custom table stores a financial dimension array field "Dimension" in Dynamics AX 4.0/2009, we would typically change the name of that field to DEL_DefaultDim in Dynamics AX 2012 and add a new DimensionDefault type of field such as DefaultDimension as the replacement field which would then relate back to the new DimensionAttribute structures.

Are upgrade scripts required or is BCP enough?

The upgrade framework allows for the automatic migration of data through BCP where the data structures have not changed. The following scenarios REQUIRE that some form of upgrade script is required:

• Changing name of field/table when the field/table ID also changes
• Deleting a table/field and choosing to NOT save the data in a DEL_ field or table
• Adding to/changing a unique index or converting a non-unique index to unique
• Moving data from one field to another, within or outside the same table
• Populating new fields/tables with existing data or a non-empty default value

The following scenarios are all covered by the BCP mapping process and do not require an upgrade script to perform the task:

• Change name of field/table when the field/table ID does not change
• Adding a new field to a table using the default value for the datatype (0, blank)
• Adding or changing table relations, delete actions
• Adding or changing non-unique indexes
• Modifications of any kind to a temporary table

Identifying which data upgrade scripts are required

After evaluating the scenarios above, if the table requires an upgrade script the next phase is to determine what types of upgrade scripts are required. The best advice in determining which type of upgrade scripts are required and also to get an idea of what the content of those scripts should be is to find an existing example of core Dynamics AX upgrade logic that does something similar to what you are attempting. For example, if your table change is to remove a ledger dimension array and replace it with a DefaultDimension refRecID value, look at the scripts that were created to validate and upgrade the Dimension array field within CustTable.

The following list highlights the key times to use each kind of script:

Upgrade Readiness scripts - these scripts run at the beginning of the process to provide validation of the data within a table. Common examples are to make sure that a ledger account, dimension, customer account, etc. which are stored still exist in the master table within the company. Known data patterns which will cause issues can also be highlighted through the process.

Live and Delta Preprocessing scripts - The most common scripts that are written are the scripts which will transform the data from the Dynamics AX 4/2009 state into the required data schema for Dynamics AX 2012. These scripts do not modify existing data directly. Instead, they rely on the use of shadow tables or brand new tables to store the new value that will flow into Dynamics AX 2012.

The live preprocessing scripts are designed to run once to create the bulk of the new data in the system. The delta preprocessing scripts will perform the same type of activity as the live scripts, but will be coded in a way to detect and process any changes in the data that have occurred since the live preprocessing was performed.

Single user scripts - the upgrade framework will automatically schedule a series of delta preprocessing scripts to run during the single user downtime window to pick up any final changes to the records in the database. Single user scripts may be required if there are certain activities which would require total control of the records being modified, which should be very rare.

Dynamics AX 2012 Pre-synchronization scripts - these scripts will run against the Dynamics AX 2012 database before the BCP process copies the previous data into the Dynamics AX 2012 system. This means that there should be no activity that processes data in these scripts.

The primary uses for these scripts are to disable unique indexes which are new or changed from the previous version or to create special table mappings for the BCP process. Some examples of those special mappings would be to ignore an unused field or table, to map field/table values when the name and ID have changed.

Dynamics AX 2012 Post-synchronization scripts - these scripts are scheduled to run after the tables used by the scripts have been populated by the BCP transfer process. The primary uses of these scripts would be to manipulate the data in a table to allow it to conform to a new or changed unique key or to make updates to tables that could not occur until the source data was transferred to Dynamics AX 2012.

Defining dependencies for the upgrade scripts

Another critical piece of designing upgrade scripts is to determine if there is any dependency to a different table or upgrade script. For example, if the upgrade script that is being written relies on data from a different table to already have been processed, such as the new financial dimension tables, a script dependency must be added to the upgrade script. These dependencies can be created to cause the upgrade script to wait for a single method, a particular table or a full class of methods to finish.

Configuration key impact on upgrade scripts

Configuration keys can also force changes to upgrade scripts. In Dynamics AX 4 and Dynamics AX 2009, turning off a configuration key removed fields and tables from the database. A generic upgrade script must check if any table or field specific configuration key is enabled before trying to refer to that field in a script. This is done through the use of isConfigKeyEnabled() checks in Dynamics AX 4/2009 and through a ConfigurationKeyAttribute in Dynamics AX 2012.

Validating success of the upgrade scripts

The last item listed, but by no means the least important, is to understand how you will be testing your upgrade scripts for success or failure. Tests should exist at each stage of the process, and here would be some common examples:

Upgrade readiness - test that the validations were successful and that you have bad data to test your validation errors

Live/Delta preprocessing - does all the right data get into the shadow table and if you make changes to the existing records do your delta scripts pick up those changes successfully

Single user processing - validate whatever action you are attempting

2012 Presynchronize scripts - validate that they create the custom mappings, disable uniqueness on keys, etc.

BCP data transfer - make sure that all fields get transferred appropriately

2012 Postsynchronize scripts - validate that all data gets processed by script and any attempts to make the key unique again are successful.





Author - Kevin Kidder

Date posted - 2/9/2011




Источник: http://blogs.msdn.com/b/axsupport/ar...e-process.aspx