Migrating Reporting Services

  • Article

This topic provides step-by-step instructions for migrating a SQL Server 2000 Reporting Services deployment to a new SQL Server 2005 Reporting Services instance. Migrating an installation is useful 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:

  • Backup application and configuration files.
  • Install a new instance of SQL Server 2005 side-by-side your existing SQL Server 2000 installation. Because SQL Server 2000 Reporting Services always runs in the default SQL Server instance, the new SQL Server 2005 instance that you install must be configured as a named instance.
  • Move the report server database from your SQL Server 2000 installation to your new SQL Server 2005 installation. You must create the RSExecRole manually if you are moving the database to a new Database Engine instance.
  • Uninstall SQL Server 2000 Reporting Services 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.

Backup Files and Data

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

  1. Back up the symmetric key for the report server database. Use the rskeymgmt utility to perform this operation.
  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 backup the report server database in Moving a Report Server Database 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.
  4. Back up the report server virtual directory settings. Use Internet Services Information Manager to backup the settings.
  5. For high availability report servers, set the report server database to read-only and modify the RSExec role on the SQL Server Database Engine to stop transactions in the report server databases.

Install SQL Server 2005 Reporting Services

Install Reporting Services and Service Pack 1

  1. Run SQL Server 2005 Setup to install a new instance of Reporting Services.

  2. In the Components to Install page, select Reporting Services. You can also select SQL Server Database Services and Workstation components, Books Online, and development tools if you want to install a new Database Engine instance and Report Designer on the same computer.

  3. In Instance Name, type a new name for the SQL Server 2005 instance you are installing. Do not specify Default Instance. If you do, you will perform an in-place upgrade of the existing installation rather than create a new report server instance.

  4. In Service Account, specify a service account for the Report Server Windows service. For more information about service accounts, see Setting Up Windows Service Accounts.

    Optionally, in Start services at the end of Setup, select SQL Server Agent (SQL Server Agent must be running to support scheduled operations).

  5. In Report Server Installation Options, select the Install but do not configure the server option. Selecting this option allows you to configure the report server after setup is finished.

  6. Finish setup.

  7. If you are applying Service Pack 1, stop all SQL Server 2005 services (including the Report Server Windows service, SQL Server Agent, SQL Server Browser, and SQL Server Fulltext Search).

  8. Apply Service Pack 1, and then restart the services.

Create RSExecRole and Attach the Report Server Database

If you are moving the report server database to an instance of the Database Engine that has never had a report server database on it, you must create the RSExecRole in the Master and MSDB system databases. The role must exist in order for the database upgrade script to run.

If the role does not exist and you attempt to upgrade the database, you will get the following error: "There was a problem applying the database upgrade script."

Reporting Services uses extended stored procedures for SQL Server Agent service to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures to the RSExecRole role.

To create RSExecRole in the Master system database using Management Studio

  1. Start SQL Server Management Studio and connect to the Database Engine instance that hosts the report server database.

  2. Open Databases.

  3. Open System Databases.

  4. Open Master.

  5. Open Security.

  6. Open Roles.

  7. Right-click Database Roles, and select New Database Role. The General page appears.

  8. In Role name, type RSExecRole.

  9. In Owner, type DBO.

  10. Click Securables.

  11. Click Add. The Add Objects dialog box appears. The Specify Objects option is selected by default.

  12. Click OK.

  13. Click Object Types.

  14. Click Extended Stored Procedures.

  15. Click OK.

  16. Click Browse.

  17. Scroll down the list of extended stored procedures and select the following:

    1. xp_sqlagent_enum_jobs
    2. xp_sqlagent_is_starting
    3. xp_sqlagent_notify

  18. Click OK, and the click OK again.

  19. In the Execute row, in the Grant column, click the check box, and then click OK.

Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all three stored procedures.

Create RSExecRole in MSDB

Reporting Services uses stored procedures for SQL Server Agent service and retrieves job information from system tables to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures and Select permissions on the tables to the RSExecRole.

To create RSExecRole in the MSDB system database

  1. Repeat similar steps for granting permissions to stored procedures and tables in MSDB. To simplify the steps, you will provision the stored procedures and tables separately.

  2. Open MSDB.

  3. Open Security.

  4. Open Roles.

  5. Right-click Database Roles, and select New Database Role. The General page appears.

  6. In Role name, type RSExecRole.

  7. In Owner, type DBO.

  8. Click Securables.

  9. Click Add. The Add Objects dialog box appears. The Specify Objects option is selected by default.

  10. Click OK.

  11. Click Object Types.

  12. Click Stored Procedures.

  13. Click OK.

  14. Click Browse.

  15. Scroll down the list of items and select the following:

    1. sp_add_category
    2. sp_add_job
    3. sp_add_jobschedule
    4. sp_add_jobserver
    5. sp_add_jobstep
    6. sp_delete_job
    7. sp_help_category
    8. sp_help_job
    9. sp_help_jobschedule
    10. sp_verify_job_identifiers

  16. Click OK, and the click OK again.

  17. Select the first stored procedure: sp_add_category.

  18. In the Execute row, in the Grant column, click the checkbox, and then click OK.

  19. Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all ten stored procedures.

  20. On the Securables tab, and click Add again. The Add Objects dialog box appears. The Specify Objects option is selected by default.

  21. Click OK.

  22. Click Object Types.

  23. Click Tables.

  24. Click OK.

  25. Click Browse.

  26. Scroll down the list of items and select the following:

    1. syscategories
    2. sysjobs

  27. Click OK, and the click OK again.

  28. Select the first table: syscategories.

  29. In the Select row, in the Grant column, click the checkbox, and then click OK.

Repeat for the sysjobs table. RSExecRole must be granted Select permissions for both tables.

Attach the Report Server Database and Copy Configuration Files

Attach or restore the report server database you backed up from the SQL Server 2000 installation to the new instance. Both the report server database and the temporary database are required and must be moved together.

Do not copy the databases; copying does not transfer all of the security settings to the new installation.

If you are using a new SQL Server 2005 Database Engine instance, you can use SQL Server Management Studio to attach or restore the databases.

Attach the database and copy files

  1. Attach or move the database using the instructions in Moving a Report Server Database to Another Computer. If you are using SQL Server 2000 to host the report server database, create a copy of the database for the new installation.

  2. Copy settings from the rsreportserver.config used in the SQL Server 2000 installation to the rsreportserver.config file of the new SQL Server 2005 installation:

    1. Copy the InstallationID entry. Copying the installation ID is necessary if you want to avoid having two unused entries in the Keys table of the report server database. The two unused rows are the installation identifiers of the SQL Server 2000 instance. The unused rows are incomplete. Missing from each row is the Machine Name and Instance Name. Both Machine Name and Instance Name are undefined for a SQL Server 2000 instance, and as a result there are no values for those columns in the Keys table after a report server database is upgraded. Note that if an entry is missing a machine name or instance name, you cannot delete it in the Reporting Services Configuration tool. However, you can edit the Keys table to remove the rows or to add a missing machine or instance name. You can also leave any unused entries in the table; the unused entries have no effect on report server operations.
    2. Copy any other custom settings that you want to use in the new installation. If you are using custom extensions in your installation, you might need to recompile the extensions before you can use them. For more information, see Upgrade Advisor product documentation.

  3. If you have custom trace settings, copy the entries to the Web.config or Machine.config files of the new installation.

Configure a Report Server

Configure report server settings

  1. Start the Reporting Services Configuration tool and connect to the SQL Server 2005 Reporting Services instance you just installed.

  2. Create virtual directories for the report server and Report Manager. Virtual directory names must be unique, so if you used the default names in the SQL Server 2000 Reporting Services installation you must choose non-default names for the SQL Server 2005 installation.

    When you create the Report Server virtual directory, the Web service identity should be configured automatically. If it is not configured correctly, you can create a new application pool in which to run the service.

    The Report Server Windows service should be configured automatically during Setup. If you want to run the service under a different account, you can modify it now. For more information about the service accounts, see Setting Up Windows Service Accounts and Connections and Accounts in a Reporting Services Deployment in SQL Server Books Online.

  3. On the Server Status page, click Stop to stop the Report Server Windows service. This step is very important. If you do not stop the service, the database upgrade operation might time out before the schema is fully updated, leaving the database in an intermediate state.

  4. On the Database Setup page, in Server Name, select the SQL Server instance that hosts the report server database that you want to upgrade and click Connect.

  5. Choose the report server database that you want to use in the SQL Server 2005 installation.

  6. Click Upgrade. Both the report server database and the temporary database are upgraded to the new schema. There was a problem applying the database upgrade script."

  7. Specify the account used to connect the report server to the report server database.

  8. On the Encryption Keys page, restore the key that is used to encrypt and decrypt content in the report server database. This is the encryption key that you backed up in step 1 of the "Backup Files and Data" section in this topic.

  9. Test the report server and Report Manager virtual directories by opening a browser and typing in the URL address. URLs and database connection information that you specify for that instance must include the instance name.

  10. 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.

  11. Uninstall SQL Server 2000 Reporting Services.

  12. Rename the virtual directories to the names used in the previous installation. If you used custom virtual directory settings, update the virtual directories you created to use the new values.

See Also

Concepts

Upgrading Reporting Services

Other Resources

Report Server Database
Reporting Services Backward Compatibility
Reporting Services Configuration Tool

Help and Information

Getting SQL Server 2005 Assistance