I recently had a requirement to migrate a SQL Server instance from 2008R2 to 2014. The core SQL Server functionality, the engine is always the easy part for me. I don’t do much with SSRS, so moving the reporting services component was the big unknown.
So let me share with you my biggest take away from this project: EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases. So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.
Based on my one experience with this, there is need to move individual reports. If you follow the process carefully, all of the existing reports will be re-created on the new machine. It’s not quite magic, but it sure feels like it when everything shows up on the new system.
I didn’t figure this process out alone… Let me give credit to the two blog posts that I used to learn this process in the first place:
The core process of SSRS migration is in both of these posts. Since they were migrating from 2005 and 2008R2 to 2012 some of the information is slightly different between the versions.
In my case, I migrated from 2008R2 to 2014.
For the sake of this post I will use the convention of SOURCE to reference the system we are moving SSRS FROM and TARGET to reference the instance we are moving SSRS to.
In general, the high level process is:
- On the SOURCE system:
- Backup the SSRS database encryption key
- Backup both of the SSRS databases
- (Optional) Backup a small number of SSRS config files (just in case)
- On the TARGET system:
- Restore the SSRS databases from the SOURCE system.
- Restore the SOURCE system SSRS Encryption Key
- Start the SSRS service and…
- Observer all of your reports and security settings, exactly as they were on the SOURCE machine.
I did skip over a few details in the above, so don’t use that literally to migrate your system. The details of the process will follow… Be mindful that I will not show the steps on how to backup and restore a database. Providing that level of detail takes the focus off of the “more interesting” SSRS components.
I’m also assuming that SSRS is installed and configured to run on the TARGET machine. There doesn’t need to be any reports running or have any users configured. It just needs to have an instance up and running.
In fact it’s worth noting that this process does not MERGE the SOURCE SSRS reports with the TARGET SSRS reports. The end product of this process is a duplication of the SOURCE SSRS.
Here are my detailed notes/steps that I used to migrate SSRS from a SQL Server 2008R2 instance (SOURCE) to a SQL Server 2014 instance (TARGET).
One the SOURCE instance:
- Using SSRS Configuration Manager, backup the database Encryption Key. Make sure the backup is successful. I intentionally chose a simple password (easy to remember) but it didn’t meet the password complexity policy, and failed the backup.
- Take a FULL (COPY_ONLY works too) backup of the SSRS databases, ReportServer and ReportServerTemp (These are the default database names. Your actual databases may be named differently).
- There are a few XML Config files you may want to backup. If these files are not in their default state you may need to reference these files in order to update the same files on the TARGET instance. The files are in C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.
On the TARGET instance:
- Using SSRS Configuration Manager, backup the TARGET SSRS encryption key. Strictly speaking, this step is not necessary. If something really bad goes wrong, and you can’t get the migration to work, you will need this key in order to restore the TARGET SSRS back to its original state.
- From SQL Server configuration manager (or Services.msc) shut down the SSRS Service. In the next step we will rename the SSRS databases. If we don’t shut down the service the rename (next step) will probably fail.
- Rename the TARGET SSRS databases to something other than what they are now. In theory we could over write them when we restore the SOURCE databases to this host, but if something goes wrong, we have the old databases ready to rename back.
- (Optional) Backup the SSRS configuration files. Again, just in case something goes wrong.
- Restore the SOURCE SSRS ReportServer and ReportServerTemp to the TARGET instance.
- (Optional) Set the TARGET SSRS database compatibility level to SQL Server 2014. By default, when restoring a database from an older version to a newer version instance, the restored database will still be running at the older version compatibility level. What’s the point of upgrading if you’re going to stay at the older level?
- Start up the TARGET SSRS service.
- Using the backed up SOURCE encryption key restore the key in the TARGET SSRS Configuration Manager. This step is critical, since it allows the SSRS service to read the database that was migrated over from the SOURCE instance.
alter database ReportServer set compatibility_level = 120;
alter database ReportServerTempDB set compatibility_level = 120;
At this point all of the reports and security settings should now be on the TARGET instance. It’s magical how the details of those reports are stored as metadata in the SSRS databases. By simply restoring the databases and providing the encryption key everything we had on the SOURCE SSRS instance is now on the TARGET SSRS instance.
Special thanks to my colleague Kaushal Kabariya (@kaushalkabariya) who started this whole process on his own and had to find out the hard way that report migration is in fact a DBA activity.
Stay curious; keep learning.