Plan data import, export, and migration [AX 2012]

This topic describes the tools and strategies that you can use when you are planning to import or export Microsoft Dynamics AX data. The topic also describes how to plan to move data from one enterprise resource planning (ERP) system to another. Finally, the topic describes performance and security considerations when you import and export data.

Data can be imported into and exported from Microsoft Dynamics AX by using various tools and techniques. Before you select a tool, you should understand your business requirements, the available data, and what you want the outcome to be.

Important

All of the following tools can be used to import and export data, but every operation that manipulates data has risks. It is your responsibility to create regular backups of your data before you use any tool. We strongly recommend that you test your particular uses of the tools you use to determine whether they meet your needs.

The following table lists the common import and export scenarios, and the tools that customers and partners can use.

On a recurring basis, update data from external sources, based on events

Best

Infrequently, update data from external sources and large data sets

Best

Possible

Data entry

Best. Use with services.

Copy business data to a non-production environment

Possible

Best

Possible

Copy business data from a test environment to a development environment for troubleshooting

Possible

Best

Possible

Copy business data to a new production environment

Possible

Do not use.

Best

Archive business data from a production database

Best

Note

The previous table does not describe how to copy a company. Microsoft Dynamics AX 2012 does not support the creation of a duplicate company. To duplicate data from one company to another, one entity at a time, you can use the Data Import/Export Framework.

The Data Import/Export Framework is an extension that helps you import data into Microsoft Dynamics AX 2012 from files, Open Database Connectivity (ODBC) data sources, and Microsoft Dynamics AX tables. You can import predefined entities, or you can create custom entities for import. You can modify data as it is imported, and you can also verify that data meets specific rules.

Different versions of the Data Import/Export Framework are available depending on what release of Microsoft Dynamics AX 2012 you have installed. To see what version to use, as well as for documentation and information about the Data Import/Export Framework, see the Data import/export framework user guide (DIXF, DMF).

Intended users: Application users, developers, and functional users

Key features:

Support for import from files, ODBC data sources, and Microsoft Dynamics AX tables

Bulk processing

Synchronous processing

Recommended uses: Use this tool when you must perform the following tasks:

Import data from another ERP system that uses dissimilar data structures. You can use the Data Import/Export Framework to map data to Microsoft Dynamics AX entities.

Import data that requires transformation, such as changes to number sequences.

RapidStart Services lets partners and customers configure an installation of Microsoft Dynamics AX by using a cloud-based, interview-style questionnaire. The questionnaire uses general terminology about business processes instead of terms that are specific to Microsoft Dynamics AX. Partners can create, maintain, and reuse configurations that reflect their custom solutions. Partners can also create additional question groups and questions to enlarge the scope of a configuration.

In-product import/export is a mechanism for transferring data between different Microsoft Dynamics AX instances. In-product import/export is available from System administration > System administration.

Intended users: Functional users and developers

Uses: You can use this tool in the following situations, but other tools may be available and more suitable in these situations. See earlier section, “Select a tool”, for a list of common import and export scenarios.

The source and target are Microsoft Dynamics AX instances.

No cleansing of data by end users is required.

You are moving data from one company to another.

Note

Data can be exported from a company that is in one partition and imported into another company that is in another partition.

Data cannot be imported into and exported from multiple companies at the same time.

Non-recommended uses: We recommend that you not use this tool when you must complete the following tasks:

Recommended uses: Use this tool when you must complete the following tasks:

Import many similar records, and repeat the same import at regular intervals. In this scenario, use AIF web services that use inbound ports and pipelines.

Take data that is in an existing file format, such as an XML export from another system, and process the data so that it matches the schema that is expected by existing or customized AIF document services. You can author transformations in either XSLT or managed code. Document services must be customized if the underlying tables and entities have been customized.

Access Microsoft Dynamics AX business logic. You might have to use this approach when the structure of the data is not easily represented as tables, or if the web service that you want to use is not supported by the Excel Add-in.

The Microsoft Dynamics AX 2012 Test Data Transfer Tool (beta) is a command-line tool that exports data from and imports data into a Microsoft Dynamics AX 2012 business database in a non-production (development or test) environment.

Importing data into a production environment from the Test Data Transfer Tool (beta) is not supported. The Test Data Transfer Tool (beta) can be used only to export data from a production environment, and then import the data into a test or development environment.

Intended users: Only advanced users should use this tool. You should be a database administrator or a developer who has experience using SQL Server. You must also have permissions to read from or write directly to the Microsoft Dynamics AX database that you are working with, and to execute applications directly on the computer that is hosting the database.

Recommended uses: Use this tool when you must complete the following tasks:

Export or import a large multi-company data set in a non-production environment.

Move data between non-production Microsoft Dynamics AX environments that have slightly different customizations.

Store business data in a version control system.

Export or import data without running an instance of Microsoft Dynamics AX Application Object Server (AOS) in a non-production environment.

Key features:

Because the import makes minimal changes to the data, the tool helps guarantee that the data remains stable over time. For example, the tool never renumbers RecIDs.

The data file format is text-based. Therefore, the data file can be compared with earlier versions and can be stored in a version control system.

The data file format is a standard format that is produced and understood by the SQL Server bcp Utility.

The tool enables export filtering, so that specified tables, columns, or rows can be easily excluded from the export.

The tool updates entity IDs, such as table IDs, class IDs, and extended data type IDs, so that the IDs match the IDs of the target system.

The tool handles differences between builds of Microsoft Dynamics AX 2012 that often occur during development. Therefore, data can often be imported without user intervention, even when the table definitions have changed. For example, renaming tables or fields does not prevent import.

Key considerations:

Importing data into a production environment from the Test Data Transfer Tool (beta) is not supported.

The tool does not make sure that any data that you export is complete or coherent. However, the tool does export the data that you ask it to export.

The tool does not make sure that any data that you import produces a complete or coherent database. However, the tool does import the data that you ask it to import.

You must use SQL Server backup and recovery to move business data to a new environment. Before the restored database can function in the new environment, you must adjust certain values, such as server names, domain names, user accounts, and URLs. For more information, see the blog post Moving between Microsoft Dynamics AX 2012 Environments.

Intended users: System administrators and database administrators

Recommended uses: Use this tool when you must perform the following tasks:

The Intelligent Data Management Framework for Microsoft Dynamics AX (IDMF) lets system administrators optimize the performance of Microsoft Dynamics AX installations by making it easier to archive data, and maintain an optimal database size.

If you are moving from another ERP system to Microsoft Dynamics AX, you must import master and reference data.

Note

We recommend that you not import transactional data or historical data into Microsoft Dynamics AX. Instead, close all open transactions that you can before import, so that totals can be imported. Maintain the database that contained your previous transactional data for reporting and compliance purposes.

Data import is a complex process that usually requires many iterations. The import process includes the following general steps:

Identify the data in your existing system that must be imported.

Consider cleaning up the data in your existing system. For example, determine whether old records can be deleted or archived, whether the current database contains duplicate records, and whether you want to change numbering schemes.

Become familiar with the relevant data structures in Microsoft Dynamics AX that the data from your existing system must be moved to.

Note

Data from one table in another system might have to be moved into multiple tables in Microsoft Dynamics AX.

Based on the information earlier in this topic, determine the appropriate tools and techniques for the data that you must migrate.

Prepare a test Microsoft Dynamics AX environment.

Note

The required configuration for master records must be completed before you import data.

Create a backup of your existing system and your Microsoft Dynamics AX environment before you import any data.

Perform a trial import of all types of data that are required.

Note

Expect to encounter errors the first time that you perform an import. Review the errors that you encounter, make any fixes that are required, and perform the import again.

When you export or import Microsoft Dynamics AX tables, store them in a folder that has appropriately restrictive permissions. Many tables in Microsoft Dynamics AX contain sensitive information, such as the global address book tables and the UserInfo tables. If you allow table data to be edited and then imported into the system, you risk providing increased access to the Microsoft Dynamics AX system.