SQL Server Report Server Migration from SQL Server 2008 R2 to SQL Server 2012 with Subscription

Hello Friends,

Following are the SQL Server Report Server migration steps where initial level of SSRS understanding are pre required.

1)      Take RSReportServer.config file backup on 2012 instance.

2)      Take a backup for ReportServer and ReportServerTemp database from 2008 instance and copy these backup into 2012 instance.

3)      Take a backup for encryption key from report server configuration 2008 and copy this key into 2012 instance. Please enter solid password to save key.

1_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

4)      Connect to SQL Server 2012 instance.

5)      Stop SQL Server reporting services. Here we are considering that SSRS already installed into 2012 instance. Configuration of reporting services on 2012 is another topic.

2_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

6)      Open SQL Server Management studio and restore ReportServer and ReportServerTemp database there with over right option.

3_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

7)      Start SQL Server reporting services.

8)      Set database connection with ReportServer database with used service credential.

4_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

9)      Restore Encryption key on Report Services Configuration Manager. Use same password what used at time of backup (Step 2).

   

5_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

Copy Custom Configuration Settings to RSReportServer.config File:

If you modified the RSReportServer.config file or RSWebApplication.config file in the previous installation, you should make the same modifications in the new RSReportServer.config file.

Verify Report Server:

To verify Report Server are working fine please click on Report Manager URL which is already set on report service configuration manager. You may encounter an error like this –

Error:

 The feature: “Scale-out deployment” is not supported in this edition of Reporting Services.

Solution:

The easiest is open the ReportServer database in SSMS on the new server and open the Keys table. You should see an entry for the old server and the new server, delete the entry for the old server .

USE [ReportServer]

select * from dbo.Keys where MachineName = ‘XXX-Old’ – Check if old one exist

delete from dbo.Keys where MachineName = ‘ XXX-Old ‘ – Delete if exist

Check Subscription:

If reports have subscription then it should be copied over 2012 instance. You can check subscription on Report Manager URL. Just expand report and click on Manage option. Then you see window where select Subscription to see all for this particular report.

6_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

You can check subscription either click on Edit or New Subscription. If lt is good then you can Add/Edit subscription. Doing this might result in below error.

Error:

Subscriptions cannot be created because the credentials used to run the report are not stored.

Solution: You need to add user name and password into report data source into “Credentials stored securely into report server”. In case of domain account check the “Use as windows credentials when connecting to the data source” check box to avoid below error

Login failed for user “DOMAIN\USER NAME”

Below screen shot can help to understand settings.

7_SQL_Server_Report_Server_Migration_from_SQL_Server_2008R2_SQL_Server_2012_ Subscription

Other than the above shown steps the important part is configuration file RSReportServer.config which is stored at:

\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

Please take backup from target server before changes in report service configuration manager.

 

Regards

Neeraj Yadav

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

   

Leave a Reply

Your email address will not be published.