SSRS 2008 R2 to SSRS 2016 Migration

Question

I have to migrate 770 SSRS Reports (Includes both Native mode and Sharepoint Mode Reports) from 2008 R2 version to SSRS 2016 version. Which is the best tool for this SSRS migration ? I have seen few tools like Microsoft Reporting Services Migration, RS script etc. Which one will be best in this case? Please help.

Answer 1

I too am in the midst of similar SSRS migration (2008SSRS:2016SSRS). You will likely need to migrate content such as: reports, shared data sources, shared data sets, report subscriptions. Below are some tools and processes that have used with pretty good sucess. I've included relevant links for Youtube videos, StackOverflow posts, and Microsoft pages.

Tools:

• ReportSync is an open source program free to download and use. It works great for downloading reports in bulk, and it can even push a report from one server to another server.

  • Download the source code files from Github: Phires/ReportSynch, Run VS, Open the solution file (.SLN), compile the program, find the executable file (.EXE) from the C:\Temp\reportsync-master\bin\Release folder. Finally, saved the .EXE somewhere for you to use regularly

  • The Export feature works marvelously for simply dumping all the RDL files to a folder for me to access and add to a VS Solution Project. With 770 reports, you will greatly appreciate the Export option.

  • How do I copy SSRS reports to a new server if I am not the owner of the reports --> ReportSync answer by nunespascal

• RS Utility with ssrs_migration.rss script file is similar to ReportSync program, except it is run via command line and published by Microsoft. In my experience, this utility will migrate more SSRS content than the ReportSync program, but I do not find it as simple to use; For example: You cannot easily cherry-pick the content you would like to move (it moves everything within the Source Folder to Target Folder). For more information:

  • Microsoft Docs article: RS Utility Script To Copy Content Between Report Servers

  • Youtube: Automating SSRS Report Deployment

  • Stackoverflow links--> ssrs_migration.rss file (RS Utility script file download), SSRS Migration Sharepoint Integrated to Standalone (RS Utility script file syntax), how to migrate or copy SSRS datasource from one server to another without restoring the report server (RS Utility script file download)

As an example, the following command moved all datasources from Source to Target server...

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://gcod049/ReportServer -v ts=http://gcop046/reportserver -v f=”/Data Sources/03-PROD” -v tf=”/Data Sources”

• Visual Studio 2015 with SSDT addin is my preferred environment for storing and deploying the files to the report files DEV SSRS server (.RDL- Report Definition , .RSD- Shared Datasets , .RDS- Shared Datasources, rptproj- Report Project).

  • I have a report project file for each target folder on the server (~20 ssrs folders and vs projects). You configure the SSRS Project with the appropriate deployment information (server url, ssrs version, target folder, etc.)

• Version Control will help you be much more agile with report change requests. This will allow you to make changes to the report fiels (.RDL) and quickly restore or compare changes.

  • I use Visual Studio 2015 with ANKHSVN for Visual Studio connected to SVN server repository

  • I've heard others on the SO community recommend Microsoft Azure DevOps Services

Process:

• I also analyzed the report execution history to identify reports no longer used. I decomissioned these reports the DEV SSRS server to a folder called something to the effect of: \Decomissioned\SSRS 2008 Migration\

Links:

• Youtube: SSW migrates SSRS reports from SQL Server 2008 R2 to 2016 --> This video references a tool that looks interesting but I haven't yet tried it... SSW: SQL Reporting Services Auditor

• I answered a similar question on this post-- Migration from SSRS 2012 to SSRS 2017.

Answer 2

The ReportingServicesTools Powershell module can help, too, if you're into Powershell.

But I'm not having luck with using it to push data sources pulled from the SSRS 2008R2 Server to the new SSRS 2016 server (e.g., using Write-RsFolderContent cmdlet), perhaps due to most of those data sources having passwords in them, and the two servers not having common encryption keys (e.g., the keys from the SSRS 2008R2 server were not extracted and then applied to the SSRS 2016 server). That's just a SWAG.

Write-RsFolderContent push a directory full of report .RDL files up, though.

MS has it documented well the "restore old db into new SSRS Server" scenario. Do note that this is a 1:1 migration.

If you have different AD users/roles on the new server compared to the old server, you might want to first set them up in the old SSRS instance first if you can (oops). It'll save some time and WTF??? If not, the cmdlets in ReportingServicesTools can bail you out, too, since it's twiddling things in the database directly.

Do refer to the MS page for all the details, especially if doing a SharePoint mode migration). This is only summary from recent memory for doing a "native" install/migration scenario:

• backup the SSRS 2008R2 encryption keys
• do a "files only" SSRS 2016 install
• restore the SSRS 2008R2 databases (ReportServer, ReportServerTempDB) to the intended SQL Server 2016 server
• In the SSRS 2016 reporting services manager app, point SSRS to the newly restored ReportServer database
• Also, import the SSRS 208R2 encryption key file into the new SSRS 2016

Do note that the stored report RDLs do not get upgraded to the new SSRS 2016 namespaces, but they should still run in compatibility mode in SSRS 2016.

Is there a good bulk way to upgrade the RDLs to the new namespaces other than to use SSDT in VS 2015/2017? If not, then use the VS 2015/2017 hammer...

• prereq: latest SSDT for VS2015 or VS2017
• create a new SSRS project, one per SSRS folder (either for SSRS 2008R2 folder or new target folder on SSRS 2016)
• add all the data sources needed for the reports to the project (they're needed to build the RDLs first) into the project
• add all the old RDLs that you want to deploy to the target folder into the project
• configure the project build properties to point to the target SSRS server, right report folder, etc.
• Build the project, then Deploy the project (or individual reports)

From VS2015/2017 SSDT project, the RDLs in the project top-level folder will be converted to be SSRS 2016 RDL files.

What gets deployed are the RDLs from the /bin/[Debug|Debug Local|Release] folders after building the reports.

Answer 3

You don't need a migration tool - steps are fairly easy to execute manually

Migration procedure:

1) Export your encryption key from SSRS Configuration Manager

2) Backout ReportServer & ReportServerTempDB databases

3) Copy these files over to the new environment

4) Install SQL Server & SSRS on the new node

5) Restore both Report databases over to the new node

6) Start the new SSRS service attaching to the "existing", newly-created database

7) Import old encryption key into the new SSRS Config Manager

8) Manually edit ReportServer.dbo.Keys table, deleting the record with the old node - eliminating "The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)" - error. I think you can remove it from the Config Manager GUI as well, using the "Scale-out deployment" menu item.

9) Force creation of the default web services for the ReportManager and Reports URL from the Config Manager

You will then have a full node migrated. You will probably also need a recent Report Manager installed separately, depending on your SQL Server edition.

Migrated recently 2008 >> 2017, without much ado.

Hope this helps.