||This article may require copy editing for Tone. (June 2013)|
Database migration testing is needed when data is moved from one database(s) to another.
The first database is called the legacy or source database and the latter is called the target or destination database. Database migration may be done manually, but it is more common to use an automated ETL (Extract-Transform-Load) process to move the data.
In addition to mapping the old data structure to the new data structure, the ETL tool may incorporate certain business rules to increase the quality of data moved to the target database as follows: (I) All the live (not expired) entities (e.g. customer records and order records) are loaded into the target database with each entity being loaded only once; (II)Every attribute (present in the source database) of every entity (present in the source database) is loaded into the target database; (III) All data related to a particular entity is loaded in each relevant table in the target database; (IV) Each required business rule is implemented correctly in the ETL tool; (V)The data migration process performs reasonably fast and without any major bottleneck.
Database migration testing may encounter problems when: I. The data in the source database(s) changes during the test; II. Some of the source data is corrupt; III. The mappings between the tables/ fields of the source databases(s) and target database are changed by the database development/ migration team; IV. A part of the data from the source database is rejected by the target database; V. Data migration takes too long because the database migration process is too slow or the source data file is too large.
The test approach for database migration testing consists of the following activities:
I. Design the validation tests:In order to test database migration, SQL queries are created either by hand or using a tool, such as a query creator. The validation queries are created to run against both the source as well as the target databases. The validation queries should cover the scope defined by the requirements of the test. It is common to arrange the validation queries in a hierarchy based on relationships within the database. For example, "Orders Records" migration should be tested before Order Detail records, on the premise that Order Details are dependent logically on Order Records. The test queries should contain logging statements for the purpose of effective analysis and bug reporting after the tests are complete.
II. Set up the test environment. The test environment should contain a copy of the source database, the ETL tool (if applicable) and a clean copy of the target database. The test environment should be isolated so that it is not changed externally during the tests.
III. Run your validation tests Depending on the test design, the database migration process does not need to completely finish before starting tests.
IV. Report the bugs and call the exterminator The following data should be reported for each failed test: a. Name of the entity that failed the test b. Number of rows or columns that failed the test c. If applicable, the database error details (error number and error description) d. Validation query d. User account under which the test was run, ⟨⟩ e. and time the test was run
Keep the tips below in mind to refine your test approach
1. Before testing, it is good practice to create backup copies of the source and target databases. These will be used if the test must be restarted. Backup copies can help reproduce bugs for evaluation. 2. If some source data is corrupt (e.g. unreadable or incomplete,) you should find out if the ETL tool takes any action on such data. If so, your validation tests should confirm these actions. The ETL tool should not simply accept the corrupt data as such. 3. If the mappings between the tables/ fields of the source and target databases are changed frequently, you should first test the stable mappings. 4. To find the point of failure efficiently, create modular validation tests. Modular test make it possible to execute some tests before the data migration process finishes. Running some tests while the data migration process is still running saves time. 5. If the database migration process is manual, validation queries must be run externally. However, if the process uses an ETL tool, the validation queries may be integrated within the ETL tool.
Data Migration Testing Methodology
Data Migration Testing Methodology is a way to design massive data transfers of data from one set of data to another in a systematic fashion.
- Migration Testing-Analysis phase.
- Understand all data sources and data within that have to be migrated.
- Data analysis and Profiling of data.
- Identify the size of data and database objects to be migrated for scoping.
- Verify the mapping between source and target databases/tables.
- Recommend a POC for complex application testing.
- Prepare Detailed Test Plan and Test Strategy.
- Migration Testing- Pre-Validation phase.
- Build the QA/Test environment (installing, configuration of the required hardware and software).
- Configure the source and target database environment.
- Populate the test data or production data, extract to the source test database server.
- Install and configure Arsin’s accelerators for migration validation/testing.
- Validate the scripts or ETL built for data migration.
- Migration Testing- Validation phase
- Perform smoke test for quick validation
- Run the migration utility/ETL/ scripts for data load
- Monitor the data load time and error logs
- Terminate the data load if error threshold exceeds
- Run an automated tool/utility to validate the data between source and target databases
- Report issues/defects
- Migration Testing- Post Validation phase
- Analyze test results
- Perform Security audit