Hints for testing a web application which had a DB migration from SQL 2000 to 2005.

Question

im testing web application(e-learning which having users, content items, reporting,assigning ...) which had a DB migration from SQL 2000 to 2005.

If somebody knows what are the area i should focus on testing Please let me know.

Answer 1

You should test all main areas of the application (administration, end user testing etc.). If the application was tested before you should have some test plans that you should follow. You never know which part might be affected, so it would be better to have a comprehensive testing.

Answer 2

Off the top of my head, one thing I can think of is that SQL 2000 supported the short-hand operators '*=' and '=*' for LEFT OUTER JOIN and RIGHT OUTER JOIN respectively. SQL 2005 no longer supports them. This means that you'll have to replace these operators with 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN' as the case may be.

But as rslite says, you should test the entire app to ensure that nothing fails.

Answer 3

From a DBA's point of view and so a SQL Server Performance (and therefore also an application) perspective, if you migrated your database to SQL Server 2005 using a database restore of a backup or the Copy Database Wizard from sql 2000, then you will want to ensure that you update the database statistics.

This is because the both the database engine and the query optimizer in SQL Server 2005 behave differently to SQL 2000. Your database statistics must be updated in order to ensure optimal performance in the new environment.

Taken from SQL Server Books Online:

To ensure optimal performance of an upgraded database, run sp_updatestats (update statistics) against the upgraded database on the SQL Server 2005 server.

Once you have validated that your application is functioning correctly, I would then recommend conducting a performance tuning exercise by identifying your poorest performing queries.

Answer 4

You're not saying what programming language are you using for your web application, this might be relevant.

Anyway you need to identify the different operations you're doing that concern the database :

• simple reads ( check if you have the same type of selects all over the app, are you opening result sets always forward-only for instance, or do you use scrollable result sets, etc. )
• complex reads ( do you read large amounts of data, do you read blobs at any moment, etc. )
• inserts ( check for bulk inserts for instance or any insert that can put a lot of load on the db )
• updates ( might put locks on some tables hence concurrency issues or problems with syntax if anything has changed on MS side )

If these kinds of operations are treated uniformly by your app, a couple of tests of each type should be enough, but you should check carefully the code for anything that can result in a different behavior and test those parts as well.

You'll also want to check the SQL 2005 documentation regarding changes in datatypes, sql changes and verify in your code if there's an impact and re-test those parts.

Hope this helps.