Approach for SQL Server Upgrade Testing
I am currently working on an assignment which involves testing a datawarehouse upgrade from SQL 2000 to SQL 2008.
The DW is currently on a SQL 2000 box being fed by number of external sources (other DB's, flat files) through Informatica ETL's. Inturn the DW feeds a number of downstream systems like Cognos reports, accounting systems etc through flat files and ETL jobs.
We plan to follow a parallel testing approach where in we run the ETL's and other jobs on the Live system and the upgraded test system and compare the data being loaded into both the DW's. The approach also involves verifying the flat files generated from the DW and the reporting data being fed into Cognos. I believe the upgrade can be called successfull by ensuring that the jobs are successfull (on the new system), the data (being loaded) in both the DW's is the same and the downstream data being fed into other systems is also same.
On the non functional end we compare the performance of the ETL and other scheduled jobs on the Live and upgraded test systems and call the test a success if the jobs on the upgraded test systems take the same amount of time or less than whats being recorded on the live system.
I was wondering whether anyone involved in similar kind of upgrade efforts can comment on my test approach. Also any other ideas or test techniques are really appreciated.