Migration of SSRS to Another Instance

Introduction

You can move the report server databases that are used in an installation SQL Server Database Engine to an instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving

Applies to:

Reporting Services Native mode.

• Moving a database does not affect scheduled operations that are currently defined for report server items.
• Schedules will be recreated the first time that you restart the Report Server service.
• SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.
• Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.
• Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

Types of Migration

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Additional Tips

• The recommended way to migrate the reportserver databases is using the backup and restore process. It maintains all the settings and login information when migrated to the destination instance and have most reduced report server services downtime.
• Finally, remember Database Compatibility requirements apply here too when it comes to the migration. You cannot migrate a database of a higher version to a lower version of SQL Server.

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql#compatibility-support  – Compatibility Support

Backing Up and Restoring the Report Server Databases

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You must use Transact-SQL statements to do the backup and restore. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic

Using BACKUP and COPY_ONLY to Backup the Report Server Databases

When backing up the databases, set the COPY_ONLY argument. Be sure to back up both of the databases and log files.

-- To permit log backups, before the full database backup, alter the database  
-- to use the full recovery model if not already in FULL Recovery Model.
–- Note: The report server database names could be different
USE master; 
GO 
ALTER DATABASE ReportServer SET RECOVERY FULL 

-- If the ReportServerData device does not exist yet, create it. Specify path according to the environment.  
USE master 
GO 
EXEC sp_addumpdevice 'disk', 'ReportServerData',  
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\ReportServerData.bak' -- Create a logical backup device, ReportServerLog.

USE master 
GO 
EXEC sp_addumpdevice 'disk', 'ReportServerLog',  
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\ReportServerLog.bak' 

-- Back up the full ReportServer database. 
BACKUP DATABASE ReportServer TO ReportServerData WITH COPY_ONLY 

-- Back up the ReportServer log. 
BACKUP LOG ReportServer TO ReportServerLog WITH COPY_ONLY 

-- To permit log backups, before the full database backup, alter the database  
-- to use the full recovery model if not already in FULL Recovery Model.
USE master; 
GO 
ALTER DATABASE ReportServerTempdb SET RECOVERY FULL 

-- If the ReportServerTempDBData device does not exist yet, create it.  
USE master 
GO 
EXEC sp_addumpdevice 'disk', 'ReportServerTempDBData',  
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\ReportServerTempDBData.bak' 

-- Create a logical backup device, ReportServerTempDBLog. 
USE master 
GO 
EXEC sp_addumpdevice 'disk', 'ReportServerTempDBLog',  
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\ReportServerTempDBLog.bak' 

-- Back up the full ReportServerTempDB database. 
BACKUP DATABASE ReportServerTempDB 
TO ReportServerTempDBData WITH COPY_ONLY 

-- Back up the ReportServerTempDB log. 
BACKUP LOG ReportServerTempDB TO ReportServerTempDBLog

USING RESTORE AND MOVE TO RELOCATE THE REPORT SERVER DATABASES

When restoring the databases, be sure to include the MOVE argument so that you can specify a path. Use the NORECOVERY argument to perform the initial restore; this keeps the database in a RESTORING state, giving you time to review log backups to determine which one to restore. The final step repeats the RESTORE operation with the RECOVERY argument.
The MOVE argument uses the logical name of the data file. To find the logical name, execute the following statement:

RESTORE FILELISTONLY FROM DISK='C:\ReportServerData.bak'; -- Change the path as needed

The following examples include the FILE argument so that you can specify the file position of the log file to restore. To find the file position, execute the following statement:

RESTORE HEADERONLY FROM DISK='C:\ReportServerData.bak'; -- Change the path as needed

When restoring the database and log files, you should run each RESTORE operation separately.

-- Restore the report server database and move to new instance folder  
-- Note: If destination instance already have the same database name, it will fail. Either restore as a different name, rename the existing one if not in use or drop that database.
RESTORE DATABASE ReportServer 
   FROM DISK='C:\ReportServerData.bak' WITH NORECOVERY,  
      MOVE 'ReportServer' TO  
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer.mdf',  
      MOVE 'ReportServer_log' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_Log.ldf'; 
GO 

-- Restore the report server log file to new instance folder  
RESTORE LOG ReportServer 
   FROM DISK='C:\ReportServerData.bak' WITH NORECOVERY, FILE=2 
      MOVE 'ReportServer' TO  
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer.mdf',  
      MOVE 'ReportServer_log' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_Log.ldf'; 
GO 

-- Restore and move the report server temporary database 
RESTORE DATABASE ReportServerTempdb 
   FROM DISK='C:\ReportServerTempDBData.bak' WITH NORECOVERY,  
      MOVE 'ReportServerTempDB' TO  
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServerTempDB.mdf',  
      MOVE 'ReportServerTempDB_log' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\REportServerTempDB_Log.ldf'; 
GO 

-- Restore the temporary database log file to new instance folder  
RESTORE LOG ReportServerTempdb 
   FROM DISK='C:\ReportServerTempDBData.bak' WITH NORECOVERY, FILE=2 
      MOVE 'ReportServerTempDB' TO  
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServerTempDB.mdf',  
      MOVE 'ReportServerTempDB_log' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\REportServerTempDB_Log.ldf'; 
GO 

-- Perform final restore 
RESTORE DATABASE ReportServer WITH RECOVERY 
GO 

-- Perform final restore 
RESTORE DATABASE ReportServerTempDB WITH RECOVERY 
GO

BACKING UP AND RESTORING THE REPORT SERVER ENCRYPTION KEY
The Reporting Server includes encrypted content from ownerid, scheduleid, subscriptionid, and more. In order for the migration to successfully work, there should be a creation of the backup of the encryption key on the source report server to be restored on the target server. All these tasks can be done using the Reporting Services Configuration Manager.
BACKING UP THE ENCRYPTION KEY
• On the left Pane of your Reporting Services Configuration Manager, select Encryption keys as shown below.

• Select the “Backup” to take a backup of the encryption key. Under the Backup Encryption Key dialog box, specify the location to save the encrypted key. Also provide a strong password which will be required to restore the key on the target server. NOTE: a weak password will cause the backup to fail.

RESTORING THE ENCRYPTION KEY
• After restoring the report server database to the target server, you are required to restore the encryption key. This process should be done after you have changed the report server database connection to point to the new target server/database.
• Copy the encryption key from the source server to the target server.
• From the Reporting Services Configuration manager, select encryption keys and select restore to bring up the Restore Encryption Key dialog box. Click on File Location to point to the encryption key on the server. Type password and click ok to begin restore.

• Once restore, you should have a result as below;

HOW TO CONFIGURE THE REPORT SERVER DATABASE CONNECTION
• Start the Reporting Services Configuration Manager and open a connection to the report server.

• On the Database page, click Change Database. Click Next.

• Click Choose an existing report server database. Click Next.

• Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

• In Database Name, select the report server database that you want to use. Click Next.

• In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

• Click Next and then Finish.

START AND STOP THE REPORT SERVER SERVICE
A report server is implemented as a Windows service that contains the Report Server Web service, Report Manager, and a background processing application. The service must be running if you want to use any report server functionality. Stopping the service stops all report server operations.
While the service is stopped, requests for scheduled report and subscription processing that would have occurred had the service been running are added to the queue. This is because jobs that are run by SQL Server Agent create the events. If you want to avoid a backlog of operations while the service is off, consider stopping SQL Server Agent as well.
You can use a variety of tools to start or stop the Report Server service, including the Reporting Services Configuration tool, SQL Server Configuration Manager, and the Services tool provided in Microsoft Windows.
If you are doing more than starting or stopping the service, such as changing the service account, you must use the Reporting Services Configuration tool. Using other tools to change the service account can break your Reporting Services installation. For more information, see Configure the Report Server Service Account (SSRS Configuration Manager).
You cannot pause and resume the service. There are no start parameters. Although there are no explicit dependencies, SQL Server Agent must be running if you support any subscriptions or scheduled report operations on the report server.
TO START OR STOP THE SERVICE USING THE REPORTING SERVICES CONFIGURATION TOOL
• Start Reporting Services Configuration tool and connect to the report server.
• On the Report Server Status page, click Stop or Start.

TO START OR STOP THE SERVICE USING SERVICES IN ADMINISTRATIVE TOOLS
• In Administrative Tools, open Services, right-click SQL Server Reporting Services (INSTANCE_NAME), and click Stop or Restart.
• If you are running multiple instance or if the report server is running as a named instance, verify that the instance name in parentheses corresponds to the report server instance you want to stop or restart.

TO START OR STOP THE SERVICE USING SQL SERVER CONFIGURATION MANAGER
• Start SQL Server Configuration Manager.
• Select SQL Server Services, right-click SQL Server Reporting Services, and click Stop or Restart.

POST MIGRATION
SSRS REQUIRED RSEXECROLE ROLE
Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role.
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. For instance that has reporting service feature, it is most likely that this role would exist.
In some circumstances where the target server does not have reporting services features installed, the RSExecRole would not exist in the master and msdb databases. In such scenarios, please use the following procedures below to create the role in the master and msdb databases.
Create RSExecRole in Master
Reporting Services uses extended stored procedures for SQL Server Agent service to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures to the RSExecRole role.
To create RSExecRole in the Master system database using Management Studio
1. Start SQL Server Management Studio and connect to the Database Engine instance that hosts the report server database.
2. Open Databases.
3. Open System Databases.
4. Open Master.
5. Open Security.
6. Open Roles.
7. Right-click Database Roles, and select New Database Role. The General page appears.
8. In Role name, type RSExecRole.
9. In Owner, type DBO.
10. Click Securables.
11. Click Search. The Add Objects dialog box appears. The Specific Objects option is selected by default.
12. Click OK. The Select Objects dialog box appears.
13. Click Object Types.
14. Click Extended Stored Procedures.
15. Click OK.
16. Click Browse.
17. Scroll down the list of extended stored procedures and select the following:
a. xp_sqlagent_enum_jobs
b. xp_sqlagent_is_starting
c. xp_sqlagent_notify
18. Click OK, and the click OK again.
19. In the Execute row, in the Grant column, click the check box, and then click OK.
20. Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all three stored procedures.

Create RSExecRole in MSDB
Reporting Services uses stored procedures for SQL Server Agent service and retrieves job information from system tables to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures and Select permissions on the tables to the RSExecRole.
To create RSExecRole in the MSDB system database
1. Repeat similar steps for granting permissions to stored procedures and tables in MSDB. To simplify the steps, you will provision the stored procedures and tables separately.
2. Open MSDB.
3. Open Security.
4. Open Roles.
5. Right-click Database Roles, and select New Database Role. The General page appears.
6. In Role name, type RSExecRole.
7. In Owner, type DBO.
8. Click Securables.
9. Click Add. The Add Objects dialog box appears. The Specify Objects option is selected by default.
10. Click OK.
11. Click Object Types.
12. Click Stored Procedures.
13. Click OK.
14. Click Browse.
15. Scroll down the list of items and select the following:
a. sp_add_category
b. sp_add_job
c. sp_add_jobschedule
d. sp_add_jobserver
e. sp_add_jobstep
f. sp_delete_job
g. sp_help_category
h. sp_help_job
i. sp_help_jobschedule
j. sp_verify_job_identifiers
16. Click OK, and the click OK again.
17. Select the first stored procedure: sp_add_category.
18. In the Execute row, in the Grant column, click the checkbox, and then click OK.
19. Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all ten stored procedures.
20. On the Securables tab, and click Add again. The Add Objects dialog box appears. The Specify Objects option is selected by default.
21. Click OK.
22. Click Object Types.
23. Click Tables.
24. Click OK.
25. Click Browse.
26. Scroll down the list of items and select the following:
a. syscategories
b. sysjobs
27. Click OK, and the click OK again.
28. Select the first table: syscategories.
29. In the Select row, in the Grant column, click the checkbox, and then click OK.
30. Repeat for the sysjobs table. RSExecRole must be granted Select permissions for both tables.

SSRS REQUIRED RSEXECROLE ROLE

NOTE: It’s possible you get an error stating ‘The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)’ or some related error. Also, it is possible that your subscriptions and schedules in the SQL Server Agent jobs would not appear.

FIX: Open Reporting Services configuration Manager and clicked Scale-out Deployment in the navigation panel. It is highly possible you will see there were two servers listed, the old one and the new one. Click on the old server and remove server. Restart your SSRS services and you should error will be resolved or jobs will appear.

• After migration, check the destination (new) ReportServer database instance jobs to make sure all the subscriptions and schedules jobs were re-created. The hexadecimal names should match that of the source (old) server. NOTE: If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.