Migrate a Reporting Services Installation (Native Mode)

This topic provides step-by-step instructions for migrating one of the following supported versions of a Reporting Services native mode deployment to a new SQL Server 2012 Reporting Services instance:

For information on migrating a Reporting Services SharePoint mode deployment, see Migrate a Reporting Services Installation (SharePoint Mode).

Migration is defined as moving application data files to a new SQL Server 2012 instance. You must migrate your installation if you encountered an issue that blocks upgrade, if you have a large-scale deployment or uptime requirements, or if you are changing the hardware or topology of your installation.

In this topic:

  • Native Mode Migration Overview

  • Content Only Migration

  • Fixed Database Name

  • Before You Start

  • Backup Files and Data

  • Install Reporting Services

  • Move the Report Server Database

  • Move Custom Assemblies or Extensions

  • Configure the Report Server

  • Copy Custom Configuration Settings to RSReportServer.config File

  • Windows Service Group and Security ACLs

  • Verify Your Deployment

  • Remove Unused Programs and Files

Native Mode Migration Overview

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 2012. If you are using the same hardware, you can install SQL Server 2012 side-by-side your existing SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 installation. Be aware that if you do this, you might need to install SQL Server 2012 as a named instance.

  • Move the report server database and other application files from your existing installation to your new SQL Server 2012 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 Create a Report Server Database.

Arrow icon used with Back to Top linkTop

Content Only Migration

Tip

If you want to only migrate content items from one report server to another, see the following resources:

Arrow icon used with Back to Top linkTop

Fixed Database Name

You cannot rename the report server database. The identity of the database is recorded in report server stored procedures when the database is created. Renaming either the report server primary or temporary databases causes errors when the procedures run, invalidating your report server installation.

If the database name from the existing installation is not suited for the new installation, you should consider creating a new database that has the name that you prefer, and then load existing application data using the techniques in the following list:

  • Write a Visual Basic script that calls Report Server Web service SOAP methods to copy data between databases. You can use the RS.exe utility to run the script. For more information about this approach, see Scripting (Reporting Services SSRS).

  • Write code that calls the WMI provider to copy data between databases. For more information about this approach, see Access the Reporting Services WMI Provider.

  • If you have just a few items, you can republish reports, report models, and shared data sources from Report Designer, Model Designer, and Report Builder to the new report server. You must re-create role assignments, subscriptions, shared schedules, report snapshot schedules, custom properties that you set on reports or other items, model item security, and properties that you set on the report server. You will lose report history and report execution log data.

Arrow icon used with Back to Top linkTop

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 2012 installation.

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

  • Starting with SQL Server 2008, 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 and later versions. 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 and later versions. 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 and later versions. 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 and later versions.

  • 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 2012 Reporting Services, see the Upgrade Advisor documentation and What's New (Reporting Services).

Arrow icon used with Back to Top linkTop

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.

  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.

Arrow icon used with Back to Top linkTop

Install SQL Server 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:

Arrow icon used with Back to Top linkTop

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 section Move Custom Assemblies or Extensions.

To move the report server database, do the following:

  1. Choose the Database Engine instance to use. SQL Server 2012 Reporting Services requires that you use SQL Server 2005, 2008, 2008 R2, or SQL Server 2012 to host the report server database. SQL Server 2000 can no longer be used to host the report server database for SQL Server 2012 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 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.

Arrow icon used with Back to Top linkTop

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 2005 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 and later versions.

    • Other custom assemblies should not require recompilation.

  2. Move the assemblies to the new report server and Report Manager \bin folders. In SQL Server 2012, the report server binaries are located in \Program files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin for the default SQL Server 2012 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

Arrow icon used with Back to Top linkTop

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 2012 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 Add and Remove Encryption Keys for Scale-Out Deployment and Configure and Manage 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 Configure a URL.

  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 2012 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 Create a Native Mode Report Server Database (Reporting Services).

  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 Back Up and Restore Reporting Services Encryption Keys (SSRS Native Mode).

  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 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 Configure a Native Mode Report Server for Local Administration (SSRS).

Arrow icon used with Back to Top linkTop

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

Customization

Information

Report Server E-mail delivery with custom settings

How to: Configure a Report Server for E-mail Delivery

Device information settings

Customize Rendering Extension Parameters in RSReportServer.Config

Report Manager on a remote instance

Configure Report Manager

Arrow icon used with Back to Top linkTop

Windows Service Group and Security ACLs

In SQL Server 2012 Reporting Services (SSRS), 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 2012 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 2012 Reporting Services.

Arrow icon used with Back to Top linkTop

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 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 2012 report object model when processing and rendering reports, but it does not replace SQL Server 2008 or SQL Server 2008 R2 constructs with new report definition language elements. To learn more about how existing reports run on a SQL Server 2012 report server, see Upgrade Reports.

Arrow icon used with Back to Top linkTop

Remove Unused Programs and Files

Once you have successfully migrated your report server to a SQL Server 2012 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.

Arrow icon used with Back to Top linkTop

See Also

Concepts

Migrate a Reporting Services Installation (SharePoint Mode)

Report Server Database

Upgrade and Migrate Reporting Services

Reporting Services Configuration Manager (SSRS)

Other Resources

Reporting Services Backward Compatibility