Migrating from Native to Integrated Mode SSRS Part 1: Setting the Stage

***Update October 2014: Microsoft has put greater emphasis on Power BI and SharePoint online in the past year, and the future of on-premises SharePoint SSRS is uncertain. Microsoft may instead find a way to connect Power BI to native mode SSRS. At this time,  I’d caution teams with an eye to future Microsoft BI against undertaking a migration from native mode  to on-premises SharePoint online. This is just my opinion, not the opinion of Microsoft or my employer, but a project of this magnitude must be very carefully planned and risks weighed against the benefits.***

As someone who has worked with SQL Server Reporting Services for a number of years, the bulk of my experience is with “native mode” installations. Reports are accessed via Report Manager or custom developed .NET UI’s, and SSRS is a free-standing entity administered by someone in a DBA and/or BI role. However, if you wish to get access to the newest and best BI goodies from Microsoft, SSRS running in SharePoint Integrated mode is the direction you must go. The goal of this blog series is to share the lessons and techniques I’ve learned over the past months of migrating reports from a native mode to an integrated mode environment.

Why Migrate: The Short Version

Microsoft is putting their BI love towards in SharePoint in one way or the other. Do you want the new good stuff? Migrate!

Other compelling reasons:

• “Report” has multiple forms to many users. It might be an Excel pivot table, a PowerPivot workbook, a fancy new Power View or Power Map, or good ol’ reliable SSRS report. You can structure a library in SharePoint to allow all of those document types to be available in one location = happier users!
• “Dashboard” is an ever-popular buzzword, and SSRS reports often get tasked to provide daily status updates. If an SSRS report is housed in SharePoint, it can be embedded within a site’s front page, providing the most important high level details to the whole team every time they navigate to their site.
• If your corporate intranet via SharePoint can be accessed (via proper credentials) outside the internal network, SSRS reports become infinitely more accessible.
  • Viewable on executive’s iPad or smartphone
  • Accessible via an admin’s home computer when they need to check something quickly after hours
  • Available on operation manager’s smartphone for a quick check of something while walking between meetings
  • Reports in SharePoint exists as a document content type. This means they can be searched, indexed, categorized and tracked via custom metadata fields like any other document in SharePoint.

Infrastructure – Software

Here are the editions of SSRS and SharePoint involved in my migration experience:

• SQL Server Reporting Services 2012 SP1 Business Intelligence Edition (64-bit)
• SharePoint 2010 Enterprise Edition SP1/ 2013 Enterprise Edition as of August 2013

Don’t bother trying to run SSRS in Integrated mode on anything less than SharePoint 2010 SP1. Ideally, you’ll have updated your SharePoint 2010 environment to SP2 by now. Some of you may even be lucky enough to be running SharePoint 2013 already. However, anything less than 2010 SP1 isn’t worth adding SSRS to, it’s just not going to be supportable/stable enough to be worth the configuration work. Many of our lessons learned may well apply to SSRS 2008 R2, though I’d argue that it’s just not worth putting all that work into setting up an environment won’t be able to leverage the BI features of SQL 2012 + SharePoint.

There is little material difference between administering SSRS on SP 2010 vs. SP 2013. The important software version in this case is SSRS 2012, as it becomes integrated more fully into SharePoint than in previous iterations. No longer is SSRS configured and managed via SSRS Configuration Manger. Instead, SSRS becomes just another service application within SharePoint and is configured and administered largely through SharePoint Central Administration.

For more guidance from Microsoft themselves, see this post.

Infrastructure – Hardware

We are fortunate in our SharePoint architecture, as we have both web and application servers rather than combining all on the same servers. Yours may be more consolidated, but whatever you do, be generous at least in the RAM allocation. SSRS still wants all the memory it can have, and now it’s not the only kid in the server’s pool – it has to share with other SharePoint service applications. Make sure there’s enough to go around. We have 64 GB of RAM on our application tier servers, and that’s working well.

Shared Data Sources

If your SSRS reports aren’t already taking advantage of the cleaner administration and support that shared data sources allow, the migration to SharePoint is the time to embrace them. You can house the shared data sources in an SSRS library on SharePoint that all users have access to, then connect the reports across all sites to their central location. Note that you CANNOT use native mode shared data sources in a report housed in SharePoint. If the reports live in SharePoint, the data sources have to live there too.

Report Content Organization

There are as many opinions on the “right” way to organize content as there are pieces of content, as anyone who has ever worked on a corporate intranet structure design knows. The topic of the right way to organize reports gets to have a rehash as part of your migration (wait, come back, it’s still worth doing!). The difference boils down to:

• Native Mode: One website for SSRS reports, organized and secured via subfolders
• Integrated Mode: SSRS reports organized and secured within Reporting Services site libraries.

You may be tempted to mimic Report Manager:

• Corporate Intranet
  • Reports Site
    • Reports Library
      • Finance Reports
      • Sales Reports
      • …

After all, as an administrator or report author serving many departments, it’s annoying to go to more than one site for reports, right? But who are the reports for, exactly – for the authors or for the users?

Let the structure of the report audiences you’re serving dictate how the reports are organized.

• Corporate Intranet
  • Finance Site
    • Reports library
• Sales Site
  • Reports library
• …

You can also leverage Views in SharePoint to further organize reports within a site. For example, the report developers may wish to view the reports and data sources all together, while the business may prefer the reports grouped by Report Category and to have the data source objects hidden.

The site security can be used to secure the SSRS library, or you can get more granular to lock down the library further, but use subfolders as little as possible. Later in this blog series, we’ll cover using the Report Category field to provide the organization that subfolders did in native mode.

Report Content Migration

The migration process from native mode to is more manual than you might think. You are transitioning from one SSRS concept to another, so it’s not an apples-to-apples conversion that can be scripted out. Perhaps someone who is exceptionally talented with PowerShell can make things slightly less manual, but in the end we could not escape the reality that each report needed to be touched by hand. Multiply that over hundreds of reports and subscriptions and you’ll see why this is not a migration that can be easily accomplished in a weekend. I will go into the detail of the migration process we followed later in this blog series.

Our general migration pattern per main level folder:

1. Use queries against report catalog metadata to determine scope of report usage, data source dependencies, and subscription volume.
2. Review usage and subscription information with the main business contacts to scope which reports and subscriptions were still useful and which could be archived.
3. Create and configure the reports library on the appropriate business team’s SharePoint site
4. Migrate copies of the reports to the new SharePoint reports library
5. Invite users to test their reports for a set period of time
6. Configure necessary subscriptions per report, set to start running on SharePoint at the predetermined “cutover” date.
7. On the day of cutover
   1. Remove access to the native mode folder for all users except SSRS administrators
   2. Delete the existing native mode subscriptions for the reports
   3. Remind users to start accessing their reports in SharePoint

Staff Resources

• SharePoint Administrator
• SharePoint content/site administrators
• SSRS Administrator
• SSRS report authors

A migration from native mode to integrated mode cannot be successful without a good partnership between the SSRS administrator and the SharePoint administrator. I’ve been fortunate enough to learn about SharePoint at the feet of David Peterson over the course of our migration. If you have no experience whatsoever with SharePoint and don’t have someone within your organization to work with, it’s time to factor the cost of SharePoint training into your migration budget. SSRS is too fully integrated into SharePoint as of SQL 2012 to blunder your way through without a guide.

You’ll need the cooperation of the report authors and site administrators throughout the migration, and it’s quite likely that some roles will overlap. As an SSRS admin, I’ve also taken on the SSRS report author role for a majority of the manual migration work, as well as performed some basic site admin work to set up the report libraries.

Coming up next – Part 2: Essentials for Your Toolbox