Craig Porteous

Migrating SSRS content with PowerShell

January 29, 2018 by

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

RS Scripter

You may still come across this first migration tool if you search for Reporting Services Migration or similar. There are several highly ranked links with publication dates in the last 5 years too! Don’t be fooled though, this tool is now 12 years old. The .NET application was created by SQL Server MVP Jasper Smith. This article “The Reporting Services Scripter”, (written by Kevin Kline back in 2006) describes the tool with a now defunct link to its original download page. (WebArchive)

This was a great tool to easily identify content in SSRS & migrate it directly to a target instance or download it to be uploaded later. It’s a shame it’s not still maintained as it became obsolete with the introduction of SQL Server 2008 R2. These new components and anything introduced since is not supported by RSScripter so they won’t be migrated. (Eg. Linked Reports & History)

You can still easily find the tool in a host of locations and it works fine for your normal folders, reports and data sources (though not the credentials).

Reporting Services Migration Tool

This tool is often another high ranking result when searching for SSRS migration tools. Released by Microsoft in 2012, it looks like a promising, “official” result. “Perfect! There is hope!”…maybe not.

This tool works by generating a full array of PowerShell scripts based on your source content, whether that be from a native or SharePoint integrated instance.

The first main roadblock is that you can only deploy to a SharePoint Integrated instance. Even though the PowerShell scripts are right there for you to edit, the source content is all explicitly called out and any potential alterations to force a deployment to a Native mode instance will involve a great deal of time editing and debugging. (Yes, I’ve spent hours doing this before!).

If your target instance is a SharePoint integrated installation then great! Read no further. It’s not the easiest application to use but it will get you from A to B and you can dissect & edit the PowerShell code if you want to customize your migration.

One feature that this tool has above almost all of the rest is that you can include Report History (if this is a feature you use) and not just the history settings. Although that does come with a stern warning:

Please note that migrating report history requires direct modification to data in the report server catalog and is not publicly supported. Backing up the catalog before migration and verifying correctness of the reports after migration are strongly recommended.

You can still find the Reporting Services Migration Tool hosted by Microsoft but there seems to have been no further development beyond the initial 2012 release despite this line in the description:

  • Target server must be SharePoint integrated mode. (We are working on support for native mode.)

Rs.exe VB Script

This Microsoft Page “Sample Reporting Services rs.exe Script to Copy Content between Report Servers” describes a sample script created by Benjamin Satzger at Microsoft, to migrate content between two Report Servers using the rs.exe application present in both modes of Reporting Services installation.

It does cover some of the shortfalls of the RSScripter tool & it can also migrate content to a native mode installation, unlike the Reporting Services Migration Tool (but it will not migrate report history).

There is some great documentation & details on the above Microsoft Docs page but it looks like the download link hasn’t (yet?) been updated from Codeplex, though the supported SQL Server versions do go right up to 2016 which is promising.

The script is all written in VB.NET using the Reporting Services SOAP API. It’s not as easy to use or as flexible as the PowerShell module but it’s certainly a more complete and relevant option than the two I have mentioned thus far. No prior knowledge of VB.NET is required which is also a bonus.

Constant backfill

This is a workaround I have personally adopted for a while but relates mainly to keeping various environments in line, and not a core migration method like the others. I wanted to mention it though as the migration of SSRS content is usually driven by the need to keep multiple non-production environments in line.

This method became part of my BI agile release process about a year ago though it is not without its own drawbacks. Prior to its inception, several days of my time were swallowed up re-building DEV or QA environments when they became too far out of date or some piece of work had impacted the content on a Report Server. I’d have to copy down our production database, remove or update the Scale-out deployment contents (servers), re-point every data source we have, manually (over 30!) to its DEV or QA counterpart & update any stored credentials. Then we’d have to update folder security to remove all the production permissions. This would also bring the environment down for the day or more that it took to get everything in place.

While moving to an agile deployment method I began exploring better ways to keep our environments in line. If no releases are missed, backfilling at the time of each production release was the least time-consuming method of keeping all our DEV & QA environments up to production version & could actually be automated easily with PowerShell or as part of a CI/CD process.

  1. Report kits are deployed to DEV & then QA
  2. Once approved, a kit gets deployed to Production
  3. Kit is then automatically deployed to cold QA & DEV environments with the same production code.

The main benefit of using this “backfill” method is that it removes the need, in most cases, to restore an entire database, recovering encryption keys, re-pointing data sources & altering security which can take more than a few hours to complete manually, depending on the size of your SSRS instance.

dbatools PowerShell module

This is a bulk method approach but much more efficient than doing a database restore through Management Studio. It is literally a single command.

The -IncludeSupportDBs flag I use here is actually designed to include the ReportServer and ReportServerTempDB anyway but seeing as those are the only 2 I am migrating, I have called out the ReportServer DB and I’m using the flag to bring the TempDB over.

You can then augment this script with the Reporting Services module to fix data source connection strings & make any further environment specific changes, which is actually my preferred migration method detailed in the conclusion below.

Although dbatools doesn’t cater to Reporting Services specifically, it’s a very powerful module for interacting with databases. You can find a full command list on the dbatools site

Reporting Services PowerShell module

Similar in functionality and flexibility as the old RS Scripter, the official PowerShell module allows you to download & upload individual items, specific folders or a full instance of content. You can also utilize the new REST API for SQL Server 2016 installations. Here are a few code chunks for tasks you might perform during a migration.

Migrate all SSRS folders

This code chunk will migrate from a source environment, down to a local temporary folder then back up to the destination environment. Setting the $Recurse variable to $true will add that flag to the commands and include subfolder contents too. It’s a great way to quickly migrate content but be careful to note that this only migrates reports, shared data sources & datasets.

Restore or backup an Encryption Key

I connect to the Report Server first, before restoring the key so that I don’t need to define the Report Server version, which is set in the Connect command. If you have dbatools however, you can get the version using the following command:

Update Data Sources from a reference table

In this script excerpt, I utilize a process I have implemented to backup all shared data source information within SSRS to a database table. See the Appendix below for that script. I then prompt the user for all of the passwords so we aren’t storing them anywhere. You can consider using the hosts file to negate the need to update connection strings but we’ve opted not to use that method.

You can also find a full command list on the module’s GitHub page

Conclusion

I find that every method has its drawbacks and even though the backfill process I use works great to keep non-production environments up to date, it won’t account for all of the content that never makes it to production or any removal or edits of reports, folders etc that can happen in development environments. This is where I utilize a hybrid of dbatools and the Reporting Services module

  1. Restore the RS Database
    1. Copy or Restore a Database backup using dbatools
    2. Set the SSRS Database (to implement Security, subscriptions etc)
    3. Restore the source encryption key to the new environment
  2. Delete all subscriptions copied down with the DB from Production (if desired) *[Remember to set up the proxy connection first]
  3. Update Data sources from a reference table (As above)
  4. Update folder security for the relevant environment. (I want to revert security to Inherit for a DEV environment in this case)
  5. Remove unwanted servers from the scale-out deployment
  6. Backup the encryption key (As Above)

Although there are a few steps in this method, I can manage each stage individually and the full process is done in under an hour with no step being performed manually which is the aim of the game! Depending on your environment you could even string these together into a single script for faster execution

Here are the functions and scripts for the full process described above. This can also be found on my GitHub page

Restore-RSDatabase

Set-RSFolderSecurity

Reset-RSScaleOut

Remove-RSSubscription

References


Appendix

SSRS Data Source Backup PowerShell Script

Craig Porteous

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous
Craig Porteous
1,119 Views