Skip to main content

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 : 

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


- Ashish Kesarkar


Post a Comment

Popular posts from this blog

Changing Default Port of WSUS Server from 8530 to 80?

Problem : How to change Default Port of WSUS Server from 8530 to 80?Environment : Windows Server 2008 R2 with WSUS 3.0 SP2Solution :1) Go to Start -> RUN -> inetmgr

2) Go to "WSUSAdministration" Site

3) Under "Edit Site", click on "Bindings"

3) Change the port from 8530 to 80, and click OK

4) Open Registry Editor with Start -> RUN -> regedit

5) Goto HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup

6) Change "Port Number" Value to "80"

7) Goto Command prompt and type following command
cscript "WSUS Installation Location\Update Services\setup\installselfupdateonport80.vbs
"cscript "c:\Program Files\Update Services\setup\installselfupdateonport80.vbs"
C:\Windows\system32>cscript "c:\Program Files\Update Services\setup\installselfupdateonport80.vbs"

Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
SetupSelfupdateTree: Finding th…