We were recently faced with this scenario:
Migrate a SQL Server Reporting Service from SharePoint 2013 On-Premise to another On-Premise farm running in your datacenter or in either Azure or AWS.
It does not matter which one, it only matters that you have access to the Farm Administration a.k.a. central admin. These steps will not work for a SharePoint that lives in Office 365. If you need to migrate from on-prem to O365, just convert all your SSRS reports to Power BI apps, in that case. <- – (I am only the messenger)
If you’re not migrating to Office 365 SharePoint and are instead migrating to a new farm, where you are a farm administrator, then these are the steps to do that process:
One other noteworthy point to mention: In this scenario outlined below, we also migrated from SQL 2008 to SQL 2012, with these steps:
Adapted from: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/upgrade-and-migrate-reporting-services
Protection of encryption key includes a hash that incorporates machine identity.
Report server database names are fixed and cannot be renamed on new computer.
Pre-requisites:
SSRS is installed in SQL and properly configured with the SSRS add-in on the servers in the farm
Determine if there is an execution account, if yes what are the credentials
Get the login info for the SSRS databases
Backup
1. Login to the source SharePoint On-prem server running central admin and navigate to SSRS.
Manage Service Applications > SSRS Services
2. Backup the key to a password protected file.
Manage Service Applications > SSRS Services > Key Management > Backup Encryption Key
Create a password and export the key to a directory
3. Copy the key file to the target\destination server that runs central admin and store in the backup\restore directory
4. Backup the SSRS Service application using the SharePoint Backup and Restore
Backup and Restore > Perform a Backup > expand Shared Service Applications > Select SSRS Services > Click Next > Select a backup location (make sure WSS_Admin_WPG and WSS_WPG have modify, read&execute, read on the backup location)
5. Backup the SSRS Service application proxy using the SharePoint Backup and Restore
Backup and Restore > Perform a Backup > expand Shared Service Proxies > Select SSRS Services > Click Next > Select a backup location
6. Copy the backed up file to the target server in your datacenter, azure VM, or AWS VM’s backup\restore directory
Restore
1. Login to target\destination server that is running central administration and run install-sprsservice and install-sprsserviceproxy
2. Open Central admin and navigate to Backup and Restore
3. Click Restore from backup Enter the backup\restore directory and click Refresh
4. Select the service application backup from the top component and click Next
5. Select the reporting services application and click Next
6. When prompted for Login Names and Passwords, type in the password for the login name that is displayed. The login name box should already be pre-populated with the login that was previously in use. This is the login you obtained in the pre-requisites.
7. Click Start restore
8. Repeat the process above but instead of restoring the service application, expand the Shared Services node and then expand the Shared Service Applications node.
9. Manage Service Applications > SSRS Services > Key Management > Restore Encryption Key, enter or browse to the path from step 3 of the backup steps, use the password from step 2 of the backup steps.
10. If there is an unattended execution account then enter that information.
This process backs up the databases so they will not need to be moved. No need to copy db’s from one version of SQL to the next, this process will bring them over and all the sites that are already in the farm that use these reports will automagically work again!
When we initially wrote these steps, our ONLY Concern was with step 6 of the restore. We were concerned that in the target SQL server, we had used the different path, the original farm had K:\data and T:\logs, the new farm just has one huge directory – – Ended up not mattering at all.
You must be logged in to post a comment.