Sharepoint Integrated mode – worth the hassle?

With SQL Server 2005 SP2, Microsoft introduced Reporting Services with Sharepoint Integrated mode. The idea was to integrate SSRS 2005 with WSS 3.0 and MOSS 2007. Prior to SP2, there was already some integration; it consisted of 2 web parts, Report Explorer and Report Viewer, which allowed reports to be selected and launched while remaining in the Sharepoint portal.  But SSRS and Sharepoint were two distinctly separate systems with separate storage and security models. The new mode allows us to use Sharepoint document libraries to store reports so that reports can be published, viewed and managed using the same user interface as is used for other documents in Sharepoint. Will this help us out? Let’s take a look.

First of all, you will need WSS 3.0 or MOSS 2007 running on your system. When SQL Server 2005 SP2, or above, is applied, new options are available under the SQL Server Reporting Service Configuration Manager regarding Sharepoint Integration. The default mode is “Native” which corresponds to the architecture before SP2, the status quo, no integration (except the web parts). To configure for Sharepoint Integration, you must first create a new Report Server database for Sharepoint Integrated mode. This is an option under the Database page when creating or changing the database. The new database will contain the meta-data changes necessary to support the new mode. A nice feature here is that you can switch back to Native mode at any time, as long as you have a copy of the original Report Server database. That’s why I usually add “WSS” to the database name when in integrated mode (ReportServerWSS) so I know which is which. Of course, backups are important here too. Once you do this you are giving up certain features like Report Manager, My Reports, Linked Reports, Job Management and the ability to use Management Studio for administration, which is a shame. But, in the name of progress, you will be using Sharepoint as the single point of access for administration, publication and delivery. And who wants to stand in the way of progress?

The next step is to configure Sharepoint itself for Report Server integration. You need to download and install the Reporting Services Add-in for SharePoint Technologies and then walk through the configuration steps in Sharepoint Central Administration. A new section under Application Management will guide you through the configuration. Books Online also helps with the process. If Sharepoint and SSRS are on different machines, it is a best practice to use a domain user for the service account. This is because the Sharepoint database and the Report Server database will need to synchronize the report definitions that are deployed in both databases.  It should be noted however, that scheduling, caching, and subscriptions are stored in the report server database only because this functionality is not available in a SharePoint document library.        

After that you can use Sharepoint document libraries to deploy, view and manage your reports. Security is handled in the same way as for any Sharepoint document. End Users can also use Report Builder directly from Sharepoint, if they wish, to develop their own reports, once given the authority. So if you always use Sharepoint to expose your reports, this may be an option for you, enabling a centralized approach through Sharepoint.

So what are the drawbacks? Well, I have noticed that performance of reports takes a hit. This is major, of course.  Apparently, this will be improved when SQL Server 2005 SP3 becomes available. Also, if you are like me, not a Sharepoint expert, you may feel a bit like a fish out of water when most of the major SSRS user interfaces like SSMS and Report Manager are cut off completely. They are just not available in Sharepoint Integrated mode. Also, if Sharepoint comes down, you are out of luck, obviously. At least in Native mode you could still offer Reporting Services as an independent option. And I like keeping my options open. So for now, I’m living like a native.

Cheers

Brian

Recent Posts:

Microsoft goes for Dundas – the one that got away?

Dear Diary - SQL PASS in Seattle – Day 3

Dear Diary - SQL PASS in Seattle – Day 2

Thoughts on Green Conferences

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Related:

Copyright © 2008 IDG Communications, Inc.