Introduction

In December 2006 Microsoft released the Community Technology Preview (CTP) for SQL Server 2005 SP2. As an eagerly anticipated release, Microsoft has a lot at stake with SP2. The first service pack for SQL Server 2005 was reportedly buggy, with a number of hot fixes following hotly on the heels of the SP1 release. Many organisations wait for the second or third service pack before upgrading their products; and this SP2 could tip the balance in favour of a move from SQL 2000 to SQL 2005.

This CTP of the service pack fixes 75 bugs, and includes cumulative hotfix build 2153. I dont see a list of any new features included in the service pack, but there is still time for Microsoft to make changes before the final version is released. A full list of the bugs fixed can be found at: http://support.microsoft.com/?kbid=921896

The service pack appears to be applicable to all editions of SQL Server 2005, including 64 bit versions. This is also supposed to be the Final CTP for this Service Pack.

Installing the product

Download the CTP from http://support.microsoft.com/kb/913089/. The installation files for Express Edition and Express Edition with Advanced Services must be downloaded separately to the files for other Editions.

I am running the installation on a Microsoft Virtual PC running XP Professional. 1024 MB of RAM is allocated to the Virtual PC, and the host machine has a total of 2048 RAM installed. The host is a Dell Dual Core 2GHz laptop with a 7200 RPM SATA drive. The Virtual PC hard drive is stored on an external USB 2.0 hard drive.

The Virtual PC has SQL Server 2005 Developer Edition SP1 (with cumulative hotfix 2153) installed. It is a full installation, with all components and features installed.

On a production server all the SQL services should already be started. Before I began my installation, I started the services on my test box in order to simulate a real-world environment.

To start the install process, download and run file SQLServer2005SP2-KB921896-x86-ENU.exe.

The installation files will be extracted to a folder created in your C:\ drive. This folder is not deleted after the installation.

After the files are extracted, which takes a few minutes, the Welcome screen is presented. Here you can click on a link to see the list of updates.

The following screen is the license agreement, which you will need to accept before continuing.

At the next screen, Feature Selection, I selected to install most features. Some features are optional; most are required.

At the Authentication screen, you will need to supply a sa account which the installation program will use to log onto each service. You can specify one account which will be applied to all instances. At this point, if you have multiple instances you should see them listed here.

You may be familiar with the next screen. Here you are asked if you would like to automatically send error reports and feature usage data to Microsoft. I always leave these options unticked.

The install process will now check for any locked files.

I found that I had several locked files, namely all the SQL services which I had diligently started at the beginning of the install process. If I choose to continue here, a reboot will be required. To avoid a reboot, I must stop the SQL services.

This is when you will start having down-time. You will need to open SQL Server Configuration Manager and stop all the SQL services. These services will still be stopped at the end of the install, so you will need to either reboot or start them manually.

I clicked Refresh, and after a few minutes of scanning, another file was listed : wmiprvse.exe. The following text is a direct quote from www.neuber.com:

Process name: Windows Management Instrumentation

Product: Windows

Company: Microsoft

File: wmiprvse.exe

Windows Management Instrumentation (WMI) is a component of the Microsoft Windows operating system that provides management information and control in an enterprise environment. By using industry standards, managers can use WMI to query and set information on desktop systems, applications, networks, and other enterprise components. Developers can use WMI to create event monitoring applications that alert users when important incidents occur.

In order to avoid a reboot, I open Services from Control Panel / Administrative Tools, and stop the Windows Management Instrumentation service.

By stopping this service, two other services will also be stopped. If you wish to avoid this, you can always let the install reboot your server. If you stop these services manually, remember to start them again after the upgrade has completed.

After clicking Refresh again, I have no locked files and can continue the installation.

The next screen is your last chance to change your mind!

Click Install, and the upgrading of your files will begin. This stage took about 20 minutes on my Virtual PC.

This screen shows the completed installation.

Here you can view the Summary file. The log file location is shown at the bottom of the screen.

I took two screen shots of the log file, so you can get an idea of the kind of information it contains.

The next screen shows a new feature to SQL Server 2005. Users with administration rights on Vista will no longer have sa permissions on SQL Server installations by default. If you plan on upgrading to Vista or later versions of Windows, it is recommended that you complete the Add New Administrator step. To add a new administrator, click the link shown in the screen.

The SQL Server User Provisioning Tool for Vista screen opens. Select the available privileges, and move them to the panel on the right of the screen.

Click OK, and you are ready for Vista!

I have not done too much testing on the Service Pack. The services started successfully, and I was able to open Management Studio and run a simple query.

The new build version for the SQL Database Engine is 3027.

Conclusion

The CTP for Service Pack 2 installed successfully and all services started normally. Although I did not do much testing after the upgrade, this first look proved that SP2 could be installed without glitch, and without a reboot. Most importantly, I had only about 20 minutes of down-time. On a full-spec production server, this could be even less.