Problem
I recently had a task to move SQL Reporting Services to a new server. While researching I found two methods to accomplish this task. One used a scripting tool which I wrote an article about and can be found here. The other method is the one recommended by Microsoft. This method physically moves the ReportServer database to the new server. So how do we move Reporting Services by moving the database?
Solution
If you do a search for how to move a SQL Reporting service to a new server you will find a Microsoft document detailing the steps required. Click this link to view the document. My initial reaction after reading through this document was there were a lot of steps and moving parts and that is why I initially used the scripting tool. But to be fair, I went through the process detailed in the Microsoft document and wanted to present it here.
My environment is simple, a single SQL 2005 server moving to a single SQL 2008 R2 server with no Scale-Out deployment, no custom assemblies and no custom configuration settings. So here we go.
Before starting the migration be sure to be aware of the changes listed in the document that may affect how you migrate your installation. In my case I did not have to worry about any of these changes so proceeded directly to the Backup Files and Data section of the document.
Backup Files and Data
Backup the encryption key for the report server database. The report server encryption key is backed up using the Reporting Services Configuration Manager. For SQL 2005 navigate to Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/ and click Reporting Services Configuration. On the Instance Selection dialog enter Machine Name and Instance Name and click Connect.
In the navigation panel on the left select Encryption Keys, then click Backup. Enter a Password in the Encryption Key Information dialog and click the button next to Key File to specify a location and name for the key backup file.
Click Exit to exit the Reporting Services Configuration Manager. Microsoft recommends backing up the report server configuration files. As I went through the process I did not need these files later in the process, but backed them up anyway. Here is a list of the files and their locations in a SQL 2008 installation on the E: drive.
- Rsreportserver.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Rssvrpolicy.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Report Server web.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Rswebapplication.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- RsmgrPolicy.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Report Manger web.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- ReportingServicesservice.exe.config – E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
- Machine.config for ASP.NET if you modified it
Backup the report server database using any supported method for backing up SQL Server databases. I choose to backup the databases using SQL Management Studio. Microsoft states that both the ReportServer and ReportServerTempDB databases should be backed up and restored to the new server. In SQL Management Studio expand Databases and right click the ReportServer database, select Tasks and select Back Up….
Make sure the Backup Type is set to Full and specify a location and filename for the backup.
Do the same tasks for the ReportServerTempDB database.
Note: If the database is using the Full recovery model, use the following script to back it up to avoid breaking the log chain, ‘With Copy_only’ will make a copy only backup and will not break the log chain:
Backup Database ReportServer To Disk='E:\Temp\ReportServer_04192012.bak' With Copy_only
Move Report Server Database and Configure Report Server
If you are moving the databases to a server that has never hosted SQL Reporting services you will need to create the RSExecRole, click this link for instructions.
When ready, restore the ReportServer and ReportServerTempDB databases to the new server. In my case I am replacing the existing Report server databases. In SQL Management Studio expand databases and right click the ReportServer database, from the pop up menu select Tasks/Restore/Database.
Make the entries in the Restore Database dialog appropriate for the backup you took. In my case it is a file stored on the local disk. Note: To gain exclusive access to the database for the restore I had to stop the SQL Server Reporting Services (MSSQLSERVER) service.
In the Options page of the Restore Database dialog check ‘Overwrite the existing database (WITH REPLACE)’, leave the database ready for use and use the buttons under ‘Restore As’ to locate the physical database files (existing .mdf and .ldf files).
Once the ReportServer database has been restored repeat the process for the ReportServerTempDB database. If your reporting installation includes custom items you must re-deploy those items, refer to the ‘Move Custom Assemblies or Extensions’ section of the migration document. My installation does not include custom items so I will not cover this section. Now that the databases and custom items (if any) have been moved you must use the Report Services Configuration tool to configure Reporting Services. Navigate to Start/All Programs/Microsoft SQL Server 2008 R2/Configuration Tools and click Reporting Services Configuration Manager. Enter the Server Name and Report Server Instance in the Reporting Services Configuration Connection dialog and click Connect.
If you stopped the Reporting Services service during the restore process click the Start button to start the service. If needed configure the URLs for the Report Server and Report Manager, instructions can be found in the Migration document under the Configure the Report Server section, I did not need to do this. For Reporting Services to recognize there is a different database you must go through the Configure Database process. In the Navigation panel click Database then click the Change Database button.
The following images show the screens and settings when configuring the database.
Now restore the Encryption Key from the database you restored. In the Navigation panel of the Reporting Services Configuration Manager select Encryption Keys and click the Restore Button. In the Restore Encryption Key dialog click the … button to locate the file you created when backing up the Encryption Key from the server you are migrating, enter the password you used and click the OK button.
Now this is where I had trouble. According to the Migration document, once the Encryption Key has been restored things should be working. When I tried to access my Report Server I got an error stating ‘The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)’. I opened Reporting Services configuration Manager and clicked Scale-out Deployment in the navigation panel and saw that there were two servers listed, the old one and the new one.
I did not find any mention of this in the migration document. After searching the internet I found some articles describing this issue and the fix. There are a couple of ways to fix this but the simplest is to delete the record corresponding to the old server in the Keys table of the ReportServer database. Once I deleted the record for the old server Reporting Services started working as expected.
Conclusion
After going through this process I found that yes, it does migrate my reporting server, but at the same time I felt there were a lot of moving parts and steps involved. There was also an undocumented issue that I had to address. When comparing this to the RSScripting tool this method was more difficult and provided much less control. With this method I have no control over what was migrated, everything gets migrated. With the RSScripting tool I can control what gets migrated and I did not experience any issues.
Next Steps
- Review the Migration document. Click this link.
- As always test on a test server before using in production.
- Be sure to inspect and test the new reporting server.
- For more information check these references.
