Prerequisite for upgrading SQL Server from one version to another for a smooth upgrade process.
Step 1: We should be aware of the Microsoft supported upgraded paths.
Example: SQL server 2005 X64 Enterprise Edition can’t be upgraded to SQL Server 2008 R2 standard edition. Only option to upgrade to SQL server 2008 Enterprise edition or Datacentre Edition.
Step 2: Run the upgrade advisor
It’s a free utility tool that analyses existing SQL Server deployments and highlighted the issues that need to be addressed prior to performing an upgrade.
Step 3: Verify the hardware and software requirements
It’s very important to verify that existing SQL Server meet the entire hardware requirement for running SQL Server.
Step 4: Perform a Full Backup
Take full backup prior to perform a SQL Server upgrade. Upgrade process usually go smoothly however sometime may go wrong.
Hence it’s important to have a backup so that we can revert to its previous state if the upgrade does not go as planned.
Step 5: Two main options for an upgrade in SQL Server.
In place Upgrade
An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for you. In addition, you do not have to update client applications to connect them to a new instance of the relational Database Engine. However, an in-place upgrade is an all-or-nothing approach. In the unlikely event that an in-place upgrade of the relational Database Engine fails, you cannot quickly roll back to SQL Server 2000 or SQL Server 2005.
Side by side upgrade
With a side-by-side upgrade, the SQL Server 2008 R2 relational Database Engine is installed as a second instance and the original SQL Server 2000 or SQL Server 2005 relational Database Engine remains installed. Then move or copy one or more SQL Server 2000 or SQL Server 2005 user databases to the SQL Server 2008 R2 instance (each moved database is automatically upgraded).
This approach help maximize ability to quickly roll back to the prior instance whenever difficulties arise. A side-by-side upgrade might also result in simpler testing scenarios because both versions are available at the same time.
Step 6: Things to consider while upgrading the database engine.
If you are running analysis service, make sure you upgrade analysis service before database engine. The analysis service must be upgraded first.
Before upgrade temporarily disable stored procedure .During the course of upgrade, various SQL- related services will be stopped and restarted. If there are procedures configured to run when services start, there may be chance those stored procedure will interfere with upgrade.
Check the master, model, msdb and tempdb databases and verify that they are set to auto grow.
Disable database replication prior to performing an upgrade.
Even though Microsoft SQL Server 2008R2 is designed to preserve max worker thread, Microsoft recommend settings the MAX worked thread value to 0.
Step 7: Run DBCC UPDATEUSAGE
Post upgrade run DBCC UPDATEUSAGE on all databases to ensure database integrity.
Attached one Microsoft upgrade document for complete detail.