Tuesday, July 22, 2014

Migrating Reporting Services Data

Reporting Services defines a migration operation as moving application data files from an existing installation to a new installation. Often, migration includes upgrading to the newest version of the software or to new hardware.

If you are migrating from SQL Server 2005 or 2008 Reporting Services to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) instance, you can usually run Setup 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. Your current installation remains online while you copy application data to a new instance and test the installation as an independent operation.
Migrating to Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) offers significant benefits in deployment and processing, but you should be aware that not all functionality from previous versions is available in Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3). Specifically, if you are using a third-party single sign-on technology solution or custom features that integrate with Reporting Services through Internet Information Services (IIS), your current solution will not work on a new report server installation. You will have to redesign your solution for a Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server.
Before you migrate, evaluate the new software and test reports and other items to ensure your business requirements are met. The following links provide information to help you determine a migration strategy:
For step-by-step instructions on how to move application data to a new Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) report server instance, see How to: Migrate a Reporting Services Installation.

You can move a SQL Server Code-Named “Denali” Reporting Services (SSRS), Community Technology Preview 3 (CTP 3) Reporting Services installation to a new or different computer by installing a new report server instance and moving the report server database to the new instance. For more information about how to move the database, see Moving the Report Server Databases to Another Computer.
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.

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.

No comments:

Post a Comment