I was recently involved in a project to upgrade a SQL Server 2005 environment to 2012. The upgrade wasn’t in place though, because the client decided to buy new hardware as well.
You will be amazed at how easy it is to migrate/upgrade an SSRS instance to 2012, and we really only encountered one minor snag (and not with the upgrade itself) which I will detail in a different blog post. Here’s what you have to do in order to move your SSRS 2005 environment to a 2012 instance (new/different hardware):
Step 1: Install & Configure SSRS 2012
The installation and configuration of the new 2012 instance goes without saying, and should be the first step in the process.
Step 2: Backup Encryption Keys
Encryption keys are used to encrypt (no surprise) the contents of your Report Server databases. This is required for various reasons, but mostly due to the fact that credentials of data sources can be stored within Reporting Services. If it wasn’t encrypted, anybody with access to the ReportServer database would be able to see all your data source account and password information.
If you are going to restore/move the SSRS databases (ReportServer and ReportServerTempDB) on/to the 2012 instance, you will need the encryption keys from the old environment. A very important fact to remember is that you will lose all your connection and subscription information should you delete the encryption keys (more info here). The Reporting Services Configuration Manager can be used to back up encryption keys.
Step 3: Restore ReportServer and ReportServerTempDB databases
If your SSRS databases are small enough, you could actually opt to detach (from 2005 instance), copy (to new 2012 server) and attach (to 2012 instance) them rather than backup and restore. An important point to here is to stop the SSRS service on the new instance before attaching or restoring the content databases.
After the content databases are restored/attached, you simply need to change the compatibility level (of both) to 110 for SQL Server 2012, and start the service. The upgrade happens at this point…and yes, it is as simple as that!!!
Step 4: Restore Encryption Keys
You should receive an error if you try to access the Report Manager on the new 2012 instance at this point. The reason for that is because the encryption keys (automatically generated when SSRS 2012 was installed) is no longer able to decrypt the information in the newly restored/attached content databases.
To resolve the issue you simply have to restore the encryption keys that you backed up from the old SSRS 2005 instance, and restart the services. Use the Reporting Services Configuration Manager for this task.
Step 5: Update Data Sources
Don’t forget to verify all your data sources. You may have to update some of these to point to the right database server…
Why Should I Restore The SSRS Content Databases?
We all like to start with a new (and clean) environment. You will be tempted to re-deploy all your reports and recreate security, but there are a few very good reasons why you may not want to go down that road:
- Subscriptions & Snapshots – If users create their own report subscriptions and/or snapshots, it will be a very tedious task to recreate it in the new environment. Asking end users to recreate their own subscriptions etc. may also not be acceptable.
- Folder-level Permissions – If you have many sub-folders with different permissions, it will be very cumbersome to reproduce in the new environment.
- Deploying Reports Manually – Deploying reports through BIDS/SSDT is quick and easy, but if you for some reason (it happened in our case) do not have the latest version of your reports in a BIDS/SSDT project for deployment, it may take a lot of time to extract the existing reports from the 2005 instance before re-deployment.