Sql-server – Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:

A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions. A Reporting Services installation uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are reportserver and reportservertempdb, respectively.

You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.

Try RSMigrationTool.exe and RSMigrationUI.exe from Microsoft

Here's a brief excerpt from the white paper link I included below, together with the migration tool download URL and Backup/Restore Encryption Key URL:

1. Download the tool at http://www.microsoft.com/en-us/download/details.aspx?id=29560.
2. Extract the files from the MigrationToo.zip file. You need the following two executable programs in the extracted files. RSMigrationTool.exe: Command line utility that helps in exporting the reporting services artifacts. RSMigrationUI.exe: GUI interface that allows the users to export the reporting services artifacts.

3. Export the SSRS items by using the RSMigrationTool.exe.

   a. Open a command prompt as an administrator.

   b. Navigate to the folder that contains the extracted RSMigrationTool.exe.

   c. Run the tool with the following parameters.

   RSMigrationTool.exe [-n native_report_server_url] –I native_report_server_instance [-h] –f migration_metadata_files_folder [-p password]

White Paper

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/MigratingContentBetweenReportServers.docx

Migration Tool

https://www.microsoft.com/en-us/download/details.aspx?id=29560

Backup and Restore Encryption Keys

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

Alternately try restoring the SSRS databases

And, if you want to go that route, you can try to restore the SSRS ReportServer and ReportServerTempDB databases to the target server. This example shows moving from SQL 2005 to 2008 SSRS:

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

As always, try all these things on a test/dev SQL Server first.