Sometime ago, I published some tips on upgrading to 2008. Here are some additional notes to keep in mind.
For upgrading, my plan of attack in the past has been as follows:
1. Run the Upgrade Advisor on all the databases (this doesn’t require much effort, just download, install, point to the db and run)
2. On production, do a custom install and only install what you need. You can do a side-by-side install and have SQL Server 2008 installed while SQL Server 2000 exists on the same server.
3. Analyze the Advisor’s reports and refactor where SQL objects that need to be refactored
4. Refactor TSQL as needed
5. Now, TSQL isn’t just in the DBs, it may also exists in the app code and other web files (.aspx, etc.). This is where it gets more challenging. Check to make sure if the analyzer can open .trc files. If so, run SQL Server profiler while the site is being used and those queries are being run. Then open that .trc file in the analyzer so it can check the validity.
If this doesn’t work, log the profiler results to a table. Then put the TSQL from output of the profiler in a sproc. Point the analyzer to that sproc to check the syntax.
You can automate this task by using Selenium or write a script using WGET to fetch and look for 500 HTTP error codes.
6. Refactor TSQL as needed.
7. Test the websites with 2008. Ideally you want to start testing with the latest compatibility. Having a lesser level of compatibility causes more overhead (since it has to support older features) and can allow the use of deprecated features that MS will abandon in the next release.
9. Optimize the settings for db server. Strip out all dbs you don’t need to avoid security problems, like: Northwind, AdventureWorks.
10. Before pushing it live, make sure you backup all dbs, including all system dbs.
11. As soon as the switch is flipped, keep an eye out on perform/SSMS monitor. Have handy the appropriate DMVs to monitor.
12. Even if the application(s) did not throw errors while testing on dev, there’s a chance that once you go to production, it starts throwing errors due to compatibility (maybe the more obscure pages were not tested). Be ready to flip the compatibility mode to 2000 – this takes place instantly.