Upgrading a database server to SQL Server 2008 R2 involves more than just inserting an installation DVD and clicking your way through the wizard. A lot of planning goes into a SQL Server upgrade. In this article, I will share with you 10 tips that should help your upgrade process to go more smoothly. If you're looking for Windows hosting that support SQL 2008 R2, you may try us to be your partner. Please take a look our hosting plan at here. OK, let we start this article..

1. Be aware of the supported upgrade paths

Before you begin planning an upgrade, you need to be aware of Microsoft’s supported upgrade paths. For example, if you are currently running SQL Server 2005 X64 Enterprise Edition, you can’t upgrade to SQL Server 2008 R2 Standard Edition. Your only options are to upgrade to SQL Server 2008 R2 Enterprise Edition or Datacenter Edition.

2. Run the Upgrade Advisor

The Upgrade Advisor, which is a part of the SQL Server 2008 R2 Feature Pack, is a free utility that is designed to assist you with your SQL Server 2008 R2 upgrade. The tool analyzes your existing SQL Server deployment and informs you of issues that need to be addressed prior to performing an upgrade.

3. Don’t panic over Other Issues

The report generated by the SQL Server Upgrade Advisor often contains a section called Other Issues. This section exists as a way of informing you of possible issues that may exist, but that the tool is incapable of testing. Therefore, the issues that appear in the Other Issues section may not necessarily be present on your network.

4. Figure out what to do about the Notification Services

If you have SQL Servers that are running the Notification Services, you will need to plan how you want to deal with them. The Notification
Services were discontinued starting with SQL Server 2008, and can’t be upgraded to SQL Server 2008 R2.

5. Verify the hardware and software requirements

If you’re considering an in-place upgrade (rather than a migration), it is critically important to verify that your existing SQL Server meets all the hardware requirements for running SQL Server 2008 R2 and that all the necessary software prerequisites are in place. Check out this full list of the hardware and software requirements.

6. Perform a full backup

Although it should go without saying, you should always perform a full server backup prior to performing a SQL Server 2008 R2 upgrade. The upgrade process usually goes smoothly, but things can and sometimes do go wrong. It’s important to have a way to revert your SQL Server to its previous state if the upgrade does not go as planned.

7. Take care when upgrading the database engine

There are a few things that you should do prior to upgrading the database engine to ensure that things go smoothly. First, if you are running the Analysis Services, make sure you upgrade them before you upgrade the database engine. The Analysis Services must be upgraded first.

Just before the upgrade, temporarily disable any stored procedures. During the course of the upgrade, various SQL-related services will be started and stopped. If you have stored procedures that are configured to run when services start, there is a good chance those stored procedures will interfere with the upgrade.

Also check the Master, Model, MSDB, and TEMPDB databases and verify that they’re set to autogrow (and that there is plenty of disk space available). In addition, be sure to disable database replication prior to performing an upgrade.

Finally, even though SQL Server 2008 R2 is designed to preserve the Max Worker Threads setting, Microsoft recommends setting the Max Worker Threads value to 0. This will cause SQL Server 2008 R2 to automatically calculate the optimal value.

8. Be aware of discontinued features

Microsoft has removed the Surface Area Configuration Tool from SQL Server 2008 R2. Most of the tool’s functionality still exists, but it has been rolled into other areas of the application. For example, protocols, connection, and startup options are now found in the SQL Server Configuration Manager. If you use the Surface Area Configuration Tool from time to time, it’s a good idea to deploy SQL Server 2008 R2 in a lab environment so that you can get a feel for what it takes to manage SQL without this tool.

9. Perform a test upgrade

Before you attempt to upgrade a production database server, try the upgrade in a lab environment. Make a full backup of a domain controller, a DNS server, your SQL server, and any other required infrastructure servers and then restore those backups to isolated lab servers. Once SQL is up and running, try out your upgrade plan in the lab. That way, you can handle any issues that come up before you have to perform a real upgrade.

10. Don’t forget to clean up when you’re finished

When the upgrade is complete, run DBCC UPDATEUSAGE on all of your databases to ensure database integrity. You will also need to reregister your servers and repopulate full text catalogs. If you have disabled replication or disabled stored procedures for the upgrade, you will need to put things back to normal.