Share via

  • Article

How to: Migrate a Reporting Services Installation

Note

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

This topic provides step-by-step instructions for migrating a SQL Server 2000 or SQL Server 2005 Reporting Services deployment to a new SQL Server 2008 Reporting Services instance. Migration is defined as moving application data files to a new SQL Server 2008 instance. You must migrate your installation if you encountered an upgrade blocker, have a large scale deployment or up-time requirements, or if you are changing the hardware or topology of your installation.

The migration process for Reporting Services includes manual and automated steps. The following tasks are part of a report server migration:

  • Back up database, application, and configuration files.

  • Back up the encryption key.

  • Install a new instance of SQL Server 2008. If you are using the same hardware, you can install SQL Server 2008 side-by-side your existing SQL Server 2000 or 2005 installation. Be aware that if you do this, you might need to install SQL Server 2008 as a named instance.

  • Move the report server database and other application files from your SQL Server 2000 or 2005 installation to your new SQL Server 2008 installation.

  • Move any custom application files to the new installation.

  • Configure the report server.

  • Edit RSReportServer.config to include any custom settings from your previous installation.

  • Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.

  • Test your installation.

  • Remove unused applications and tools after you have confirmed that the new instance is fully operational.

If you are reusing an existing report server database that you created in a previous installation, be aware that there are restrictions on the editions of SQL Server you use to host the report server database. For more information, see Creating a Report Server Database.

Before You Start

Even though you are migrating rather than upgrading the installation, consider running Upgrade Advisor on your existing installation help identify any issues that could affect migration. This step is especially helpful if you are migrating a report server that you did not install or configure. By running Upgrade Advisor, you can find out about custom settings that might not be supported in a new SQL Server 2008 installation.

In addition, you should be aware of several important changes in SQL Server 2008 that will affect how you migrate your installation:

  • IIS is no longer a prerequisite. If you are migrating a report server installation to a new computer, you do not need to add the Web server role. In addition, steps for configuring URLs and authentication are different from the previous release, as are techniques and tools for diagnosing and troubleshooting problems.

  • Report Server Web service, Report Manager, and the Report Server Windows service are consolidated within a single Report Server service. All three applications run under the same account. All three applications read configuration settings from RSReportServer.config file, making RSWebApplication.config obsolete.

  • Report Manager and SQL Server Management Studio are redesigned to remove overlapping features. Each tool supports a distinct set of tasks; the tools are no longer interchangeable.

  • ISAPI filters are not supported in SQL Server 2008 Reporting Services. If you use ISAPI filters, you must redesign your reporting solution prior to migration.

  • IP address restrictions are not supported in SQL Server 2008 Reporting Services. If you use IP address restrictions, you must redesign your reporting solution prior to migration or use a technology such as a firewall, router, or Network Address Translation (NAT) to configure addresses that are restricted from accessing the report server.

  • Client Secure Sockets Layer (SSL) certificates are not supported in SQL Server 2008 Reporting Services. If you use client SSL certificates, you must redesign your reporting solution prior to migration.

  • If you use an authentication type other than Windows Integrated authentication, you must update the <AuthenticationTypes> element in the RSReportServer.config file with a supported authentication type. The supported authentication types are NTLM, Kerberos, Negotiate, and Basic. Anonymous, .NET Passport, and Digest authentication are not supported in SQL Server 2008 Reporting Services.

  • If you use custom cascading style sheets in your reporting environment, they will not be migrated. You must manually move them following migration.

For more information about changes in SQL Server 2008 Reporting Services, see the Upgrade Advisor documentation and What's New (Reporting Services).

Backup Files and Data

Before you install a new instance of Reporting Services, be sure to back up all of the files in your current installation.

  1. Back up the encryption key for the report server database. This step is critical to migration success. Further on in the migration process, you must restore it for the report server to regain access to encrypted data. To back up the key, use the Reporting Services Configuration tool (or rskeymgmt utility if you are migrating from SQL Server 2000).

  2. Back up the report server database using any of the supported methods for backing up a SQL Server database. For more information, see the instructions on how to back up the report server database in Moving the Report Server Databases to Another Computer.

  3. Back up the report server configuration files. Files to back up include:

    1. Rsreportserver.config

    2. Rswebapplication.config

    3. Rssvrpolicy.config

    4. Rsmgrpolicy.config

    5. Reportingservicesservice.exe.config

    6. Web.config for both the Report Server and Report Manager ASP.NET applications.

    7. Machine.config for ASP.NET if you modified it for report server operations.

Install SQL Server 2008 Reporting Services

Install a new report server instance in files-only mode so that you can configure it to use non-default values. For command line installation, use the FilesOnly argument. In the Installation Wizard, select the Install but do not configure option.

Click one of the following links to view instructions on how to install a new instance of Reporting Services:

Move the Report Server Database

The report server database contains published reports, models, shared data sources, schedules, resources, subscriptions, and folders. It also contains system and item properties, and permissions for accessing report server content.

If your migration includes using a different Database Engine instance, you must move the report server database to the new Database Engine instance. If you are using the same Database Engine instance, skip to "Configure the Report Server".

To move the report server database, do the following:

  1. Choose the Database Engine instance to use. SQL Server 2008 Reporting Services requires that you use SQL Server 2005 or 2008 to host the report server database. SQL Server 2000 can no longer be used to host the report server database for SQL Server 2008 Reporting Services. If you used SQL Server 2000 or SQL Server 2005 running in 8.0 compatibility mode, you must upgrade the Database Engine or choose another Database Engine instance.

  2. Start SQL Server Management Studio and connect to the Database Engine.

  3. Create the RSExecRole in the system databases if the Database Engine has never hosted a report server database. For more information, see How to: Create the RSExecRole.

  4. Follow the instructions in Moving the Report Server Databases to Another Computer.

Remember that both the report server database and the temporary database are interdependent and must be moved together. Do not copy the databases; copying does not transfer all of the security settings to the new installation. Do not move SQL Server Agent jobs for scheduled report server operations. The report server will recreate these jobs automatically.

Move Custom Assemblies or Extensions

If your installation includes custom report items, assemblies, or extensions, you must redeploy the custom components. If you are not using custom components, skip to "Configure the Report Server".

To redeploy the custom components, do the following:

  1. Determine whether the assemblies are supported or need recompilation:

    • Custom authentication extensions created for the SQL Server 2000 release must be recompiled.

    • Custom rendering extensions for SQL Server 2008 Reporting Services must be rewritten using the Rendering Object Model (ROM).

    • HTML 3.2 and HTML OWC renderers are not supported in SQL Server 2008 Reporting Services.

    • Other custom assemblies should not require recompilation.

  2. Move the assemblies to the new report server and Report Manager \bin folders. In SQL Server 2008, the report server binaries are located in \Program files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin for the default SQL Server 2008 Reporting Services instance.

  3. Modify the configuration files to add entries for your custom component. The entries will vary depending on the kind of assembly you are using. For instructions on where to place files and add configuration entries, see the following:

    1. Deploying a Custom Assembly

    2. How to: Deploy a Custom Report Item

    3. Deploying a Data Processing Extension

    4. Deploying a Delivery Extension

    5. Deploying a Rendering Extension

    6. Implementing a Security Extension

Configure the Report Server

Configure URLs for the Report Server Web service and Report Manager, and configure the connection to the report server database.

If you are migrating a scale-out deployment, take all of the report server nodes offline and migrate each server one at a time. Once the first report server is migrated and it successfully connects to the report server database, the report server database version is automatically upgraded to the SQL Server 2008 database version.

Important

If any of the report servers in the scale-out deployment are online and have not been migrated, they might encounter an rsInvalidReportServerDatabase exception because they are using an older schema when connected to the upgraded.

Note

If the report server you migrated was configured as the shared database for a scale-out deployment, you need to delete any of the old encryption keys from the Keys table in the ReportServer database, before configuring the report server service. If the keys are not removed, the migrated report server will try to initialize in scale-out deployment mode. For more information, see Adding and Removing Encryption Keys for Scale-Out Deployment and Configuring and Managing Encryption Keys.

The scale-out keys cannot be deleted by using the Reporting Services Configuration Manager. The old keys must be deleted from the Keys table in the ReportServer database using SQL Server Management Studio. Delete all rows in the Keys table. This will clear the table and prepare it for restoring the Symmetric key only, as documented in the following steps.

Prior to deleting the keys it is recommended you first back up the Symmetric Encryption key. You can use the Reporting Services Configuration Manager to back up the key. Open the Configuration Manager open, click the Encryption Keys tab and then click the Backup button. You can also script WMI commands to back up the encryption key. For more information on WMI, see BackupEncryptionKey Method (WMI MSReportServer_ConfigurationSetting).

  1. Start the Reporting Services Configuration tool and connect to the Reporting Services instance you just installed. For more information, see How to: Start Reporting Services Configuration Manager.

  2. Configure URLs for the report server and Report Manager. For more information, see How to: Configure a URL (Reporting Services Configuration).

  3. Configure the report server database, selecting the existing report server database from your previous installation. After successful configuration, the report server services will restart, and once a connection is made to the report server database, the database will be automatically upgraded to SQL Server 2008 Reporting Services. For more information about how to run the Change Database Wizard that you use to create or select a report server database, see How to: Create a Report Server Database (Reporting Services Configuration).

  4. Restore the encryption keys. This step is necessary for enabling reversible encryption on pre-existing connection strings and credentials that are already in the report server database. For more information, see Backing Up and Restoring Encryption Keys.

  5. If you installed report server on a new computer and you are using Windows Firewall, be sure that the TCP port on which the report server listens is open. By default, this port is 80. For more information, see How to: Configure a Firewall for Report Server Access.

  6. If you installed report server on Windows Vista or Windows Server 2008, create role assignments and add the report server site to Trusted Sites to support local administration. For more information, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008 (UAC).

Copy Custom Configuration Settings to RSReportServer.config File

If you modified the RSReportServer.config file or RSWebApplication.config file in the previous installation, you should make the same modifications in the new RSReportServer.config file. The following list summarizes some of the reasons why you might have modified the previous configuration file and provides links to additional information about how to configure the same settings in SQL Server 2008.

Customization

Information

Report Server E-mail delivery with custom settings

How to: Configure a Report Server for E-mail Delivery (Reporting Services Configuration)

Device information settings

Customizing Rendering Extension Parameters in RSReportServer.Config

Report Manager on a remote instance

How to: Configure Report Manager

Windows Service Group and Security ACLs

In SQL Server 2008 Reporting Services, there is one service group, the Reporting Services Windows Service group, which is used to create security ACLs for all the registry keys, files, and folders that are installed with SQL Server 2008 Reporting Services. This Windows group name appears in the format SQLServerReportServerUser$<computer_name>$<instance_name>. This group takes the place of the two Windows service groups in SQL Server 2005 Reporting Services. If you have custom ACLs associated with either of the SQL Server 2005 Reporting Services Windows groups, you will need to apply those ACLs to the new group for your new report server instance in SQL Server 2008 Reporting Services.

Verify Your Deployment

  1. Test the report server and Report Manager virtual directories by opening a browser and typing in the URL address. For more information, see How to: Verify a Reporting Services Installation.

  2. Test reports and verify they contain the data you expect. Review data source information to see whether the data source connection information is still specified. The report server uses the SQL Server 2008 report object model when processing and rendering reports, but it does not replace SQL Server 2000 or 2005 constructs with new report definition language elements. To learn more about how existing reports run on a SQL Server 2008 report server, see Upgrading Reports.

Remove Unused Programs and Files

Once you have successfully migrated your report server to a SQL Server 2008 Reporting Services instance, you might want to perform the following steps to remove programs and files that are no longer necessary.

  1. Uninstall the previous version of Reporting Services if you no longer need it. This step does not delete the following items, but you can manually remove them if you no longer need them:

    • The old Report Server database

    • RsExec role

    • Report Server service accounts

    • Application pool for the Report Server Web service

    • Virtual directories for Report Manager and the report server

    • Report server log files

  2. Remove IIS if you no longer need it on this computer.

  3. Delete RSActivate.exe (from SQL Server 2000 installations only).