THIS TOPIC APPLIES TO: SQL Server Reporting Services (2016) SQL Server Reporting Services (2017) Power BI Report Server
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 Reporting Services instance:
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
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 instance. The following are common reasons you must migrate your installation:
You have a large-scale deployment or uptime requirements.
You are changing the hardware or topology of your installation.
You encounter an issue that blocks upgrade.
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. If you are using the same hardware, you can install SQL Server side-by-side with your existing installation if it was one of the supported versions.
A side-by-side installation may require that you install SQL Server as a named instance.
Move the report server database and other application files from your existing installation to your new SQL Server 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.
Remove unused applications and tools after you have confirmed that the new instance is fully operational.
There are restrictions on the editions of SQL Server that host the report server database. Review the following topic if you are reusing a report server database that was created in a previous installation.
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, 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 and PowerShell with Reporting Services.
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.
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 installation.
In addition, you should be aware of several important changes in SQL Server Reporting Services that will affect how you migrate your installation:
The new web portal has replaced Report Manager.
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, the web portal, and the Report Server Windows service run under the same account. All three applications read configuration settings from RSReportServer.config file.
The web portal and SQL Server Management Studio are designed to remove overlapping features. Each tool supports a distinct set of tasks.
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 Reporting Services, see the Upgrade Advisor documentation and What's New in 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.
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 Manager.
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 (SSRS Native Mode).
Back up the report server configuration files. Files to back up include:
Web.config for the Report Server ASP.NET application.
Machine.config for ASP.NET if you modified it for report server operations.
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:
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:
Choose the Database Engine instance to use. SQL Server Reporting Services requires that you use one of the following versions to host the report server database:
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
Start SQL Server Management Studio and connect to the Database Engine.
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.
Follow the instructions in Moving the Report Server Databases to Another Computer (SSRS Native Mode).
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 section Configure the Report Server.
To redeploy the custom components, do the following:
Determine whether the assemblies are supported or need recompilation:
Custom security extensions must be re-written using the IAuthenticationExtension2 interface.
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.
Move the assemblies to the new report server \bin folder. In SQL Server, the report server binaries are located in the following location for the default report server instance:
\Program files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin
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:
Configure the Report Server
Configure URLs for the Report Server Web service and web portal, 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 database version.
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.
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 (SSRS Configuration Manager) and Configure and Manage Encryption Keys (SSRS Configuration Manager).
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).
Start the Reporting Services Configuration Manager and connect to the Reporting Services instance you just installed. For more information, see Reporting Services Configuration Manager (Native Mode).
Configure URLs for the report server and the web portal. For more information, see Configure a URL (SSRS Configuration Manager).
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 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.
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.
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.
If you want to administer your native mode report server locally, you need to configure the operating system to allow local administration with the web portal. For more information, see Configure a Native Mode Report Server for Local Administration.
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 2016.
|Report Server E-mail delivery with custom settings||E-Mail Settings - Reporting Services Native mode.|
|Device information settings||Customize Rendering Extension Parameters in RSReportServer.Config|
Windows Service Group and Security ACLs
In SQL Server 2016 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 Reporting Services. This Windows group name appears in the format SQLServerReportServerUser$<computer_name>$<instance_name>.
Verify Your Deployment
Test the report server and web portal virtual directories by opening a browser and typing in the URL address. For more information, see Verify a Reporting Services Installation.
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 report object model when processing and rendering reports, but it does not replace SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, or SQL Server 2014 constructs with new report definition language elements. To learn more about how existing reports run on a new version of your report server, see Upgrade Reports.
Remove Unused Programs and Files
Once you have successfully migrated your report server to a new instance, you might want to perform the following steps to remove programs and files that are no longer necessary.
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
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
Remove IIS if you no longer need it on this computer.
More questions? Try asking the Reporting Services forum