How to copy data from one Microsoft Dynamics AX 2012 R2 AOS to another (MSSQL VS Test Data Transfer Tool (TDTT))

Below will show you the steps on how to copy all of the data from one aos to another.

*Option 2 should be only used when promoting to non-production environments

Related: How to promote entire Microsoft Dynamics AX 2012 R2 Code Base (MSSQL VS AXUtil VS Powershell)

Export Data

o   Option 1 (MSSQL Server):

§  Stop AOS

§  Make backup of database from within MSSQL management studio

§  Be sure to compress the db! (via software method)

§  Note: this option can cause issues with syncing the db and you may need to manually drop tables and reimport the data 

o   Option 2 (Test Data Transfer Tool):

§  Info: TechNet

§  Download: Available in Microsoft Lifecycle Services > Downloadable tools

§  DP.exe <direction> <directory> <database> <server>

·       Example: DP.exe export C:\AXExport AX_PROD_01 SQL\AXPROD

§  This should never be used to import data into prod, only user it on environments such as qc, test, dev.

§  It also has the ability to skip certain tables such as system settings. See the link above for more details on skipping tables

NOTE: This needs to be ran on the sql box where the db is located. It uses whatever account triggers dp.exe so that user needs to have access to the raw sql db as well

Import Data

o   Option 1 (MSSQL Server): Restore Database

§  Settings (General): Override old db name with the db you want to restore to

§  Settings (Files): Change file name under “restore as” to the db you want import to.

§  Settings (Options): Overwrite the existing database (WITH REPLACE) should be checked

§  Start AOS

§  Run full compile via client side (long compile)

·       Since the DB is different it’s better to do this on the client to make sure no errors exist

§  Sync Data Dictionary (makes sure table indexes are good to go)

§  Full CIL

o   Option 2 (Test Data Transfer Tool):

§  DP.exe <direction> <directory> <database> <server>

·       Example: DP.exe import C:\AXExport AX_PROD_01 SQL\AXPROD
NOTE: This needs to be ran on the sql box where the db is located. It uses whatever account triggers dp.exe so that user needs to have access to the raw sql db as well

Settings that need updated

·      System administration/Setup

o   Batch Group

§  Remove servers from transferring dbs

§  Assign all groups to the correct batch server(s) related to the current environment

o   System / Server configuration

§  Remove servers from transferring dbs

§  Mark batch servers related to the current environment

o   System / Cluster configuration (Prod)

§  Doing the previous step should remove the servers from the transferring dbs, but if it doesn’t remove it from here as well.

§  Create group for batch server

·       Assign batch servers into this group via the cluster name field on the grid

§  Create group for non-batch servers

·       Assign non-batch server into this group via the cluster name field on the grid

o   System / E-mail Parameters

§  Local Computer Name – update to the current aos

§  Password: enter password for email

o   System / Help system parameters

§  Update URL according to the environment

§  Example: http://aoswebserver:8080/DynamicsAX6HelpServer/HelpService.svc

o   Business intelligence / Analysis Services / Analysis Servers

§  OLAP servers (tab) > OLAP Server Name – change to db\instance of current aos sql server

·       Example: sql\axprod

o   Business intelligence / Reporting Services / Report servers

§  Create instance for every aos and update with related SQL/SSRS information

§  Server Name: <sql server name>

·       When dealing with a cluster it seems to work better to list the default ssrs box

§  Server instance name: <sql instance name>

§  Report Manager URL: http://aosssrs/Reports_AXPROD

§  Web service URL: http://aosssrs/ReportServer_AXPROD

§  Report Folder: DynamicsAX

§  AOS: point to the aos (this is the setting you change for every aos when dealing with a cluster)

o   Enterprise Portal / Web sites

§  Change internal/external urls to AX Sharepoint urls

·       Example: http://aoswebserver/sites/DynamicsAx

§  Type: Full

·       With importing data, there comes a risk that the code may be expecting certain number sequences to be setup which are not. THIS IS OPTIONAL AND NOT REQUIRED ON EVERY IMPORT

o   Because of this, run the Number Sequence Wizard under Organization Administration \ Common \ Number Sequences \ Number Sequences List page \ “Generate” under “New” on the Action Bar

o   That will generate any un-generated Number sequences.

§  Take note of what it generates, and the Number sequence code, as further tuning may be necessary because the sequences default to Company-####, which may not be desired.