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.