SharePoint & SQL Reporting Services

image Today’s blog post is a guest post by Jonathan Stuckey, Solution Specialist for Office and SharePoint at Microsoft New Zealand.

I've recently seen a number of designs and solutions for SharePoint being deployed, where organisation is looking to take advantage of SQL Server Reporting Services capability to deliver rich looking reports via SharePoint's portal access.

Given that there are several options with generating and deploying reporting solutions via SP, I thought it would be good to cover the impact of some designs & licensing implications.

A. Using SSRS in SP Integrated mode

Note: in order to be able to use SSRS SP integration, the reporting seclip_image001rvices FE needs to be installed on same system as SP binaries.

There are 2 ways in which you can architect and deploy this solution

1. SSRS on a SQL Farm (not on same host as DB as this is not recommended SQL best practise)

Remember that need to install SP binaries on the same server as SSRS in order to get integrated administration UI and Id/Access experience based on SP. See Diagram 1

REMEMBER: In this case you need to purchase a SharePoint Svr license for the instance that SSRS is installed on as SharePoint’s EULA states that need to have a server license for each instance of SharePoint binaries installed. This includes when deploying SP binaries for SP integration requirements only.

For specific definitions of what constitute “installed instance” etc, see the current PUR and EULA for details.

2. SSRS installed on SharePoint Farm

Doing this you are separating out the SSRS front-end from SQL environment.

This allows you to co-exist the SSRS services on an existing MOSS/SP installation therefore reducing the requirement for an additional server license.

REMEMBER: SSRS, as functionality supplied from SQL Server, falls under SQL Server's product's use-rights (PUR). This stipulates that SSRS requires a SQL Server license when run separately to the SQL installation.

Jono 2 Therefore in this design scenario, Diagram 2, you will need to make sure that you have licensed SSRS correctly i.e. you have the correct SQL Svr licensing for solution.

B. Using SSRS as a reporting engine in standalone (not using SP integration)

The assumption in providing reports this way is that you are reporting on things which are not natively part of SharePoint, but making reports available via SP as a central portal and access.

With this design, SSRS will retain it’s own administration and configuration interfaces, and to

access the reports you are in effect “punching through” to the SSRS web front-end from inside a SharePoint page, or web-part.

In this case you are effectively deploying SSRS independently of SharePoint farm, there are no interdependencies on SharePoint binaries, and there no implications on Server licensing. You would still be prudent to review the SQL Server & Reporting Services best practise guidance and licensing

Importantly though this design approach requires technical considerations on navigation to / from SharePoint and the SSRS content. You need to include solution and web-design aspects, as it is possible to be “orphaned” on SSRS FE server page, without direct navigation back to SharePoint if you don’t specifically design this in to solution.

You can email Jonathan at jonstuck AT microsoft.com.

Posted by darryl on April 4/20/2010, 2010  •  Comments  •   • 

Cloud Solution Models

There are many facets to the approach of cloud computing.  One is the various and variations of business models that are available, another is the way different companies are approaching the cloud and how they are offering different services.

There are two primary approaches to offering cloud based solutions.

SAAS

The best known of these is Software as a Service (SaaS). The concept of SaaS is that a company (provider) offers a software product that is consumed and paid for as a service.  Rather than downloading and installing software on computers, a customer would simply open their web browser, type an address and start using the software.

There are benefits of this approach.  Obviously, the application is available on any device that has a web browser.  This means that there is no software to be written for Windows, Mac and other devices – write it once and it runs anywhere.

There are also trade offs with this approach.  These include latency (the time it takes for the computer to talk to the applications over the internet), disconnections (e.g. on a plane) and richness.  Richness can be managed by using technology such as Flash or even Silverlight.  However, not all browsers support these technologies (and I’m not just referring to certain tablets), so when these technologies are relied on, it can break the primary benefit of the SaaS approach.

Who is using this approach today?  Many companies use this approach today including Xero and Google.  In fact, the SaaS model seems to be driven largely by Google – which is hardly surprising given their strategy.

Software + Services

The other approach has been labelled Software+Services (or S+S).  Software+Services emphasises the user experience and offline connectivity over portability. That is that this approach has a client solution that uses web services of some description (be it ReST, SOAP or something similar) to connect to a server on the internet which provides a service.  In some cases, there may be a SaaS solution that fills the gaps in portability but the main solution is based on the client applications.

Benefits of this approach are that there is no problem with richness as information can be cached locally and manipulated by the user on their device. The client application generally has offline capability and is not sensitive to latency as SaaS solutions tend to be.

The disadvantage of this approach is that the client needs to be written for each platform being targeted.  This may mean writing an application for Windows, Mac, Linux or the various i-something devices. Where this becomes a real problem is when an update is made, it needs to be rolled out to each of the applications in turn.  This can make the development process cumbersome.

Who is using this approach today? Microsoft and Apple both tend to use this approach – which makes sense given both companies have a client business to build upon.

Hybrid

Increasingly today, solutions start as a SaaS model but then see the advantage of having the rich clients, but don’t want to maintain them.  What to do?  The hybrid solution provides a middle ground between these two solutions by building a SaaS solution and then building in an API. The beauty of this is that if a customer wants a client application badly enough, they can build one themselves.

The benefits of building an API are several: Firstly with critical mass (note that critical mass must come first) this can enable an ecosystem around your SaaS model, which means that people can build an applications and possibly sell it and make money for themselves or give it away to build their own reputation (and when they are smart, their own business).  When this happens, your customers get the best of both worlds – they get your great SaaS application and the client apps for richness, but you don’t have to maintain it.  Alternatively, if you want to build some limited applications, you are also free to do this.

The disadvantages associated with this is that you don’t control the user experience from end to end.  This has a number of flow on effects.  For example, your company logo/brand is likely to not be used in client applications (or perhaps it will).  Another example is support.  Customers may get used to a client app’s way of doing things and the developer may stop producing that application, which may cause end user confusion.  Often customers don’t understand what services you do and don’t provide, so they may email your support facilities about a problem which is really with the client software they are using.

Who is using this approach today? Flickr, Twitter and a host of others use this approach extensively.  Other companies use it to varying degrees – e.g. Xero has an API as does Toodledo and others, but in some cases finding applications is very hard – and in most cases is dependant on the target audience for that solution as well as critical mass.

What does the future look like?

At the end of the day, when we look at the current range of applications most used by end users, some of them work well as a client based solution and won’t go into the cloud very easily, others will disappear into a SaaS model very easily. There are applications that might need to change before it can make the leap to the cloud (ala Xero taking a direct connection to your bank account), but in some cases this might not happen easily or seamlessly. Some applications need device capabilities (e.g. GPS, scanners, etc) which will likely to necessitate a client approach.  In saying this, there is no one model that will suit all solutions in all cases, however, we will, I believe see some trends particularly with existing application models.

Personally, I expect that the S+S and hybrid models will be generally more popular for the next few years. I see the S+S model potentially strengthening further over time as compelling devices and client delivery methods for accessing the cloud become more commonly accepted among users and drive the S+S model rather than the SaaS or even the Hybrid model.  Watch this space.

Posted by darryl on April 4/7/2010, 2010  •  Comments  •   •