Checking Data Migration...greenspun.com : LUSENET : SQL Server Database Administration : One Thread
Currently I am involved in the Data Migration using SQL Srvr Databases. My question is: Once the migration process is through, What are the ways and means to check that the Data's are migrated successfully?
-- Anonymous, July 20, 1999
No simple answer. I just went through this process myself. We had a fairly complex transactional database in operation, and made radical changes to the data structure. I had twenty pages of SQL to do the transfer. Testing basically boiled down to two things:
1) I made a set of test cases in the old database which covered all the tricky bits. Then I looked to see if they showed up right in the new application.
2) I wrote a bunch more SQL statements that validated the databases against each other. Eg., I had one statement to find any cases where a user could see a record in the new database that he couldn't see in the old. Etc.
Putting all the constraints on helped too.
-- Anonymous, July 27, 1999
Basically, you need to get enough experience so that you finally trust the migration tool.
Until you get to that point, you will want to do the types of things that Dennis suggests, such as comparing the quantity of data in the tables, comparing the table names and contents, comparing the logins and users, comparing the referential integrity, indexes and triggers, and comparing the stored procedures, statistics, database options, database size and log size, and comparing the scheduled procedures to be run against the databases.
The preceeding list probably leaves a few things out.
Hope this helps,
-- Anonymous, July 28, 1999