Testing and Verification of Data After Database Migration


Database migration is a critical process that requires thorough testing and verification to ensure the accuracy and integrity of the migrated data. After successfully migrating your database from one system to another, it is essential to validate the data to guarantee a seamless transition. This article provides a step-by-step guide on how to test and verify data after a database migration.

Database testing

Step 1: Define Testing Scope and Objectives

Clearly define the scope of your testing, including the tables, views, stored procedures, and any other relevant database objects that need to be verified. Set specific objectives to ensure the migrated data meets your expectations for accuracy, completeness, and consistency.

Step 2: Select Sample Data

Identify a representative sample of data from your source database, choosing data from various tables, data types, primary key constraints, and relationships. A diverse sample helps uncover potential issues across different aspects of the database.

Step 3: Compare Data Counts

Start by comparing the total row counts between the source and target databases for each selected table. A mismatch in row counts could indicate missing or duplicated data during migration. Use simple SQL queries to retrieve the row counts from both databases and compare them side by side.

Download Data Loader
Step 4: Validate Primary Keys and Unique Constraints

Verify the integrity of primary keys and unique constraints in the migrated data. Run queries to identify duplicate or missing primary key values, and ensure primary key columns have unique values in the target database that match the corresponding values in the source.

Here are detailed instructions on how to validate primary keys and unique constraints in MSSQL and MySQL.

Validating in MSSQL: Check Primary Key Existence
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.key_constraints AS pk ON pk.parent_object_id = t.object_id AND pk.type = 'PK'
WHERE t.name = 'YourTableName'
Check Duplicate Primary Key Values
SELECT PK_Column, COUNT(*) AS DuplicateCount
FROM YourTableName
GROUP BY PK_Column
HAVING COUNT(*) > 1

Validate referential integrity by ensuring foreign keys referencing the primary keys are valid and refer to existing records — you can query the foreign key constraints using system views like sys.foreign_keys.

Check Unique Constraints Existence
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE t.name = 'YourTableName' AND i.is_unique_constraint = 1
Check Duplicate Unique Constraint Values
SELECT UniqueColumns, COUNT(*) AS DuplicateCount
FROM YourTableName
GROUP BY UniqueColumns
HAVING COUNT(*) > 1
Validating in MySQL: Check Primary Key Existence
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'PRIMARY' AND TABLE_SCHEMA = 'YourDatabaseName'
Check Duplicate Primary Key Values
SELECT PK_Column, COUNT(*) AS DuplicateCount
FROM YourTableName
GROUP BY PK_Column
HAVING COUNT(*) > 1
Check Unique Constraints Existence
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME <> 'PRIMARY' AND TABLE_SCHEMA = 'YourDatabaseName'
Check Duplicate Unique Constraint Values
SELECT UniqueColumns, COUNT(*) AS DuplicateCount
FROM YourTableName
GROUP BY UniqueColumns
HAVING COUNT(*) > 1

Remember to replace 'YourTableName' and 'YourDatabaseName' with the appropriate table and database names in the queries above.

Step 5: Check Data Consistency

Perform data consistency checks by comparing specific columns or values between the source and target databases. Execute queries to retrieve sample data from both databases and compare them, looking for discrepancies in critical fields such as dates, amounts, or other business-critical attributes.

Step 6: Test Referential Integrity

If your database includes foreign key relationships, validate the referential integrity after migration. Check that relationships between tables are maintained correctly, and that foreign keys reference valid primary key values with cascading updates or deletes working as expected.

Step 7: Execute Sample Queries and Reports

Execute report

Run a set of sample queries and reports that your applications or users typically use. Ensure results from these queries match between source and target databases, paying attention to complex queries involving joins, aggregations, and calculations.

Step 8: Perform Functionality Testing

Validate the functionality of applications or systems that rely on the migrated database. Conduct end-to-end testing to ensure the migrated data works seamlessly with the application, testing common use cases and workflows.

Step 9: Perform Performance Testing

Assess the performance of the migrated database by executing stress tests, load tests, or other relevant performance testing techniques. Monitor query response times, transaction throughput, and system resource utilization, comparing against the baseline performance of the source database.

Step 10: Document and Resolve Issues

Document any issues, discrepancies, or anomalies encountered during testing. Categorize by severity and impact, collaborate with the migration team to resolve identified issues promptly, and retest fixed issues to confirm resolution.

Conclusion

Thorough testing and verification of data after a database migration are essential to ensure the accuracy and integrity of the migrated data. By following the step-by-step guide outlined in this article, you can systematically validate the migrated data, identify any discrepancies, and address them promptly — minimizing the risk of data corruption, improving application performance, and providing confidence in the success of your database migration process.

Back to: Step by Step Guide on Migrating MSSQL to MySQL