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.
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.
6) Open SQL Server Management studio and restore ReportServer and ReportServerTemp database there with over right option.
7) Start SQL Server reporting services.
8) Set database connection with ReportServer database with used service credential.
9) Restore Encryption key on Report Services Configuration Manager. Use same password what used at time of backup (Step 2).
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.
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.
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