Data Validation

In order to validate that all required data has migrated successfully from Oracle to SQL Server it is advisable to prepare a row count comparison spreadsheet.

Two scripts (detailed below) provide row counts for tables within each of the RDBMS’s. Ensure that the scripts are run against the appropriate schema. The results of the scripts can be entered into Excel where a simple comparison can be performed.

Oracle

select table_name, 
to_number (extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c
from '||table_name)),'/ROWSET/ROW/C')) count
from user_tables where table_name not like 'ax_tmp%' order by table_name;

Note that Oracle temp tables are excluded from the count as they do not contain data and will cause the script to throw an error.

SQL Server

SELECT sc.name +'.'+ ta.name TableName,
SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY ta.name ASC

Schema Differences

There can be some differences between the source and target databases, however those differences tend to pose no risk to the data migration as the tables involved are often system configuration tables or may be related to application modules that are no longer used or not licensed for. Like with any major migration task it is advisable to test the migration procedure against non-production environments first to ensure that the approach works and to verify that any discrepancies between the source and target databases pose no threat to the migration or validity of the data in the final system. Microsoft publish details of Dynamics AX schema objects (e.g. tables, views, indexes, etc.) and it would be recommended to check any discrepancies with the migration with those object details so that an informed decision can be made as to whether to ignore them or make alternative arrangements for their migration.

The resulting  SQL Server database contains an expected additional table, called MIGRATIONDETAILS,  where data related to the migration itself is stored which is not present in the Oracle database.

Once data validation is complete, the Dynamics AX 2009 instance running on SQL Server should be accessed and a round of functional testing should be performed to ensure the application operates as expected.

Conclusion

Any Data Migration project is fraught with risk; risk of data loss, risk of datatype mismatching, risk of application failure, and risk to business operations. The risks inherent in a data migration project are heightened when the data is at the heart of a major business system, so it is wise to mitigate against those risks as much as possible. Risk mitigation comes in many forms; proper documentation, adequate testing, and safety measures like data validation, but one of the most important steps in reducing exposure to risk is to select the best migration methodology available. For Dynamics AX systems moving from Oracle to SQL Server, the safest option, and therefore the best option, is to utilise the migration tool provided by Microsoft for the explicit purpose of getting data from one database platform and into the required destination. There is a lot of work in migrating, especially when it comes to the testing phases, but the frightening aspects of the migration can be made a little easier to live with when the ease of migration is demonstrated, and more time can be freed up for testing, thus building confidence in the system that goes live at the end of the process.