Upgrading SQL Server editions

How to upgrade your SQL Server edition (without reinstalling)

With the updated SQL Server 2008 installation program, upgrading SQL Server editions has finally been made as straightforward process as it always should have been.

In SQL Server 2005 upgrading/changing the edition involved running the setup program from the command line with a multitude of specific parameters set.

This blog posting covers SQL Server 2005 and SQL Server 2008.

Before you start, make sure you are on a supported upgrade path as described in the edition upgrade matrix (for SQL Server 2005) on Microsoft’s website.

Assuming you are on a supported upgrade path (i.e. not trying to ‘upgrade’ a 32-bit edition of SQL Server to a 64-bit edition), the next step is to dig out the installation files and run the setup program.

Upgrading SQL Server 2005

This is where it gets unnecessarily complicated if you’re upgrading a SQL Server 2005 installation. Basically, you have to run the install via the command line and send the setup program the relevant parameters to upgrade the relevant SQL Server components you want to upgrade.

So, assuming you want to upgrade SQL Server 2005 Standard Edition to SQL Server 2005 Enterprise Edition, you’d open a DOS prompt in the same folder as the install media and run the following command:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=DesktopSQL2K5EXP UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

The above command line upgrades the SQL Server instance named SQL2K5EXP on machine Desktop to whatever edition the install media contains.

The multitude of parameters here is what causes all the confusion and the problems when trying to carry out something that should really be fairly straightforward, so make sure your syntax matches what is above when you’re trying the upgrade.

A compromise method involves invoking the gui component of the installation by running just the following:

start /wait setup.exe SKUUPGRADE=1

Which will start the graphical setup program and step through the appropriate options to complete the edition upgrade.

Upgrading SQL Server 2008

Fortunately, Microsoft have realised the upgrade mechanism offered for SQL Server 2005 was lacking in some areas and have addressed the issue via the gui component of the setup program, so now the same setup program you use to install SQL Server 2008 can be used to upgrade SQL Server 2008. Crazy idea, I know.

Figure 1 below shows the setup GUI with the Installation Maintenance option selected:

[caption id="" align=“alignnone” width=“546”] Figure 1 below shows the setup GUI with the Installation Maintenance option selected[/caption]

Clicking on Edition Upgrade will result in a couple of Setup Support Rules checks running which will check e.g. whether the WMI service is running, the OS version and whether the user running the setup application has the relevant privileges to complete the install.

Assuming you get past these checks you’ll end up at Figure 2 which is the Product Key dialog where you can select either a free edition to ‘upgrade’ to or enter the Product Key that accompanies the edition of SQL Server that the setup program was launched from.

After selecting the relevant option the install is pretty much the same as a normal install; accept the licence agreement, choose the instance name to be upgraded and let the install run through and complete. Simple.

Validating the edition change

The following query will confirm the edition of SQL Server:

select serverproperty(‘Edition’)

This will return the edition, and if the install went through correctly it will confirm that SQL Server is now running the expected edition.

Troubleshooting the installation

A run-through of troubleshooting SQL Server installs is slightly out of scope and there are plenty of KBs and webcasts on the Microsoft web site that will take you through that.

All I will say is look at the summary.txt file in the setup bootstrap directory (default location is C:Program FilesMicrosoft SQL Server100Setup BootstrapLog) to identify the initial failure. This should provide you with a summary of the problem and further details will be found in a subdirectory off the location of the summary.txt file which will be named after the date and time of the setup run.

How to: Install SQL Server 2005 from the Command Prompt

How to: Upgrade to a Different Edition of SQL Server 2008 (Setup)