Upgrade and Migrate Reporting Services

There are two general approaches to updating your environment:

• Upgrade: You upgrade the various technologies on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade.

• Migrate: You install a new environment, copy your metadata and data to the new environment, and configure the new environment to use existing content. You may also decide to copy your content to a new location and configure the new environment to use the copied content This may include copying the Reporting Services databases as well SharePoint content and configuration information for SharePoint mode.

If you are upgrading from a supported version of SQL Server Reporting Services to a new SQL Server 2012 instance, you can usually run the SQL Server Installation Wizard to upgrade the report server program files, database, and all application data. However, migrating a report server installation manually is required if you encounter any of the following conditions:

• Upgrade Advisor detected one or more upgrade blockers.

• You want to minimize the time the report server is taken offline during the upgrade process. Your current installation remains online while you copy application data to a new instance and test the installation as an independent operation.

• You want to migrate a SharePoint 2010 deployment of Reporting Services to SharePoint 2013. SharePoint 2013 does not support in-place upgrade from SharePoint 2010. For more information, see Migrate a Reporting Services Installation (SharePoint Mode).

In this topic:

• Editions and Versions You Can Upgrade

• SharePoint Mode Upgrade and Migration Scenarios

• Native Mode Upgrade and Migration Scenarios

• Native Mode to SharePoint Mode Migration Scenario

• Migration Considerations

• In-place Upgrade

• Pre-Upgrade Checklist (In-place Upgrade)

• To Upgrade (In-place Upgrade)

• Native Mode Upgrade Workflow (In-place Upgrade)

• Upgrading Reports

• Known Upgrade Issues

• Additional Resources

Editions and Versions You can upgrade

SQL Server 2012 Setup provides upgrade support for the following earlier editions of Reporting Services:

• SQL Server 2012.

• SQL Server 2005 Reporting Services SP4.

• SQL Server 2008 Reporting Services SP2.

• SQL Server 2008 R2 Reporting Services SP1.

For information on supported scenarios for Upgrading or downgrading from one SQL Server 2012 edition to another, see Features Supported by the Editions of SQL Server 2012 (https://go.microsoft.com/fwlink/?linkid=232473)..

Top

SharePoint Mode Upgrade and Migration Scenarios

The following sections describe the issues and basic steps needed to upgrade or migrate from specified versions of Reporting Services SharePoint mode to SQL Server 2012  Reporting Services SharePoint mode.

For detailed upgrade steps see the following sections in this topic:

• In Place upgrade

• Pre-Upgrade Checklist

• To Upgrade Reporting Services

For detailed steps on Migrating a SharePoint mode installation, see Migrate a Reporting Services Installation (SharePoint Mode).

• SQL Server 2012 to SQL Server 2012 Service Pack 1 (SP1).

• SQL Server 2012 CTP3 to SQL Server 2012.

• SQL Server 2008 R2 to SQL Server 2012.

• SQL Server 2008 SP2 to SQL Server 2012.

• SQL Server 2005 SP2 to SQL Server 2012.

SQL Server 2012 to SQL Server 2012 SP1

Starting environment: SQL Server 2012, SharePoint 2010.

Ending environment: SQL Server 2012 SP1, SharePoint 2010.

1. SharePoint 2010: In-place upgrade is supported and there is no down time for your SharePoint environment.

2. SharePoint 2013: SharePoint 2013 does not support in-place upgrade. However the procedure of database-attach upgrade is supported. The behavior is different from upgrading to SharePoint 2010, where a customer could choose between the two basic upgrade approaches, in-place upgrade and database-attach upgrade.

   If you have a Reporting Services installation integrated with SharePoint 2010, you cannot upgrade in-place the SharePoint server. However you can migrate content databases and service application databases from the SharePoint 2010 farm to a SharePoint 2013 farm.

SQL Server 2012 CTP3 to SQL Server 2012

Starting environment: SQL Server 2012 CTP3, SharePoint 2010.

Ending environment: SQL Server 2012, SharePoint 2010.

• In-place upgrade is supported and there is no down time for your SharePoint environment.

• SQL Server 2012 only supports SharePoint 2010 therefore no SharePoint upgrade is required.

• If you want to use SQL Server 2012 database engine for SharePoint content databases, you need to upgrade SharePoint 2010 to SharePoint 2010 Service pack 1 (SP1).

• Run the SQL Server 2012 installation wizard, you will see the Reporting Services upgrade dialog to confirm you want to upgrade all of the SQL Server Shared Features. Reporting Services SharePoint mode is considered a SQL Server Shared feature and Shared features are either all upgraded at once or none are upgraded.

Top

SQL Server 2008 R2 to SQL Server 2012

Starting environment: SQL Server 2008 R2, SharePoint 2010.

Ending environment: SQL Server 2012, SharePoint 2010.

• In-place upgrade is supported and there is no down time for your SharePoint environment.

• Install the SQL Server 2012 version of the Reporting Services add-in for SharePoint on each web front-end in the farm. You can install the add-in by using the SQL Server 2012 installation wizard or by downloading the add-in.

• Run SQL Server 2012 installation to upgrade SharePoint mode for each ‘report server’.The SQL Server installation wizard will install the Reporting Services Service and create a new Service application.

Top

SQL Server 2008 SP2 to SQL Server 2012

Starting environment: SQL Server 2008 SP2, SharePoint 2007.

Ending environment: SQL Server 2012, SharePoint 2010.

• This in-place upgrade scenario does require down time of the SharePoint environment because both the SharePoint and SQL Server technologies need to be upgraded. You may want to consider completing a migration rather than an in-place upgrade.

• Upgrade SQL server 2008 to Service Pack 2 (SP2) first, if that has not already been completed.

• Upgrade SharePoint to 2010. When you run the SharePoint 2010 prerequisite installer it will upgrade the Reporting Services add-in for SharePoint 2010 products.

• Install the SQL Server 2012 version of the Reporting Services add-in for SharePoint on all SharePoint web front-ends. The SharePoint pre-requisite installer installed the SQL Server 2008 R2 version of the add-in but you need the SQL Server 2012 version to work with a SQL Server 2012 report server.

• Warning

  Following the SharePoint upgrade your reporting services environment will be in a non-working state until SQL Server is upgraded.

• Upgrade Reporting Services to SQL Server 2012. When you run the SQL server installation wizard, you will a dialog regarding "SQL Server Reporting Services SharePoint Mode Authentication" dialog. The Reporting Services service will be installed and the credentials from the authentication page will be used for creating a new SharePoint application pool.

Top

SQL Server 2005 SP2 to SQL Server 2012

Starting environment: SQL Server 2005 SP2, SharePoint 2007.

Ending environment: SQL Server 2012, SharePoint 2010.

• This in-place upgrade scenario does require down time of the SharePoint environment because both the SharePoint and SQL Server technologies need to be upgraded. You may want to consider completing a migration rather than an in-place upgrade.

• Upgrade SQL server 2005 to Service Pack 2 (SP2) first, if that has not already been completed.

• Upgrade SharePoint to SharePoint 2010. When you run the SharePoint 2010 prerequisite installer it will upgrade the Reporting Services add-in for SharePoint 2010 products.

• Warning

  Following the SharePoint upgrade your reporting services environment will be in a non-working state until SQL Server is upgraded.

• Install the SQL Server 2012 version of the Reporting Services add-in for SharePoint on all SharePoint web front-ends. The SharePoint pre-requisite installer installed the SQL Server 2008 R2 version of the add-in but you need the SQL Server 2012 version to work with a SQL Server 2012 report server.

• Upgrade Reporting Services to SQL Server 2012. When you run the SQL server installation wizard, you will a dialog regarding "SQL Server Reporting Services SharePoint Mode Authentication" dialog. The Reporting Services service will be installed and the credentials from the authentication page will be used for creating a new SharePoint application pool.

Top

Native Mode Upgrade and Migration Scenarios

Upgrade: In-place upgrade for native mode is the same process for each of the supported versions that are listed earlier in this topic. Run the SQL Server installation wizard or a command line installation. Following installation the report server database will automatically upgrade to the new report server database schema. For more information, see the In-place Upgrade section in this topic.

Migration: Migrating a previous version of a native mode installation to SQL Server 2012 is the same steps for all of the supported s versions that are listed earlier in this topic. For more information, see Migrate a Reporting Services Installation (Native Mode)

Top

Native to SharePoint Migration Scenario

You cannot upgrade or convert from one Reporting Services server mode to another. For example you cannot upgrade or convert a Native mode report server to SharePoint mode. You cannot copy the report server databases between modes because they use different database schemas.

If the migration tool does not meet your needs, you can manually migrate the report server data. The following is summary of the steps you can complete to manually migrate report items from a Reporting Services Native mode installation to a SharePoint mode installation.

1. Backup and restore encryption keys. This is the key that is used to encrypt data. The encryption key is also used to encrypt passwords such as the passwords stored for data source connections. However, passwords cannot be migrated and you will need to enter them again in the destination environment.

2. 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).

Top

Considerations for a Migration

When moving application data, you should be aware of the following concerns and restrictions:

• Protection of encryption key includes a hash that incorporates machine identity.

• Report server database names are fixed and cannot be renamed on new computer.

Encryption Key Considerations

Always back up the encryption keys before moving a report server database to a new computer.

Moving a report server installation to another computer will invalidate the hash that protects the encryption keys used to help secure sensitive data stored in the report server database. Each report server instance that uses the database has its copy of the encryption key, which is encrypted with the identity of the service account as it is defined on the current computer. If you change computers, the service will no longer have access to its key, even if you use the same account name on the new computer.

To re-establish reversible encryption on the new report server computer, you must restore the key that you previously backed up. The complete key set that is stored in the report server database consists of a symmetric key value, plus service identity information used to restrict access to the key so that it can be used only by the report server instance that stored it. During key restoration, the report server replaces existing copies of the key with new versions. The new version includes machine and service identity values as defined on the current computer. For more information, see the following topics:

• SharePoint mode: See the “Key Management” section of Manage a Reporting Services Service Application

• Native Mode: See Back Up and Restore Reporting Services Encryption Keys (SSRS Native Mode)

Top

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 will cause errors to occur 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.

Top

In-place upgrade

Upgrade is performed by SQL Server Setup. SQL Server Setup can be used to upgrade any or all SQL Server components, including Reporting Services. To upgrade an earlier version of SQL Server, run the SQL Server 2012 Setup program on a computer that has an earlier version of SQL Server installed. Setup detects the existing instances and prompts you to upgrade.

When you run Setup, you can select the option to upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, or you can install a new instance of SQL Server 2012 Reporting Services (SSRS) that runs side-by-side existing installations. 

Top

Pre-Upgrade Checklist

Before upgrading to SQL Server 2012 Reporting Services (SSRS), review the following requirements:

• Review requirements to determine whether your hardware and software can support SQL Server 2012 Reporting Services (SSRS). For more information, see Hardware and Software Requirements for Installing SQL Server 2012.

• Use System Configuration Checker (SCC) to scan the report server computer for any conditions that might prevent a successful installation of SQL Server 2012. For more information, see Check Parameters for the System Configuration Checker.

• Review security best practices and guidance for SQL Server. For more information, see Security Considerations for a SQL Server Installation.

• Run the Upgrade Advisor tool on the report server computer to determine any issues that might prevent you from successfully upgrading. For more information, see Use Upgrade Advisor to Prepare for Upgrades.

• Back up your symmetric key. For more information, see Back Up and Restore Reporting Services Encryption Keys (SSRS Native Mode).

• Back up your report server databases. For more information, see Moving the Report Server Databases to Another Computer.

• Back up the following report server configuration files:

  • Rsreportserver.config

  • Rswebapplication.config

  • Rssvrpolicy.config

  • Rsmgrpolicy.config (Native mode only)

  • Reportingservicesservice.exe.config (Native mode only).

  • Web.config forthe report server ASP.NET applications).

  • Web.config for the Report Manager ASP.NET application (Native mode only).

  • Machine.config (for ASP.NET if you modified it for report server operations).

• Back up any customizations to existing Reporting Services virtual directories in IIS.

• Remove invalid SSL certificates.  This includes certificates that are expired and you do not plan to update prior to upgrading Reporting Services.  Invalid certificates will cause upgrade to fail and an error message similar to the following will be written to the Reporting Services Log file: Microsoft.ReportingServices.WmiProvider.WMIProviderException: A Secure Sockets Layer (SSL) certificate is not configured on the Web site..

Before you upgrade a production environment, always run a test upgrade in a pre-production environment that has the same configuration as your production environment.

Top

To Upgrade Reporting Services

Setup provides upgrade options that you can specify as a command-line argument or in the Setup wizard. Choose from the following topics for instructions on how to upgrade your installation.

• Upgrade to SQL Server 2012 Using the Installation Wizard (Setup)
  Explains how to upgrade to SQL Server 2012 using the Installation Wizard.

• Install SQL Server 2012 from the Command Prompt
  Explains how to upgrade to SQL Server 2012 as a command-line operation.  

Native Mode Upgrade Workflow

The upgrade process begins when you select an existing report server instance to upgrade.

1. If the report server database is on a remote computer and you do not have permission to update that database, Setup prompts you to provide credentials to update to a remote report server database. Be sure to provide credentials that have sysadmin or database update permissions.

2. Setup checks for conditions or settings that prevent upgrade and reads configuration settings. Examples include custom extensions deployed on the report server. If upgrade is blocked, you must either modify your installation so that upgrade is no longer blocked, or migrate to a new SQL Server 2012 instance. For more information, see the Upgrade Advisor documentation.

3. If upgrade can proceed, Setup prompts you to continue with the upgrade process.

4. Setup creates new folders for SQL Server 2012 program files. The program folders for a Reporting Services installation include MSRS11.<instance name>.

5. Setup adds the SQL Server 2012 report server program files, configuration tools, and command line utilities that are part of the report server feature.

   1. Program files from the previous version are removed.

   2. Report server configuration tools and utilities that are upgraded to the new version include the Reporting Services Configuration tool, command line utilities, and Report Builder. New tools installed with SQL Server 2012 include the new Report Designer Preview.

   3. Other client tools such as SQL Server Data Tools (SSDT), SQL Server Management Studio, and Books Online are not upgraded. To obtain new versions of the tools, you can add them when you run Setup. Earlier versions will co-exist alongside SQL Server 2012 versions. If you installed samples, the earlier version will remain. Setup does not support upgrade for the SQL Server samples.

6. Setup reuses the service entry in Service Control Manager for the Report Server service. This service entry includes the Report Server Windows service account.

7. Setup reserves new URLs based on existing virtual directory settings in IIS. Setup might not remove virtual directories in IIS, so be sure to remove those manually after upgrade is finished.

8. Setup upgrades the report server databases to the new schema and modifies the RSExecRole by adding Database Owner permissions to the role. This step only occurs when you are upgrading from SQL Server 2005 Reporting Services prior to SP1.

9. Setup merges settings in the configuration files. Using the configuration files from the current installation as the basis, new entries are added. Obsolete entries are not removed, but they will no longer be read by the report server after upgrade is finished. Upgrade will not delete old log files, the obsolete RSWebApplication.config file, or virtual directory settings in IIS. Upgrade will not remove SQL Server 2005 Report Designer, Management Studio, or other client tools. If you no longer require them, be sure to remove these files and tools after upgrade is finished.

Top

Upgrading Reports

In general, a report server will automatically upgrade the compiled version of individual reports the first time they are run following a server upgrade. The source .rdl is not upgraded. For information on report upgrade behavior and scenarios, see Upgrade Reports.

Top

Known Upgrade Issues and Best Practices

Before upgrading to SQL Server 2012 Reporting Services (SSRS), review the Upgrade Advisor documentation and the Online Release Notes for the latest information about upgrade issues. To obtain the Upgrade Advisor documentation, install Upgrade Advisor. For more information, see Use Upgrade Advisor to Prepare for Upgrades.

Known upgrade issues currently include the following:

• No support for earlier versions of the Reporting Services WMI provider. The Reporting Services WMI provider is not backwards compatible with previous versions. After upgrade, you cannot use the SQL Server 2012 Reporting Services (SSRS) WMI provider with earlier versions of Reporting Services. For more information on backwards compatibility, see Reporting Services Backward Compatibility

Top

Additional Resources

• Overview of the upgrade process to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256688).

• Clean up preparations before an upgrade to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256689).

• Upgrade databases from SharePoint 2010 to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256690).

Top

See Also

Concepts

Upgrade to SQL Server 2012