1

I have a customer consolidating and upgrading two different SQL instances into a new 2019 cluster. Both of those older instances have an SSRS deployment on them. 2019 doesn't allow multiple SSRS instances on the same server, so I was wondering if it is possible to merge two different SSRS databases. Or am I going to have to go the route of upgrading/migrating one instance and then export/import the reports and data source definitions from the second instance? If that is the answer, then is this powershell module the best way to do that and are there any gotchas that I need to be careful of? (ReportingServicesTools https://github.com/microsoft/ReportingServicesTools).


Late update to this question as the customer postponed the migration by a few months. Following on from the answer provided below, I ran the following commands to merge the reporting server instances.

On source server create a folder to hold exported objects, e.g. C:\SSRS_Txfr The following command will start at the top level folder and recurse down the folder tree exporting most SSRS objects.

Out-RsFolderContent -ReportServerUri 'http://<Source SSRS Server>:80/reportserver' -RsFolder / -Destination "C:\SSRS_Txfr" -Recurse -Verbose 

On target server, create folder to hold exported objects and copy over from source server, again example used is C:\SSRS_Txfr

Create a folder in SSRS to hold transferred reports to avoid accidental overwrite or objects not copied due to existing object with the same name

New-RsFolder -ReportServerUri 'http://<Target SSRS server>/reportserver' -Path / -Name SSRS_Transfer -Verbose

Then load the exported objects retaining folder structure

Write-RsFolderContent -ReportServerUri 'http://<Target SSRS server>/reportserver'  -Path "C:\SSRS_Txfr" -Destination  /SSRS_Transfer -Recurse -Verbose 

I will note that the customer had a number of Models in their source SSRS server that the scripts would not transfer. The customers BI team picked those up as well as updates to the Data Sources post-transfer.

1
  • I would spin up a new instance and then use the listed scripts to migrate both source instances. Alternatively, you could just redeploy from source control if you have it, although recreating subscriptions may be problematic depending on how many you have. But the scripts you reference are what I consider to be current best practice for manipulating SSRS in the way you need. Aug 12, 2022 at 12:31

1 Answer 1

0

I have personally done a few migrations where we have combined multiple servers into one.

The best way is to create a new server, then use Microsoft's Power Shell Reporting Services Tool to migrate reports, datasets, and subscriptions over. Then you validate everything is working.

The only real issues that you would face is 1) if you are using mobile reports, they don't migrate over well so expect to manually add them. 2) if you have the same report name in the same folder migrating from multiple servers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.