Wednesday, February 15, 2017

SQL Server Report Server Migration - Step by Step Guide

Scenario :

Source SQL Server Reporting Instance : SQL Server 2008/12/14/16

Destination SQL Server Reporting Instance : SQL Server 2008/12/14/16

Solution : 

There are two methods for the  migration of reporting server from One instance to another. The easiest method and the hardest method. You may choose the hardest method if you want to consolidate multiple report servers into single. And if you are willing to have multiple report servers in your environment, go ahead with the easiest method.

1) Easiest method : Database Migration
2) Hardest method : Reports Migration

1) Easiest method : Database Migration
 Setps to follow : 
  • Backup the Source Reporting Server Databases : e.g. ReportServer and ReportServerTempDB
  • Backup the Encryption key from Source Reporting server instance
  •  
  • Restore the Database on destination reporting server SQL Instance (Not required if you want to keep the existing Database server same.
  • Restore the Encryption key on Destination Reporting server instance
  •  
  •  
  • Do the remaining configurations like URL for Report Manager, SMTP, Service Account etc.
  • It done.
2) Hardest method : Reports Migration
Please go thru the below link for reference :
https://msdn.microsoft.com/en-us/library/dn531017.aspx 

Native Mode Report Server to Native Mode Report Server

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password" -v security="True"  

Native Mode to SharePoint Mode – root site

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p Password -v ts="http://TargetServer/_vti_bin/ReportServer" -v tu="Domain\User" -v tp="Password"  

SharePoint Mode to SharePoint Mode –‘bi’ site collection

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/_vti_bin/reportserver -v st="sites/bi" -v f="Shared Documents" -u Domain\User1 -p Password -v ts="http://TargetServer/sites/bi/_vti_bin/reportserver" -v tst="sites/bi" -v tf="Shared Documents" -v tu="Domain\User" -v tp="Password" 

Native Mode to Native Mode – Windows Azure Virtual Machine

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\user1 -p Password -v ts="http://ssrsnativeazure.cloudapp.net/ReportServer" -v tu="user2" -v tp="Password2" 
 
If you want to enable debugging for the Reporting server, open Report Server configuration file from the Report Server installation location
For my case it is : 
D:\Program Files\Microsoft SQL Server\MSRS13.2014_RS\Reporting Services\ReportServer\bin

Open file "ReportingServicesService.exe.config"

Make the changes as shown in below line : 
    <add name="DefaultTraceSwitch" value="4" />
    <add name="Components" value="all:4" />

You may trace the log files in below location : 
D:\Program Files\Microsoft SQL Server\MSRS13.2014_RS\Reporting Services\LogFiles

IMP Note : In this migration, your Developer needs to work hard to input the password in each report as all above mentioned command does not migrate the passwords.
In our case it was running into Thousands.

Hope you got the basic understanding of migration methods of the SQL Server Reporting server

Feel free to reach out to me in case you stuck some where...

Cheers!

- Ashish Kesarkar
India

1 comment:

Solved : The user profile failed to attach. The process cannot access the file becase it is being used by another process

Issue :  The user profile failed to attach. Please contact Support. Status : 0x0000000B, Message: Cannot open virtual disk Error Code: 0x000...