Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

Planning your data upgrade to Microsoft Dynamics AX 2012

Planning your data upgrade to Microsoft Dynamics AX 2012

Rate This
  • Comments 9

 

Hello all - I wanted to give out a sample look at some additional documentation that we have recently released to help build understanding of the planning required to have a successful upgrade to Microsoft Dynamics AX 2012. This section talks about some of the core items to think about as you prepare to create a project plan for the data upgrade. The Microsoft Dynamics AX 2012 Data Upgrade Best Practices document has been released and can be found at (http://go.microsoft.com/fwlink/?LinkId=238709) has specific performance tuning information for the various phases of the data upgrade. In the near future, I will be posting other content around upgrade in more specific areas, such as issues commonly found with virtual companies. If you have specific topics of interest, drop me a mail or leave a comment and I'll do what I can to help build the knowledge around upgrade around our community.

Kevin Kidder

Understanding the importance of planning

Any project involving a data upgrade to Microsoft Dynamics AX 2012 is a very large and complex undertaking, regardless of the size of the source database being upgraded. Some of the primary variables that drive the complexity of the data upgrade include:

  • Amount of customizations, especially modifications or relations to core Microsoft Dynamics AX tables
  • Use of virtual companies to share data
  • Custom code and/or tables that interact with General Ledger, Inventory and Global Address Book
  • Large catalog of inventory items and/or inventory dimension combinations (color/size/etc.)
  • Complexity of security structure

Preparing for a Data Upgrade

Many of the individual lessons that follow have specific performance suggestions that require changes to settings to improve efficiency. The following guidelines listed here are more general in nature, and help define a plan for the upgrade prior to beginning the process.

Start investigations required for code upgrade.  Before you begin your data upgrade you must begin analyzing all customizations and create plans for your code upgrade.  All customizations should be evaluated to see if they are still necessary in Microsoft Dynamics AX 2012, or if a complete refactoring is required. This includes working with your Value Added Reseller (VAR) and Independent Software Vendors (ISVs) to ensure they have the necessary upgrade scripts in place for your upgrade. 

The planning must include writing data upgrade scripts required for your customizations in order to proceed with the various sections of the data upgrade. Examples of the types of upgrade scripts required for the various phases are:

  • Readiness checks - it is very important to consider data readiness checks for all custom tables that interact with core Dynamics AX tables. Any type of Ledger account or Dimension, Address information, inventory dimension information, etc. should always have a readiness script written to check for existence of the related data.
  • Preprocessing and delta scripts - these scripts are required to start to prepare the application data for upgrading by creating shadow tables to map any new fields and assign key relations to the new ledger, inventory and address data that is required for custom fields and tables. These scripts also should be used to facilitate a change for most of the relationships between custom tables and core Microsoft Dynamics AX tables to use RecIDs instead of the typical string ID value.
  • Single user steps and all target side operations - the code upgrade should be fully complete by the time of single user processing and target side processing starts. The only exception would doing a test upgrade for the purpose of migrating data for developer testing, but that can only occur after all data upgrade scripts are written. The data upgrade scripts on the target side must include all table/field mapping information in order to proceed.

Purge and Archive data with the Dynamics AX Intelligent Data Management Framework (IDMF). Prior to upgrade is an excellent time to consider purging and or archiving data from the production Dynamics AX database.  Any data purging or archiving must be in line with your data retention policies. See CustomerSource for the tool download information. (https://mbs.microsoft.com/customersource/downloads/servicepacks/ax_idmf.htm?printpage=false)

Analyze space requirements for databases: Prior to running the data preparation and preprocessing scripts on the source version, the size of the source SQL database and transaction logs should be increased considerably (by at least 35%) in order to ensure enough space to prevent costly database resizing during these operations. Investigating the actual growth on a test run will give a much better idea of how much additional space should be allocated to the database and log files.

A similar investigation needs to be made to determine the proper size for the Microsoft Dynamics AX 2012 database. A rough estimate to use as a starting point for determining the size would be 30% over the expanded size of the source database.  

The TempDB database size should also be increased and monitored during a test run for performance and recommended sizing for the upgrade. A rough estimate for sizing your TempDB database would be 20-25% of the expanded Microsoft Dynamics AX 2012 database. Optimal performance may require splitting the TempDB database into separate files. See the article Optimizing tempdb Performance on MSDN for more suggestions.

Microsoft Dynamics AX 2012 does not support Oracle.  To upgrade an Oracle database, use the Oracle to SQL migration tool (on Microsoft Dynamics AX 4 or Microsoft Dynamics AX 2009) first then upgrade the SQL database to Microsoft Dynamics AX 2012. The migration tool can be found here on CustomerSource:

(https://mbs.microsoft.com/customersource/downloads/servicepacks/AX2009_OracleToSQL)

Executing a successful data upgrade

The key to having an optimal data upgrade experience is to plan well in advance, run multiple test cycles building on lessons learned, and then plan the live data upgrade in complete detail which builds in time for unexpected last minute issues.

  • ·         Resolve all production readiness errors. It is strongly recommended that all readiness errors are resolved on the production system (including warnings and advisory errors). Readiness scripts also need to be written and executed where appropriate for modified and custom tables.
  • ·         Define a robust strategy for creating backups or database snapshots before each step of the upgrade process. These can greatly improve your efficiency in running multiple test passes during the testing phases, and are a necessity while running through each of the steps of the live upgrade downtime window in case of unexpected errors (out of disk space, network failures, etc.)
  • ·         Use all available performance tools to assist in debugging performance slowdowns (e.g. Performance Monitor, SQL DMVs, Performance Analyzer for Microsoft Dynamics, etc.) This performance evaluation should be done for all phases that rely on batch processing, and should be revisited with each test run after appropriate adjustments are made.
  • ·         Prepare to actively monitor the system for performance bottlenecks through all stages that rely on batch processing. During all test runs, use the SQL DMVs or the Performance Analyzer for Microsoft Dynamics to identify areas where index tuning or code changes are needed - in some cases, an index created on the fly within the test environment can provide immediate benefit to a long running process.
  • ·         Understand the usage of the State Transfer Tool that ships with the database upgrade code to take advantage of work done on a test system. This process is outlined later in the document.
  • ·         Before running the final upgrade on the live system, plan for at least two successful complete test runs in order to prevent last minute problems that could derail the entire upgrade and force a long postponement. In order to get to this state, you will execute multiple test runs before the final two successful test runs. Those preliminary test runs are used to find errors, understand how to optimize performance, etc. and allow you to create the smallest possible downtime window for the upgrade.  These full test runs should include at a minimum the following steps:
    • o   Running single user mode processing in the source database
    • o   Launch Data upgrade cockpit functionality on the target.
    • o   All steps after the Data upgrade cockpit on the target checklist.
    • o   Security setup - roles and privileges should be created ahead of time as part of the code upgrade, but user and role associations must be populated or imported from a previous test database
  • ·         For every test run, it is important to document all of the findings identified in each phase. Those details should be incorporated into an overall project plan or addressed within the database or application prior to running the next test cycle.
  • ·         Start planning for user acceptance testing on a test copy of the upgraded dataset and incorporate that into the end user training cycle time. The user acceptance testing should be outlined in a clear, well-defined project plan. Making the project plan as thorough as possible is key, and has to include all daily activities, period end activities, and all critical integration points with other applications/processes. Another key aspect is validation of key amounts after the upgrade, such as master record balances, and investigating that all types of data migrated successfully. Failure to define a valid testing plan can create situations that will be extremely difficult to recover from and lead to significant downtime and risk.
  • ·         Use the detailed user acceptance testing plan to create a smaller version of the plan that will be executed as part of the live upgrade downtime window to validate that the system is ready to go live after that final upgrade.
  • ·         Detail each step of the overall upgrade process in a project plan, covering the task and expected time.  This project plan should cover the tasks from both the source system and the target system. One example would be to create a general plan for the majority of the testing/process cycles that can demonstrate performance changes between test cycles for each of the source system checklist items up to the single user processing section. A much more granular project plan should be created to outline every individual step required for the downtime window comprised of the single user processing steps on the source database and each of the steps on the target database. This more granular plan will be used for the final upgrade and will help ensure there are no surprises and everything works as expected.
Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • Please note the Microsoft Dynamics AX 2012 Data Upgrade Best Practices document has been released and the link is now provided at the top of the article.

  • HI Kevin,

    Thanks for the information and it's really useful for me when i am doing my upgrade.

    I need little clarification Kevin,

    When doing data upgrade from Ax4/5 to Ax6, Will it get all the setup's data also from source machine to target machine (like Dimensions, Chart of accounts). Since i am unable to see the setup's available in any module until i set manually. Because of this i am unable to get the information from Tables to Forms (Data is still available at table level).

    If the setup's also need to come up with data upgrade what are the steps need to performed. Did i need to run the checklist again in Source machine?

    If possible could you please help me

    Thank you.

  • When doing the upgrade, yes all setup information should also copy from AX 4/2009 into AX 2012. However, several of these tables would have required you to make some choices in the AX 4/2009 upgrade checklist related to Preparing your data for pre-processing.

    Another common reason for not seeing information inside AX 2012 is that the table had mapping errors that were not addressed - such as having a field that is not mapped from the source database to a field in AX 2012. If any mapping errors exist, the entire table is not copied. You can check the Generate Table Mappings window to find out what might have been missed.

    Kevin

  • Thanks for the response Kevin.

    I already ran full checklist in both Target and Source.

    I scenario i came across here is, in 'Release Order' form in Production module, the setup's like Storage, tracking dimensions, Item group and item model group are not set to particular items directly in upgrade.

    I need to release the item to 'Release Order' form manually and set all these mandatory setup's manually while releasing. I have around 3000+  items in my inventory and all these are need to released to particular company manually..!

    as per your suggestion, I will try to run the checklist in target machine to see the mappings errors.

    My concern is, that the functionality of the 'Release order' form.. to release the items again newly & manually..?

    Thank you..!

  • If you are using Virtual Companies to share information such as the InventDimSetup or InventModelGroup tables, then there may indeed need to be changes made to your ugprade scripts to account for this information being shared among several companies. That is the most common reason for not seeing those fields set when you look at the products inside Dynamics AX 2012.

    I have on my to-do list an idea to blog about what script/process changes are needed if the InventDimSetup table is shared, and I will try and prioritize that one a little higher. In the meantime, this type of question would be better managed through a support incident where an engineer can work with you to look at your system in more detail.

    Kevin

  • HI Kevin,

    Thanks for the Responce.

    I will try to implement your suggestion.  Looking forward for the Blog.

    One more similar issue i am facing here is, the 'MainAccounts' and 'offsetAccounts' of posting transactions of the 'GeneralJournels' are not upgraded to target machine.

    We investigated the issues, we found that the few of the table data is missing i,e Dimensionattributevaluecobination Table.  Few records are missing here.

    The 'ledgerdimension' fiels of 'Ledgerjournaltrans' table’s records are not present in the 'dimensionattributevaluecombination' table.

    i,e 'Ledgerjournaltrans' .Ledgerdimension == davc.recid

    I tried to Crete the records manually, since the 'hash' key in that table is Unique, i am unable to insert the 'hash' key value in the table.

    FYI: I am able to create one record manually and able to see the main account of the one particular posted transaction in Journals.

    So tried to do the same for the second record but it's not allowing since the 'Hash' key 'NULL i.e. the value is system generated and it is unique.

    Please suggest the best approach to resolve the main account and offsetaccount issue.

    Thanks in Advance.

    Harry

  • If you find a few very specific examples of this behavior after upgrading, the best method is to go into a transaction window and create a transaction using the same account/dimension combination. Then find the new row in the DimensionAttributeValueCombination (DAVC) table and get the RecID value. Then do an update of the original table that is pointing to a missing DAVC value and change its dimension field to be the RecID of the newly created record.

    If you have many instances of this problem, then there is likely something wrong with your upgrade scripts that should be investigated and fixed.

    Kevin

  • Hi Kelvin,

    I am upgrading the code and data from AX4 to AX 2012, I have moved all the codes as per the checklist but still I have certain code error in my AX 2012 machine, in this time can I start the data upgrade process.

    is it good to start the process? please suggest your valuable comments.

    Thanks in advance.

  • You need to be able to generate CIL in your environment for the data upgrade to run most effectively, which typically means a full compile with no errors. You might be able to get by if only your tables are compiling successfully with incremental CIL compiles, but that wouldn't be the recommended approach.

    Kevin

Page 1 of 1 (9 items)