Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases


By:   |   Read Comments (29)   |   Related Tips: 1 | 2 | > Reporting Services Migration

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.

Reporting Services Configuration Manager

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.

Reporting Services Configuration Manager

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....

SQL Management Studio

Make sure the Backup Type is set to Full and specify a location and filename for the backup.

SQL Management Studio

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.

SQL Management Studio

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.

SQL Management Studio

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).

SQL Management Studio

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.

Reporting Services Configuration Manager

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.

Reporting Services Configuration Manager

The following images show the screens and settings when configuring the database.

Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager

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.

Reporting Services Configuration Manager

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.

Reporting Services Configuration Manager

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


Last Update:






About the author
MSSQLTips author Dale Kelly Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, March 31, 2017 - 11:36:55 AM - Michael Back To Top

Genius!  Thanks for these notes, saved me a ton of time. 

 


Wednesday, March 01, 2017 - 10:29:02 AM - saji Back To Top

 excellent, I have "scale out" and edition error crop up, both resolved with deleting old server information from keys table.

Saved my lot of time :)

Thanks

 


Friday, February 10, 2017 - 4:16:32 PM - vanrani Back To Top

 Hello Dale,

Thank you for your tips, it is very helpful. I had to move only the SSRS db from one server A to Server B. After moving the ssrs dbs, what i suppose to do all the subscriptions jobs created on ServerA SQL agent?

 

Thank you in Advance.

 


Wednesday, May 04, 2016 - 6:11:55 PM - Jim Back To Top

Thanks for your post, it had the key thing that Microsoft seem not to mention

But a really important thing to note is that you need to delete the Keys value from the database *before* you go any further. So my steps outline was:
1 Turn OFF the RS service
2 Restore the databases
3 delete from keys where MachineName = 'sourceserver'  -- i.e., do this before the RS service finds it
4 Turn ON the RS service
5 Connect the database into it
6 Restore the encryption keys
7 Open Report Manager

Cheers


Tuesday, April 05, 2016 - 5:39:15 PM - Russell Tye Back To Top

 

 I thought this was a great presentation. Kudoes to you!


Thursday, August 27, 2015 - 12:57:48 AM - Ganesh Back To Top

Hi Dale,

Can you give brief about moving ssrs 2008 32 bit to 2012 64 bit.

 

 

 


Tuesday, July 21, 2015 - 3:27:39 PM - Bobby Back To Top

Just would like to add, I followed every thing mentioned to the T but after deleting the key from the dbo.keys table ( the last step) could'nt access my report manager URL with the error message.

 

THE REPORT SERVER INSTALLATION IS NOT INITIALIZED . Solution is to delete the keys form the reports configuration manager and run the database configuration form the reports configuration manager again.

 

 

 

 

 

 

 

 


Tuesday, March 31, 2015 - 8:43:43 AM - Forrards Back To Top

If you`re itnerested in Crystal reports to SSRS migration and pros and cons of such proejct take a look here 

http://www.forrards.com/MigrateFromCrystalReportsToSSRS

http://www.forrards.com/ReportingServices


Friday, January 30, 2015 - 10:29:19 AM - CLi Back To Top

Hi Dale,

Thanks Dale. I have a problem to migrate the ReportServer database from SQL Server 2005 to SQL Server 2012. I have followed your comments. And I am able to cnfigured the restored ReportServer in Reporting Services Configuration Manager 2012 without error. But I am not able to open the link http://localhost:8080/reports or http://localhost:8080/reportserver. Both link are given error "

  • An internal error occurred on the report server. See the error log for more details. (rsInternalError)"
Do you have any advice? thanks here in advance.

 


Thursday, September 25, 2014 - 2:13:38 PM - Singh Back To Top

Thanks Dale! The Scale-out issue took me some time to figure out, your tip was very helpful. 


Friday, July 04, 2014 - 1:27:44 PM - Manjunath Back To Top

Hi

I need to migrate custom reports. I saw this text above "Move Custom Assemblies or Extensions" but could not locate in the site. Can some one help me with this document please?

 

Thanks

 


Thursday, April 03, 2014 - 5:21:00 PM - Robin Back To Top

Thank you so much for the article!

I just finished migrating the report server from SQL Server 2008 to SQL Server 2012 after following the database backup/restore steps mentioned here.

I just had to change a few data sources pointing to the new server , for those reports where the databases has been moved to the new server as well.

Everything works great except that the new reporting services server (http://reportservername/reports)   is  so slow after the migration. Even accessing the folders within takes 30-45 sec average. Do you have a workaround for this? Any help is greatly appreciated. Thanks!


Wednesday, April 02, 2014 - 3:26:31 PM - Craig Back To Top

For another option, the following topic describes in detail the approach and a sample to copy SSRS items from one server to another using script.  The script supports either SSRS server type.  For example, Native to SharePoint, SharePoint to Native, SharePoint to SharePoint....

"Sample Reporting Services rs.exe Script to Migrate Content between Report Servers"

http://msdn.microsoft.com/en-us/library/dn531017.aspx


Tuesday, July 30, 2013 - 12:13:04 PM - George Back To Top

Smooth as silk Dale, thanks for the article.

 

 


Tuesday, July 02, 2013 - 10:07:54 PM - Dan Back To Top

Thanks, the screen shots were very useful. I found that with SQL Server Enterprise, this is the way to go.

I was able to do the migration and simplify the installation at the same time. I didn't need to do the last step. I tried it to see what would hapen and it broke my configuration. I actually need the Reporing Service database to be referenced on a common domain server by two web-facing (DMZ) Webapp servers. I did this using a pair of SQL Server account users.


Monday, June 24, 2013 - 5:49:12 AM - hemanth Back To Top

Thanks a Lot.


Friday, April 19, 2013 - 12:21:31 PM - Konstantin Back To Top

Hi Can anyone tell me how to move just one report from one report server to another to perform there a performance test.All the tutorials around the network are how to move everything.


Friday, February 08, 2013 - 1:33:05 PM - Dinesh Back To Top
 
Nice...

Monday, December 17, 2012 - 7:32:49 AM - Jack Owens Back To Top

In this URL in 3rd image from top I was able to see your server name in "Object Explorer Details" section as  "CPSHOULD815", might want to edit the image

 

Also based on your TEST Server naming convention anybody woud guess Production Server Name too ;)


Wednesday, August 29, 2012 - 9:08:37 AM - Simon Doubt Back To Top

Thank you kindly for the post. I, too, found the part about resolving the 'scale out deployments' very helpful. Nothing better than a quick and easy fix!

 


Wednesday, August 22, 2012 - 9:30:44 AM - Nasir Back To Top

Well done... 

 

Many thanks for your post..


Monday, May 21, 2012 - 8:29:15 AM - Dale Back To Top

James;

If you are refering to the subscriptions on the old server, yes you should disable these. It can be done by going to the reporting server and removing or disabling the subscription on each report. Or, you can disable the job under SQL Agent. You will need to determine which job is for what report since they do not have nice titles. I was able to find a script on the internet that will list the report and the associated job. Here is a link http://www.mssqltips.com/sqlservertip/1846/how-to-easily-identify-a-scheduled-sql-server-reporting-services-report/.


Sunday, May 20, 2012 - 8:09:24 PM - James Back To Top

Just a question. Do I need to disable the schedules on the old server? If so how? Thanks.


Sunday, May 20, 2012 - 8:08:26 PM - James Back To Top

 

Very helpful post especially the part about Scale-out Deployment. Thanks.


Friday, May 18, 2012 - 3:21:27 PM - Dale Back To Top

Mathew;

When I did this test migration it recreated the subscription jobs once I completed the last step. It did this after fixing the Scale out deployment issue.


Friday, May 18, 2012 - 3:19:15 PM - Dale Back To Top

Hello Ankit;

I am sorry I have not done a migration from Crystal to SSRS so cannot help.


Friday, May 18, 2012 - 1:02:52 PM - George Su Back To Top

For Crystal Reports to SSRS convertion, Crystal Migration Services provides the best solution at the lowest price. There is sample conversion available. So take a try and there is nothing loss.

The URL is as follows.

http://www.crystalmigrater.com/Default.aspx

 


Friday, May 18, 2012 - 11:52:47 AM - Mathew Newton Back To Top

Hi,

What about scheduled subscriptions? These are stored as jobs in SQL Server Agent.


Friday, May 18, 2012 - 9:42:50 AM - Ankit Shah Back To Top

Hi Dale ,

   It's Nice and very helpful post. i would like to know that have you done conversion or crystal report to SSRS ? I am working on that project now and i need step by step help .so if you or anyone can help me would be much appereciated .

Thanks


Learn more about SQL Server tools