In preparation for upgrading our SQL Server 2005 production database servers to SQL Server 2008, it was prudent and necessary to test the upgrade process and regression test the existing applications dependent on those servers. In this article, we will discuss the lessons learned and best practices for upgrading SQL Server.

The Environment

Our current production environment consists of eight primary production database servers, four of which are clustered using Windows Server 2003 R2 and SQL Server 2005 SP2. We also have four stand-alone BI servers that support each other’s functionality. In addition, there are several dev and test servers, including one clustered server, in support of the production servers.

The Process

We began the upgrade process by conducting interviews with the principal stakeholders, breaking them into functional groups. Each group was assigned the responsibility of regression testing the applications. We followed the upgrade procedure documented in the MSDN article, “How to: Upgrade a SQL Server Failover Cluster Instance (Setup)”, as our guide. We upgraded the CRM dev and test servers first, followed by the BI servers, and finally the shared MOSS/Web test server.

The Gotchas

During the upgrade process, we encountered several “Gotchas” or issues that required attention. Here are some of the key ones:

1. Reporting Services (RS) is required for the Upgrade Advisor: The SQL Upgrade Advisor (SUA) needs to be installed on the server that it is scanning. While it is recommended not to put Reporting Services on the same server as an Operational Data Store (ODS), we installed RS on the first dev server and ran the SUA scan. However, we made a judgment call and decided not to run SUA on servers that don’t already have RS on them.
2. Windows Installer v4.5 hotfix: Before installing SUA or SQL upgrade, the latest Windows installer v4.5 needs to be installed. This can be done from the SQL Server 2008 installation package or as a separate download from a Windows hotfix. It is important to plan for a server reboot after installing the hotfix.
3. Visual Studio 2008 – SP1 required for any v3.5 .NET components: If Visual Studio 2008 or components that use Framework 3.5 are on the server to be upgraded, Service Pack 1 for Visual Studio 2008 (VSSP1) needs to be installed before the SQL upgrade begins. Failure to do so may result in certain components not getting upgraded, requiring additional rework.
4. Install packages need to be on local drive for SQL Native Client to upgrade to SP1: When doing a network install from a shared drive, there may be versioning issues with the SQL Native client component that can prevent the installation of SQL 2008 Service Pack 1. To resolve this, it is recommended to copy the upgrade packages to a local drive, perform a complete repair, and then install SP1.
5. When MSCORSVW.EXE is active during SP1 install process, a reboot is required: After upgrading to SQL Server 2008, the v3.5 Framework needs to recompile the installed assemblies. This process takes about 10 to 15 minutes and requires a reboot before the SP1 installation can be completed.
6. Proper sequencing of actions to reduce the number of reboots and failover events: When upgrading clustered servers, it is important to plan the sequencing of actions to minimize reboots and failover events. Applying the Windows installer v4.5 on the passive node before rebooting and performing a manual failover can help reduce the number of failover events.
7. Developers required to upgrade to SQL Server 2008 workstation tools: It is essential to communicate to developers the need to upgrade their client tools to SQL Server 2008. This ensures compatibility and avoids any issues when working with the upgraded SQL database servers.

In Conclusion

The lessons learned from upgrading SQL Server 2008 in our dev/test environment were invaluable in discovering and resolving the “Gotchas” that could have slowed down the production rollout. It is crucial to follow best practices and conduct thorough regression testing of applications before any major upgrade. The benefits of the upgrade became apparent immediately, with faster response times from SQL Server 2008. We are now preparing a whitepaper for developers on the new features of SQL Server 2008 that they should consider for new development projects.

Overall, the upgrade process was successful, and we are excited to explore the new features of SQL Server 2008, such as the Resource Governor and backup compression on the fly. With a test bed to quantify the real gains from these features, we can report to management and justify the time and expense of the SQL Server 2008 upgrade.