Migrating from Native to Integrated Mode SSRS Part 5: Testing, Subscriptions, & Implementation

***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.***

Huzzah, the reports are in SharePoint! Time to take a bow and exit stage left, right?

Not so fast! Have you tested them properly? Are your subscriptions configured in their new home? And what about the users – how do you get them rolling with SSRS in SharePoint? Read on to find out!

Testing

The good news about testing reports migrated to a new SSRS instance is that they’re still SSRS reports, and are still connecting to the same back-end data sources. What’s to test, it’s basically the same, right? Well, not quite:

• Manually re-pointed data sources are prime candidates for a “Wait, was this supposed to report from Staging or Production?” type of mistake. Get an extra pair of eyes on the reports to make sure that the data pulled is the data they want.
• Business user testing helps confirm security structures are functioning as they should. The reports may be the same, but they’re in a new home. Validate that the security is functioning as it should.
• If your reports source from non-Microsoft data sources, it’s important to test the rendering of those data types within SharePoint. We’re working through an issue with the GRAPHIC data types used by our DB2-based ERP system that throws the horrible “#Error” when the report tries to render data from those columns. If we CAST the report dataset columns to a VARCHAR the data can render, but that’s just a workaround. Only testing will uncover odd issues like this.

Speaking of error debugging, here’s an important item to enable in your reports libraries:

1. Navigate to the Site Settings for each report library’s site
2. Under Reporting Services, click on Reporting Services Site Settings  
   
3. Check the “Enable remote errors in local mode” checkbox
4. Click OK to save your changes

If you don’t check this box, dataset and data source errors will throw only a generic error message that’s useless for debugging. Check the box and save yourself some frustration!

Subscription Configuration

Creating SSRS subscriptions in integrated mode SSRS is a little different visually from the Report Manager experience, but the basic how-to is documented here. Rather than spend blog space on more screenshots, let’s review some gotchas from a setup and administration standpoint

• Setup: Get two monitors for side by side comparison, as again this is very manual work. With enough time and testing, this may be a scriptable process, depending on the complexity of your implementation.
• Permissions: To create subscriptions for yourself in SharePoint SSRS just requires you to have “Contribute” level access to the reports library. However, if you want that subscription emailed to anyone else, you need Full Control over the library. This might upset your apple cart for what users can do for themselves and what you have to take care of on their behalf. If this is going to be a major issue for you, it’s time to code an alternate “create subscription” agent. Get thee to the web services documentation!
• Shared Schedules: Remember you’ll have to create them per library. They make configuring multiple subscriptions much speedier, and unless your SharePoint server is horrifically underpowered, there’s no need to create a bunch of custom schedules to send reports five minutes apart from each other to fake “load balance”. If your SSRS instance can’t withstand a few subscription renders running at the same time, it’s time to reconsider your hardware architecture. Providing, that is, that your reports aren’t trying to behave like SSIS packages to produce massive extracts. 🙂
• File Drop Subscriptions: Do you have a different account running SharePoint SSRS? If so, you’ll need to grant that account rights to drop report files to the subscription paths. 
•  Subscription Niceties: Since you’re recreating the subscriptions anyway, can you improve them?
  • Send emails to group addresses instead of individuals whenever possible
  • Use the ReplyTo address to send to a support mailbox where applicable
  • Should the extract format change or be modernized? MHTML is a nice way to email brief reports in a way that renders within the email client, while PDF attachments are readable on nearly any device – smartphones, tablets, maybe even on the “smart watches” we hear buzz about. The old standbys of Excel & CSV won’t go away anytime soon, though, and are still valid formats for certain use cases. Some users just process information better if it’s in Excel, or you may be sending results in a CSV to be imported into a system on a different platform.

Implementation

The good news and the bad news about migrating an existing native mode instance to integrated mode SSRS is that it probably cannot be accomplished in a weekend. The real upside there is that the migration can happen organically, and you can adapt your approach as you migrate folders for different users. Some may consume their reports entirely via subscription, while others may have a much more dynamic relationship with their live reports. Knowing how your users consume their reports has the greatest influence on how you cut over to SharePoint SSRS.

Here’s what a typical cutover process looked like for me:

Documentation for Report Authors

You will gain a great deal of experience in modifying existing reports to point to SharePoint data sources and live in SharePoint libraries over the course of your migration. Soon, it will seem like old hat. However, report authors that aren’t involved in the migration will think the process is weird – saving to different site addresses, remembering to use a data source in SharePoint, etc. Make your own documentation on the tips & tricks for working with reports in your particular implementation of SharePoint SSRS. Yes, writing doco can be tedious, but think about how nice it is to answer a repeat question by just sending a link to the instructions! 

Communication – Two Weeks Prior to Cutover

Here’s an example of the communication I sent to alert users to the upcoming change. When possible, I’d have it sent by the business area’s department managers to raise its visibility.

If you access <<report folder name>> SSRS reports via <report site>, please review the announcement below.

The <<report folder name>> SSRS reports are moving to SharePoint! As of <<date>>, all <<report folder name>> SSRS reports should be run via their SharePoint address below. We welcome you to test the reports you use now, but know that as of <<date>>, you will access <<report folder name>> SSRS on SharePoint via https://intranet.mycompany.net/sites/OurProducts/Reports/

Where will these reports go?
<<Library name and link>>

Is the data the same?
Yes, the reports will work the same way as they have before, and still report on the same data.

What’s different about the reports being in SharePoint?
Now that these reports are in SharePoint, you can now access them from:

• Your work computer
• Your home computer
• Your mobile devices – smartphones & tablets! (Login credentials required)
• Your reports are also searchable via the Intranet, searchable only for those who have access to see them.

What about my email subscriptions?
All existing <<report folder name>> SSRS report subscriptions have been recreated in SharePoint, and will start running from there on <<date>>. You can confirm what subscriptions are configured for you for all SSRS reports via this report: <<link to SSRS report
How do I export a report to Excel or PDF?
Exporting is available via the Actions menu

What do I need to do if I write SSRS reports for <<report folder name>>?
Please see this documentation <<link>> on how to work with SSRS reports in SharePoint

What if I get an error?
Please send any errors or issues to <<support email address>> and include information on the report, parameters chosen, and the error or issue you encountered.

Cutover Day/Night

1. Via Report Manager, remove access to the native mode folder for all users except SSRS administrators.
2. Delete the existing native mode subscriptions for the reports within that folder
3. Remind users to start accessing their reports in SharePoint via whatever communication channel makes sense: email, enterprise social media, phone calls, desk visit, etc.
4. The next day, you can easily confirm that users are running reports against the SharePoint instance by adapting the “What Reports Are Actually Being Used?” query to pull from your SharePoint SSRS Reportserver database.

Be prepared for a smattering of “My report links don’t work/I’m getting a security error/I can’t see my reports anymore!” tickets over the weeks following the cutover. Browser favorites are still a dominant way for users to interact with sites, no matter how easy you make the navigation or URL. Once things have calmed down sufficiently and you’re confident you have source control or other backup of the RDL’s, delete the folder from native mode.

Lather, rinse, repeat!