As you indicate in your question, it is very important that you test the upgrade process before you upgrade your production server. Here’s what I recommend.
- Restore a backup of each of your production databases onto another (test) server, including the system databases running the same version of SQL Server as your production box. In other words, you essentially need to duplicate your current production system on a test server.
- Run Upgrade Advisor on the test box and see what your results are. In some cases you may have to modify your application for it to work properly with SQL Server 2005, although this is not common.
- Assuming that the Upgrade Advisor results are positive, then perform an in-place upgrade on the test box. Once the box has been upgraded to SQL Server 2005, then you will want to test each of the applications that use the databases on this server to ensure they work correctly.
- Once you are sure that all your old applications work fine, then you are ready to upgrade your production box.
Note: The above example assumes that you are performing an in-place upgrade, but this is not your only option. You can also create a new test box that has SQL Server 2005 on it, and then detach the user databases from the production server (during a down time), copy them to the test server, and then reattach them. This will automatically upgrade the older databases to SQL Server 2005. At this point, you will of course want to test your applications to see if they work properly before you do this for your production server. In addition, you will need to run the Upgrade Advisor on the production box, as there may be no test box to run it on. Running Upgrade Advisor on a production SQL Server will not cause any problems.
To download the Upgrade Advisor as a separate component (it also comes on the SQL Server 2005 install DVD), visit:
Additional information from Microsoft on peforming upgrades is available at:
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
and
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Also, don’t forget, Books Online has a very good section on how to upgrade to SQL Server 2005.
]]>