How to: Upgrade a SQL Server Express Report Server to Another SQL Server Edition

Article
12/03/2008

New: 14 April 2006

SQL Server 2005 Express Edition with Advanced Services (Express Edition with Advanced Services) is a new edition of SQL Server 2005 that was introduced in Service Pack 1 (SP1). It includes a subset of Reporting Services functionality. To use additional features, you must upgrade to a different edition.

Note

Not all Reporting Services features are available in every edition. If you are upgrading SQL Server Express Edition with Advanced Services because you want to use a specific feature, verify that the edition supports it. For more information, see Features Supported by the Editions of SQL Server 2005. If you are also upgrading the operating System from Windows XP to Windows Vista, see How to: Upgrade Reporting Services on Windows Vista for additional instructions on how to enable a report server on Windows Vista.

Upgrading to Other Versions and Editions

You can upgrade the Express Edition with Advanced Services to the Standard, Developer, Enterprise, or Workgroup Edition of SQL Server 2005 Reporting Services.

Because the original product release of the Express Edition with Advanced Services uses the SP1 program files and database schema, the application files and database schema that are in the current installation will be newer than the original product release files of the edition you are upgrading to. This will result in upgrade warnings and version discrepancy errors. To work through the version discrepancy, do the following:

Run the Setup program with a command line argument SKUUPGRADE=1 to upgrade the SP1 Express Edition with Advanced Services to a pre-SP1 release of a different edition.
Apply SP1 or SP2 to replace the pre-SP1 program files with updated versions of the files.

Preparing to Upgrade

Before you upgrade Express Edition with Advanced Services, you must back up the report server database, configuration files, and data files.

You must also determine whether to upgrade the Database Engine component of Express Edition with Advanced Services, or use a different edition and instance of the Database Engine to host the report server database.

Back Up Files

Back up the report server database and report server temporary database. By default, the databases are named reportserver$SQLExpress and reportserverTempDB$SQLExpress. For more information, see Backup and Restore Operations for a Reporting Services Installation.
Back up the RSReportServer.config file. If you modified other configuration files (such as Web.config or RSWebApplication.config), you should back them up.
Back up the encryption key using the Encryption Keys page in the Reporting Services Configuration tool. For more information, see Backing Up and Restoring Encryption Keys.

Upgrade the Database Engine and Workstation Components

To simplify the upgrade and minimize configuration steps after setup is complete, you should upgrade Reporting Services, client tools, and the Database Engine together.

Reporting Services has edition requirements for the instance of the SQL Server Database Engine that hosts the report server database. After the report server is upgraded, you can no longer use the Express Edition with Advanced Services to host a report server database.

If you choose to upgrade only Reporting Services, you must attach the report server database to the Database Engine instance you want to use, and then configure the report server to use that database. You can use any local or remote SQL Server 2000 or SQL Server 2005 Database Engine instance that is valid for the report server edition you are using. For more information about edition requirements for report server databases, see Creating a Report Server Database.

Run Setup to Upgrade Express Edition with Advanced Services

To upgrade your installation, you must use the SKUUPGRADE=1 argument. You can specify this argument when you start the SQL Server Setup wizard. Or, you can run Setup from the command prompt and include the SKUUPGRADE=1 argument on the command line.

To upgrade using the Installation Wizard

Open a command prompt, navigate to the directory or drive that contains the installation media, and specify setup.exe SKUUPGRADE=1 on the command line (for example, d:\setup.exe SKUUPGRADE=1).
On the System Configuration Check page, you should see a single warning for SQL Server Edition Operating System Compatibility that warns you to upgrade to SP2. The Edition Change Check should read "Success". If it does not, restart Setup with the SKUUPGRADE=1 argument.
On the Registration Information page, enter identification information used to register your copy of SQL Server.
On the Components to Install page, select Database Services, Reporting Services, and Workstation components, Books Online and development tools if you are upgrading all components together.
On the Instance Name page, click Named instance, and then choose the SQLEXPRESS instance you are upgrading.
Complete the wizard by specifying options on the remaining pages.
Apply SP1 or SP2 after Setup is complete.
Start the services if you stopped them during Setup.

To upgrade using a command line installation

Open a command prompt window, and then navigate to the directory or drive that contains the installation media.
To run Setup in unattended mode from the command line, use syntax that is similar to the following example. Note that the PIDKEY is fictitious; be sure to replace it with a PIDKEY that is valid for the edition you are installing. For more information about running Setup from the command prompt, see How to: Install SQL Server 2005 from the Command Prompt.
```
start /wait setup.exe ADDLOCAL=SQL_Engine,RS_Server PIDKEY=ABCDE12345FGHIJ67890KLMNO INSTANCENAME=SQLEXPRESS UPGRADE=SQL_Engine,RS_Server SKUUPGRADE=1 /qb
```
Apply SP1 or SP2 after Setup is complete.
Start the services if you stopped them during Setup.

After upgrade is finished, you must apply Service Pack 1. Otherwise, you will get a database compatibility error message.

Note that once you begin upgrading your SQL Server Express installation, the report server and content will not be available until all of these steps are complete.

Apply SP1 or SP2

The Express Edition with Advanced Services is based on SP1 and uses the SP1 version of the report server database schema. When you upgrade from Express to a different edition of SQL Server, the schema of the report server database retains the SP1 format and is not modified during the upgrade. To use the report server database from the previous installation in an upgraded installation, you must apply at least SP1 to avoid compatibility problems between the report server and the report server database.

If possible, install SP2 instead of SP1. SP2 includes all SP1 functionality plus additional enhancements. Furthermore, SP2 is required if you are upgrading to Windows Vista.

To download the latest service pack, see How to obtain the latest SQL Server 2005 Service Pack on the Microsoft Web site.

Verify Upgrade

To verify that the upgrade succeeded, open Report Manager. You should be able to view all of the reports that you previously created. If you have not changed the virtual directory, the URL used to access Report Manager in the upgraded installation will be the same URL you used in the Express installation.

Unless you rename them, an upgraded installation continues to use names from the original installation. If you used default SQL Server Express names, the upgraded SQL Server instance name continues to be <servername>\SQLExpress. For the databases, the default names continue to be reportserver$sqlexpress and reportservertempdb$sqlexpress. For the Reporting Services URLs, the default names continue to be http://<servername>/reports$SQLExpress and http://<servername>/reportserver$SQLExpress. To avoid confusion, you can reregister the SQL Server instance to change the instance name, and rename the report server virtual directories. Renaming a report server database is not supported. For more information, see Registering Servers and Configuring Report Server Virtual Directories in SQL Server Books Online.

In addition to viewing existing report server content, you should also be able to create subscriptions and scheduled operations, and use other features that are available in the edition you installed. For more information about how to create subscriptions and schedules, see Report Manager How-to Topics. If the report server upgrade did not succeed or seems incomplete, you can migrate the installation instead. For more information, see Migrating Reporting Services.

Verify Tools

You can specify client components on the command line to upgrade SQL Server Management Studio and Business Intelligence Development Studio. You can verify whether the tools upgraded correctly by doing the following:

Connect to a Reporting Services instance in SQL Server Management Studio.
Create additional project types in Business Intelligence Development Studio (specifically, the Report Model project template should be available when you create a new project in Business Intelligence Development Studio).

If the tools do not upgrade correctly, you can use Add or Remove Programs to uninstall the tools you no longer need, and then run SQL Server Setup to install the tools that are included in the edition you are upgrading to.

Modify Connection Strings

If you are migrating other SQL Server Express databases that provide data to reports to another Database Engine, be sure to update the data source connection information used by published reports after the upgrade is complete. For example, if a data source specified Data Source=localhost\sqlexpress, it should be updated to Data Source=localhost if you migrate the database to a local default instance.