SQL Server Reporting Services–if it’s important look after it

Before I can commit funds to a new project be it at work or at home I need to know I can afford it; specifically is there enough money in the budget and to find that out I need to run a report.  This is what’s meant by operational reporting and this distinction means that this report can be considered to be mission critical.  Whole rafts or reports could be considered to be mission critical and this begs the questions how do I make them highly available if they are so important.

The answer depends on how you are using reporting services and which version you have:

SQL Server 20005⁽¹⁾, 2008, 2008 R2 Reporting Services in standalone mode

In this type of deployment there are two moving parts, the web service and the databases and both can be configured to make reporting services highly available.The databases can be clustered but as there two of them:

• The metadata database (typically called ReportServer) that stores the reports, security, schedules etc..
• There is also a special temporary database called ReportServerTempDB for calculations and snapshotted reports.

mirroring won’t work properly, so clustering is your best option here.  The web service of SQL Server can then be network load balanced (aka scale out deployment) where multiple reporting services servers reference the same databases, giving you redundancy if one fails.  The complete how to on this is on TechNet, but note there is nothing actually in Reporting Services that does the Load balancing bit itself.

SQL Server Denali in standalone mode

As above but you cold use the new AlwaysOn feature to protect the databases in an availability Group.  This uses clustering services but not shared storage, to continuously replicate the databases multiple locations and keep them in synch.  I have a short post on AlwaysOn here and the definitive word is on TechNet

SQL Server 2008, 2008 R2 Reporting Services in SharePoint mode

Here there is an extra layer , SharePoint, but otherwise things remain the same, there’s still the databases to cluster and the reporting services servers can be load balanced.  However there is a raft of options to increase scalability and availability in Reporting Services Books Online:

 

Note that the reporting services add-in referred to here provides the report viewer web parts and integration components to make reports behave like any other document in SharePoint.

SQL Server Denali Reporting Services in SharePoint mode.  

What is different here is that Reporting Services is now just like any other application web services in SharePoint. so the  The difference is the way your configuration is done – it’s all done through SharePoint administration apart from selecting the installation options when you actually install SQL Server, as per my last post. 

 

So a quick 101 run through of your high availability options for reporting services, and just to be clear there is nothing in reporting services itself to support high availability (clustering or network load balancing), but the service can be configure to use these features.

(1) SQL Server 2005 is now in extended support, so unless you are paying for that service you aren’t really in a highly available environment as support is part of the equation

Comments

• Anonymous
  August 31, 2011
  Have you seen Project Crescent?

• Anonymous
  September 01, 2011
  Ed love it, but there's alot of buzz about it , so I am concentrating on the less well known stuff here Andrew

• Anonymous
  March 24, 2012
  The databases can be clustered but as there two of them: ... mirroring won’t work properly, so clustering is your best option here. I'm pretty new to the whole MS SQL environment - my background is in Oracle - but I think I'm getting to grips with it ... then I come across a statement like this ... which leaves me wondering if I've grasped it or not???  What does this mean? Why will mirroring not work properly for two (or presumably, more) databases?   Sorry to be a pain - and maybe in the wrong place here - but could you please explain this a little better? Cheers ... Bill