Microsoft SQL Server 2005 is no longer just the relational database engine. It is packaged with a number of components and tools to provide the ultimate data management and business intelligence platform. Designing the installation and ensuring a smooth upgrade to SQL Server 2005 was not an easy task. The SQL Server development team had to provide a consistent experience for the installation and upgrade of all SQL Server 2005 components. With the installation and upgrade being the first out-of-the-box experience, the team wanted to create a positive first impression. The installation interface had to have a friendly and easy-to-use interface to guide the novice user through the installation steps; yet it had to be robust and versatile to meet the advanced specifications of large IT organizations. Using technologies such as Windows Installer, MSI, WMI, and Wizard UI, the team was successful in creating a setup application for the installation experience. However, upgrade was a different story. The early adopter customers who were part of the beta and CTP (Community Technology Preview) program were reporting numerous backward compatibility issues. SQL Server 2005 was a long release and during its five-year cycle, some legacy features had gone through many changes, some of which were no longer backward compatible with earlier releases of SQL Server. This meant customer applications that used these features in SQL Server 7.0 or SQL Server 2000 did not work after the server was upgraded to SQL Server 2005. This was unacceptable. Two teams were formed just to focus on and tackle the upgrade and backward compatibility issues. These teams collected all the reported issues and they classified the issues into four categories:
The first class of issues was simply awful. They were problems that simply prohibited customers from upgrading their systems to SQL Server 2005. What it made it worse was the "point of no return scenario," where the upgrade blockers terminated the setup program at a point where it had partially updated the system to SQL Server 2005. This meant the upgrade could not be rolled forward or backward and the system was left in an unusable state. To solve this class of issues, the team set a high-quality bar and triaged each of the upgrade blockers with the development team. Soon, the number of upgrade blockers diminished: At first there were 23, then the number dropped to 14, and it finally stopped at 9. These 9 issues were true edge cases that rarely happen in real life scenarios. You can find these issues under the topic "Issues that Prevent Upgrading" in Upgrade Advisor help. In addition, the setup program added the logic to check for the upgrade blockers prior to updating the system. This prevented the "point of no return" scenario.
The effort behind tackling the second and third classes of upgrade issues was huge. Although the team worked hard to minimize the number of features with different behavior, it ran into two major issues. For some features, it was too late to revert or redesign the feature to achieve backward compatibility. The development team proved that redesigning some features would impact the stability of these features, weakening their quality. Another issue was related to discontinuation of undocumented and unsupported features. For example, for years authors of SQL Server books and articles had written about undocumented system stored procedures (like xp_regread) and customers had implemented these undocumented features in their applications even though they were not officially supported by Microsoft. The SQL Server development team needed to extend, enhance, or replace some of these undocumented features with the new and more powerful features, but with lack of documentation, it was impossible to understand all customer scenarios. The question became how to inform and educate customers on the upgrade and compatibility issues that are relevant to their environments. The answer was a new tool name, Upgrade Advisor. The goal behind the design of Upgrade Advisor was to improve the SQL Server 2005 upgrade experience. The tool had to run before any upgrade to inform the user of the compatibility issues, thereby eliminating any surprises during or after the upgrade. In addition to detecting issues, Upgrade Advisor had to provide guidance and documentation for fixing or implementing workarounds. The upgrade checks (rules) in Upgrade Advisor also had to be easily updatable, so as the team came across additional issues they could easily be added to the tool.
For the fourth class of upgrade issues, the team decided to build a new tool named Surface Area Configuration (described in Chapter 3). The idea behind the tool is simple: Provide an integrated and easy-to-use tool to enable and disable elements that were switched off by default. In addition, the Surface Area Configuration had to provide advisory documentation to describe each of the surface area elements in detail to help administrators understand the side effects of enabling or disabling an element.
Both the Upgrade Advisor and the Surface Area configuration tools were introduced in the July 2005 CTP release of SQL Server 2005 and they were an immediate success. The number of upgrade-related issues reduced significantly. By the next CTP release, the upgrade issues were almost non-existent.
Once again, this proves the significance of SQL Server's tools in its success story. The Setup and Installation tools, Upgrade Advisor, and Surface Area Configuration have been crucial in the success of SQL Server 2005.
This chapter is in three main parts. The first part describes pre-installation considerations and the installation tools. The second part walks you through the installation process step-by-step. The third part focuses on upgrades and scenarios that are enabled by the upgrade tools. We also share some of the tips and tricks for ensuring a smooth and trouble-free installation and upgrade to SQL Server 2005.