Print | posted on Saturday, February 24, 2007 2:59 PM
There's been a lot of interest in the enhanced integration between SQL Server 2005 Reporting Services and SharePoint 2007 delivered by SQL Server 2005 Service Pack 2. This truly is some awesome functionality and useful to anyone looking at Report Center in MOSS, or indeed those who currently have a bespoke Web UI for their reports and are looking for something a bit more robust and feature rich.
This integration is yet another example of the seamless integration opportunities available in SharePoint 2007, and it's place within the enterprise as a platform providing super rich frameworks on which to base composite applications.
From a (cynical) commercial perspective this is quite interesting and somewhat amusing as it could be surmised that Microsoft are now using SQL Server to upsell SharePoint - an enormous opportunity. This could be considered the reverse of the more traditional using cool apps (like SharePoint) to upsell the SQL business.
The SharePoint addin has been in CTP form for some time, but now it's here for real as SP2 finally got done! As all you Vista heads out there know, about time too! :) It works for both WSS and MOSS, obviously some things require MOSS.
Here's a "in a nutshell" guide to deployment...
On the Reporting Services box...
Firstly SQL Server 2005 SP2 adds a SharePoint Integration node to the Report Server Configuration Manager. All you need here is to install SP2. Now report server has a couple of modes. Native - the same as before SP2 - the default configuration and SharePoint Integration - this is what we are interested in.
But wait a second - if you are installing in a farm - you must install a SharePoint Web Front End on your Report Server, as this machine must be part of the farm(!) and have access to the SharePoint OM. If you are messing about on a single machine then obviously this is no problem, but it certainly encourages separate machines for databases and features of SQL Server such as Reporting. This should be a consideration in your overall farm design.
Following on from this, all your Report Server instances must be in SharePoint mode, you cannot mix and match in the same deployment.
Most SharePoint Farm/Topology design material relegates SQL Server to a simple 'backend' and there is little focus on the SQL topology itself. Don't fall in this trap and slap everything in the SQL product box all on the same box (as it were) - separate storage and config from application services as per the common model. Think of a "SharePoint Farm" as a superset of SharePoint Topologies and SQL Server Topologies.
Having said that there is no reason you cannot install Report Server on your existing WFEs in say a traditional medium SharePoint server farm and indeed this is often an entirely suitable deployment. However ensure you weigh the additional resource requirements against the cost/number of boxes argument and consider the more scalable, many WFEs option.
If you previously installed SQL 2005 Reporting Services, your current Reporting Services database will be in Native mode, so a new database is required. So you can either change the default, or more simply add a new one.
You lose a bunch of Native features, but gain the SharePoint coolness. Examples of the coolness include being able to use the SharePoint native list functionality with your reports. You know you want some. However, you can't have both Native and SharePoint together on the same instance.
On the SharePoint box...
There is an addin (with a ridiculously long official name) which I will call the RS Addin for SharePoint. You install this bad boy on your WSS or MOSS box(es) and it adds a new feature, which is managed via SharePoint Central Admin. You then configure the feature with the Report Server Web Service and Database locations, and some other reporting specific settings. Now there are new links and functionality in SharePoint Report Center, providing integrated report storage and rendering via your SharePoint site.
Does it go wrong?
Well the basic idea is to avoid IIS Web Site port conflicts, which was the most common problem prior to RTM and emphasized in the documentation. If you are doing a headbutt the keyboard install this is what will likely give you grief. Of course the way to completely nullify the issue is to implement Windows DNS in your organization.
I've also seen the Addin Install take a very long time on a Virtual machine - it thrashes during the last step with the progress bar at full. If you are in a hurry, disable your guest anti-virus before the install.
The skinny...
It's all deeply cool. The basic scenario is if you have a bunch of reports and are looking for the Web UI that should have been in Reporting Services in the first place, as opposed to the lame one that shipped, this is for you. Now this is not a complaint - it actually shows the benefit of waiting for SharePoint 2007 to provide the Web UI, because, well that's what SharePoint is for :)
Again, this feature set is a classic example of basing your enterprise platform on SharePoint, and indeed how that is now truly viable in a way that wasn't with SharePoint 2003. In many respects it offers guidance in terms of architecture and guidance when looking to consolidate the number of web applications with varying user experience (not to mention URLs) in your enterprise to a single delivery platform (SharePoint).
Here is a collection of resources for those interested in implementation:
I'm looking forward to the upcoming whitepaper, but hopefully this post will help if you are considering deployment and can't wait for it's publication.