Data Migration testing

Data Migration testing

Data Migration
Data migration is the process of transferring data between storage types, formats, or computer systems. Data migration is usually performed programmatically to achieve an automated migration, freeing up human resources from tedious tasks. It is required when organizations or individuals change computer systems or upgrade to new systems, or when systems merge (such as when the organizations that use them undergo a merger or takeover).

To achieve an effective data migration procedure, data on the old system is mapped to the new system providing a design for data extraction and data loading. The design relates old data formats to the new system's formats and requirements. Programmatic data migration may involve many phases but it minimally includes data extraction where data is read from the old system and data loading where data is written to the new system.

After loading into the new system, results are subjected to data verification to determine whether data was accurately translated, is complete, and supports processes in the new system. During verification, there may be a need for a parallel run of both systems to identify areas of disparity and forestall erroneous data loss.

Automated and manual data cleaning is commonly performed in migration to improve data quality, eliminate redundant or obsolete information, and match the requirements of the new system.

Data migration phases: design, extraction, cleansing, load and verification; for applications of moderate to high complexity are commonly repeated several times before the new system is deployed.

Types of Data Migration

• 1.Storage migration, when moving from specific physical media to another.
• 2.Database migration, when moving from specific database vendor to another or moving data between tables
• 3.Application migration, when changing application platform or service
• 4.Business process migration, on organizations where the process is manages and tracked, on changing vendors or interfaces between the human process parts to the software parts.

Testing Options and Strategies
The de facto approach to testing data and content migrations relies upon sampling, where some subset of random data or content is selected and inspected to ensure the migration was completed “as designed”. Those that have tested migrations using this approach are familiar with the typical iterative test, debug and retest method, where subsequent executions of the testing process reveal different error conditions as new samples are reviewed.

Sampling works, but is reliant upon an acceptable level of error and an assumption pertaining to repeatability. An acceptable level of error implies that less than 100% of the data will be migrated without error and the level of error is inversely proportionate to the number of samples tested(refer to sampling standards such as ANSI/ASQ Z1.4). As per the assumption on repeatability, the fact that many migrations require four, five or more iterations of testing with differing results implies that one of the key tenets of sampling is not upheld, i.e., “non-conformities occur randomly and with statistical independence…”.

Even with these shortcomings, sampling has a role in a well defined testing strategy, but what are the other testing options? The following lists options for testing by the phase of the migration process:

Pre-migration testing
These tests occur early in the migration process, before any migration, even migration for testing purposes, is completed. The pre-migration testing options include:

• Verify scope of source systems and data with user community and IT. Verification should include data to be included as well as excluded and, if applicable, tied to the specific queries being used for the migration.
• Define the source to target high-level mappings for each category of data or content and verify that the desired type has been defined in the destination system.
• Verify destination system data requirements such as the field names, field type, mandatory fields, valid value lists and other field-level validation checks.
• Using the source to destination mappings, test the source data against the requirements of the destination system. For example, if the destination system has a mandatory field, ensure that the appropriate source is not null, or if the destination system field has a list of valid values, test to ensure that the appropriate source fields contain these valid values.
• Test the fields that uniquely link source and target records and ensure that there is a definitive mapping between the record sets
• Test source and target system connections from the migration platform.
• Test tool configuration against the migration specification which can often be completed via black box testing on a field –by- field basis. If clever, testing here can also be used to verify that a migration specification’s mappings are complete and accurate.

Formal Design Review
Conduct a formal design review of the migration specification when the pre-migration testing in near complete, or during the earliest stages of the migration tool configuration.

The specification should include:

1. A definition of the source systems
2. The source system’s data sets and queries
3. The mappings between the source system fields and the destination system
4. Number of source records
5. Number of source systems records created per unit time (to be used to define the migration timing and downtime
6. Identification of supplementary sources
7. Data cleansing requirements
8. Performance requirements
9. Testing requirements

The formal design review should include representatives from the appropriate user communities, IT and management. The outcome of a formal design review should include a list of open issues, the means to close each issue and approve the migration specification and a process to maintain the specification in sync with the migration tool configuration (which seems to continuously change until the production migration).

Post-Migration Testing
Once a migration has been executed, additional end to end testing can be executed. Expect a significant sum of errors to be identified during the initial test runs although it will be minimized if sufficient pre-migration testing is well executed.
Post-migration is typically performed in a test environment and includes:

1. Test the throughput of the migration process (number of records per unit time). This testing will be used to verify that the planned downtime is sufficient. For planning purposes, consider the time to verify that the migration process was completed successfully.
2. Compare Migrated Records to Records Generated by the Destination System – Ensure that migrated records are complete and of the appropriate context.
3. Summary Verification – There are several techniques that provide summary information including record counts and checksums. Here, the number of records migrated is compiled from the destination system and then compared to the number of records migrated. This approach provides only summary information and if any issue exists, it does not often provide insight to an issue’s root cause.
4. Compare Migrated Records to Sources – Tests should verify that fields’ values are migrated as per the migration specification. In short, source values and the field level mappings are used to calculate the expected results at the destination. This testing can be completed using sampling if appropriate or if the migration includes data that poses significant business or compliance risk, 100% of the migrated data can be verified using an automated testing tool.

The advantages of the automated approach include the ability to identify errors that are less likely to occur (the proverbial needles in a haystack). Additionally, as an automated testing tool can be configured in parallel with the configuration of the migration tool, the ability to test 100% of the migrated data is available immediately following the first test migration. When compared to sampling approaches, it is easy to see that automated testing saves significant time and minimizes the typical iterative test, debug and retest found with sampling.

Migrated content has special considerations. For those cases where content is being migrated without change, testing should verify the integrity of the content is maintained and the content is associated with the correct destination record. This can be completed using sampling or as already described, automated tools can be used to verify 100% of the result.

User Acceptance Testing
Functional subtleties related to the co-mingling of migrated data and data created in the destination system may be difficult to identify early in the migration process. User acceptance testing provides an opportunity for the user community to interact with legacy data in the destination system prior to production release, and most often, this is the first such opportunity for the users. Attention should be given to reporting, downstream feeds, and other system processes that rely on migrated data.

Production Migration
All of the testing completed prior to the production migration does not guarantee that the production process will be completed without error. Challenges seen at this point include procedural errors and at times, production system configuration errors. If an automated testing tool has been used for post migration testing of data and content, executing another testing run is straightforward and recommended. If an automated approach had not been used, some level of sampling or summary verification is still recommended.

Recommendations for Designing a Data Migration Test Strategy
In the context of data and content migrations, business and compliance risks are a direct result of migration error. A thorough testing strategy minimizes the likelihood of data and content migration errors.

The list below provides a set of recommendations to define such a testing strategy for a specific system:

1. Establish a comprehensive migration team , including representatives from the user community, IT and management. Verify the appropriate level of experience for each team member and train as required on data migration principles, the source and the destination system.
2. Analyze business and compliance risks with the specific systems being migrated. These risks should become the basis for the data migration testing strategy.
3. Create, formally review and manage a complete migration specification – While it’s easy to state, very few migrations take this step.
4. Verify the scope of the migration with the user community and IT. Understand that the scope of the migration may be refined over time as pre- and post-migration testing may reveal shortcomings of this initial scope.
5. Identify (or predict) likely sources of migration error and define specific testing strategies to identify and remediate these errors. This gets easier with experience and the error categories and conditions listed here provide a good starting point.
6. Use the field-level source to destination mappings to establish data requirements for the source system. Use these data requirements to complete pre-migration testing. If necessary, cleanse or supplement the source data as necessary.
7. Complete an appropriate level of post migration testing . For migrations where errors need to be minimized, 100% verification using an automated tool is recommended. Ensure that this automated testing tool is independent of the migration tool.
8. Look closely at the ROI of automated testing if there is some concern about the costs, time commitment or the iterative nature of migration verification via sampling
9. Complete User Acceptance Testing with migrated data . This approach tends to identify application errors with data that has been migrated as designed.
10. Test the production run . If an automated testing tool was chosen, it is likely that 100% of the migrated data can be tested here with minimal incremental cost or downtime. If a manual testing approach is being used, complete a summary verification.