Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting Services Part I: Overview, Architecture and Installation

By James Luetkehoelter, (first published: 2004/03/04)

So it's here. Finally. For those of you who haven't had a chance to work with the beta, it's probably a good idea to download the evaluation version and start.  This add-on should be widely adopted in a relatively short time. It doesn't matter if you're primarily a developer or primarily a DBA -- there's a great deal to know in both arenas.

In this article we're going to discuss Reporting Services in general, its architecture and how it's installed. We'll be focusing more on *gotchas* and related information rather than duplicating BOL documentation. Later articles will discuss report design, reporting extensibility, automating administration, and enterprise-level design and deployment.

Overview

Unless you're living under the proverbial SQL Server rock, you've heard of SQL Server Reporting Services.  You probably already know that it is an add-on to SQL Server that allows you to create and distribute reports.  You may not be aware of some of the more useful features:

  • You can create role-based security to allow users access to specific reports.

  • Users can "subscribe" to reports without the intervention of an administrator.

  • You can schedule off-line execution of reports to minimize processing time.

  • You can embed Reporting Services functionality within other applications.

  • You can maintain a history of executed reports (useful when existing data is changed frequently).

Reporting Services (RS) is often described as a "free" add-on to SQL Server. It isn't free. It follows the existing SQL Server licensing structure. If you're using CALs, you need a SQL Server CAL to use reporting services. If you want to provide anonymous access to reports (say, from a public website), you'll need to have processor licensing.  If you want to deploy RS to a web-farm, you need SQL Server Enterprise edition licenses. In a number of scenarios, effective use of the product will require additional SQL Server licensing.  

An additional cost could be Visual Studio 2003. The Report Designer provided by RS only works within VS 2003. Now you can design the reports by writing the XML-based report definition file manually.  I'm sure some of you read that and say "Yay!" --  I say "Ugh". You can also write your own designer.  I still say "Ugh".  I would imagine that most reporting vendors will support Microsoft's report format in the near future. The exception might be Crystal Reports; it doesn't appear that version 10 will have an export to Microsoft's format. This makes sense for them since they have a competing report distribution product (Crystal Enterprise). I don't have Crystal 10 yet, so I can't say for certain. I also know of no existing utilities to convert Crystal to RS reports. I'm sure someone will do this, but there should be licensing considerations for Crystal. 

Another thing to note is RS's role in business intelligence. At the moment, working with Analysis Services requires writing MDX queries and provides only basic drill-down functionality. I attended an early partner-focused training session, and this was a particular point of disappointment for many of the attendees. Their opinion was that business intelligence *is* multi-dimensional analysis, so labeling RS as a business intelligence tool is misleading. I disagree. Any method of representing information that helps a company understand itself should be considered business intelligence. This includes static, relational database reporting. Not every data consumer needs to slice and dice.

Architecture

Reporting Services is comprised of a number of technologies and environments.  So what exactly does that entail? I see it as four major logical components:

A report development environment -- A standard report definition language is defined, and a report designer is made accessible through Visual Studio 2003.

A centralized report repository -- A SQL Server database is created to house not only the report definitions but also snapshots of executed reports.

A report processing and distribution system -- A Windows service manages rendering reports, scheduling execution, and distributing via email or file share.

A webservice-based management and report viewing tool -- An ASP.NET application provides an interface for scheduling, defining security, managing connectivity, etc. 

I'm making a point of separating these components to stress that the entire RS package spans many areas of IT. In a production environment it would be crucial to involve not only developers, but DBAs, systems staff, network staff, and email administrators. These four logical components are implemented by:

  • Report Definition Language -- This is the report itself. Microsoft has provided an open, documented XML format for report definition (.RDL). As I mentioned before, this means you can either write reports by hand, or build your own interface to do so.

  • Report Designer -- Right now, this is Visual Studio 2003. Two new project types appear under "Business Intelligence" when starting a new project in VS 2003 -- Report Project and Report Project Wizard. The interface for building reports is very Access-like; you can actually upgrade Access reports to RS .RDL formats. The designer also lets you easily deploy projects or individual reports.

  • Report Manager/Viewer -- "Report Manager" is an ASP.NET application that comes with RS. It's a solid tool that not only lets you manage security, scheduling, etc., but also acts as a report viewing interface.

  • Web Service -- Accessing the core RS functions is done by way of web services. This provides an easy way to extend RS functionality into other applications. They expose the real "guts" of the engine, the Report Service.

  • ReportServer Service -- This Windows service is the real engine behind everything. It handles everything -- report rendering, data retrieval, etc. 

  • ReportServer database -- This SQL Server database holds all of the report definitions, schedules, subscriptions, etc. It also holds snapshots of reports, which are stored in an intermediate, unrendered format. This keeps the overall size of the database down to a minimum. I'm in the middle of running stress testing, so at this point I can't give any sizing guidelines. I'll discuss this in detail in a later article.

  • ReportServerTempDB database -- This database has only two tables, "ChunkData" and "ExecutionCache". It's primarily used for rendered report caching purposes. Again, I have no sizing or management guildlines yet, but they will be in a later article.

  • Reporting Data Sources -- Of course, there has to be some data source to report on. This can be any OLE DB datasource. Since there is an OLE DB driver for ODBC, this means practically anything.

  • SQL Agent -- Subscriptions are set up as SQL Agent jobs so you obviously need to have this running. 

  • IIS server with the .NET framework installed -- Report Manager is an ASP.NET application so it needs an environment to run in.

  • SMTP -- If you plan on distributing reports via email, you need to have an SMTP server available. Currently there's no other email option than SMTP (Dear MAPI: Your services are no longer required).  One thing to be aware of is that the SMTP server must be used on the local network.

  • File Share -- If you plan on distributing reports by writing them to a central file location, you'll need to have a share setup with the appropriate permissions defined.

After writing out this list, it really struck me how complex the product is. There are numerous technologies involved:  IIS, ASP.NET, Windows Services, Web Services, SQL Server, etc.  Given that complexity, it's amazing how easy the product is to work with after you it is properly installed.  

The basic workflow is as follows:

1) A report definition (.RDL file) is created by VS 2003.

2) This report is stored in the ReportServer database by the ReportServer service, which is then exposed by the ReportingServices webservice. 

3) When executed, the ReportServer service retrieves the necessary information from the specified databases and creates an intermediate report.

4) This intermediate report is stored either in the ReportServer or ReportServerTempDB, depending on whether it is executed by means of a subscription or on demand.

5) The ReportServer service then renders a formatted report using the intermediate definition, presenting it to the Report Manager application, sending it contained in an email via SMTP, or writing it to a pre-defined file share.

 


Looking at the workflow diagram, the critical piece that I see are the number of access points to be secured. The Windows Report Server needs security rights to the operating system. It also needs SQL Server security defined to connect to the ReportServer database. The ReportServer database stores security information for source data connectivity. If you're pushing reports to a file share, that share should be configured with the appropriate NTFS and share permissions.

Installation

The installation process we'll be examining follows the developer edition. The evaluation edition available on Microsoft's website does have a few differences between the developer and enterprise editions, primarily in deployment.   There is also a command-line syntax for installing RS, as well as the ability to create unattended install scripts.  For the purposes of this article, we are going to focus solely on the installation wizard.

If you select all of the components, the following items will be installed:

  • The ReportService Windows service

  • The ReportServer and ReportServerTempDB database

  • A new database role called RSExecRole within the ReportServer database

  • The AdventureWorks sample database

  • A ReportService and Report virtual directory on an IIS server

  • A ReportServer webservice is registered

  • New project types are created within Visual Studio 2003

  • An ASP.NET Report Manager application

  • Command-line administration tools (under %SQLroot%/80/Binn by default)

  • Application coding samples (under %SQLroot%/MSSQL/ReportingServices by default)

The installation has a number of *gotchas* that can be extremely frustrating. The single biggest piece of advice I can give is: read the documentation. I tend to be the kind of person that dives into a new product without studying all of the accompanying documentation.  When the production version was released, I wasted significant time troubleshooting installation issues that were clearly identified in the readme. I'm not going to simply rehash all of the documented prerequisites and issues, but rather focus on the major items:

  • The software platform requires SQL 2000 SP3, Windows 2000 SP4, Windows XP SP1 or Windows 2003 server.  MDAC must be at 2.6 or higher. The 1.1 .NET framework is required, as is  ASP.NET version 1.1.4322.
  • If you aren't already running the Distributed Transaction Coordinator (DTC), it will need to be started and should be set to start automatically.  You can do this either through Enterprise Manager (Server Properties|General) or under Services under Administration Tools.  
  • If you're a DBA and haven't configured a IIS server for ASP.NET, you may need to run the ASP.NET IIS registration tool (ASPNET_REGIIS.EXE). There are a number of command-line options for that utility, but for the most part you can just run "aspnet_regiis -i". Visit http://msdn.microsoft.com/library/en-us/cptools/html/cpgrfASPNETIISRegistrationToolAspnet_regiisexe.asp?frame=true for more information.
  • The Reporting Services BOL suggests that a minimum of 256 RAM be available to the Windows ReportServer service; that's an awfully high minimum requirement for a single service, approaching a service like SQL Server or Exchange. I haven't done any real stress testing, but it does look as if the ReportService could have a heavy memory load. Just starting the service consumes around 35-40MB of memory on my test server.  In a real deployment, you'll probably want to have the ReportServer service on a dedicated machine.
  • Installing Reporting Services on a domain controller requires manual configuration.  See the installation readme.htm for details.  This scenario should only occur in a single-server, small office environment.
  • Be aware of the difference between NT Authority/System and Local Service.  Since NT Authority/System is often referred to as the local system account, I sometimes mistake it with Local Service, which is only available under XP or 2003 Server.

Before you begin the installation, verify the following:

  • The IIS server is available.
  • The Distributed Transaction Coordinator service is started.
  • The proper version of .NET framework is installed on that web server.
  • The proper version of ASP.NET is enabled on that web server.
  • No ReportServer or ReportServerTempDb database exists.
  • No ReportServer or Reports virtual directory exists.

The actual installation process has some screens that are less than intuitive:

One of the first steps in the installation process is to check to see if you have the necessary software requirements.  What frustrates me with this screen is that *if* you're missing a component, you can't easily go back to re-run the system check. No "Back" button is supplied.  This is why I can't stress enough the need to have everything needed in place before starting the install.




Another item that confused me briefly was the missing directory location for some of the installation components.  After going through the install, it was clear why there was no installation path available;  the location of those items are determined later during the install.  I still would like some sort of message here.

 

Next we're asked to setup the ReportServer Windows service.  It is installed locally on the machine that runs the installation.  By default, it assumes that you will provide a domain user account;  however, BOL recommends using a built-in account.  If you aren't installing on Windows Server 2003, NT Authority\SYSTEM is really your only choice (2003 allows you to use a Local Service or Network Service account).  The Report Server Web service will default to running under the local ASPNET user account.

 

 

When selecting virtual directories, it defaulted to these names during my installation.  As I mentioned before, I had previously installed beta 2, and uninstalling did not remove the existing virtual directories.  The installation will not automatically overwrite or use these directories, so you'll need to drop them from the IIS management tool.  Chances are you aren't going to want to select the "Redirect the default Web site..." option unless the IIS server is going to be dedicated to RS, and you want to make it as easy as possible for your end-users to reach the Report Manager site.

 

 

This screen asks for the location of the ReportServer database and the credentials that the Report Service (the Windows service) will use to connect to that database.  Again, if the database already exists, the install will *not* overwrite it on a local SQL Server instance.  It does say that if the database is on a remote SQL Server, it will be used. I have yet to verify this.  More info on distributed deployments will be included in a later article.  In addition to the ReportServer databases, the ReportServerTempDB database will be created.

 

This screen is asking where to set up the *sample* database.  In case you didn't already know, Microsoft is moving away from Pubs and Northwind as sample databases.  The new database is called AdventureWorks and will also be used in Yukon.

 

Remember, this licensing scheme should be exactly the same as the licensing scheme for your existing SQL Servers.  Unless you have less than 25 users, processor licensing is generally the most cost-effective and manageable method; it allows for unlimited and anonymous connections to the database (no need to count up CALs!).  

So  my installation was nearly finished when I got this message.  I was starting the ReportService under the LocalSystem account and I was logged in with administrator privileges while I was installing.  There was no way that I didn't have privileges to start the service.  The wonderful thing is when you hit Cancel, it roles back the entire installation!  Well, that isn't quite true.  It didn't drop the SQL databases, or remove the IIS virtual directory;  they needed to be cleaned out by hand.

There is a hotfix referenced in the readme that sounded like it might be the source of my problem.  I received the same results after applying it.  And again, the installation was rolled back.  After numerous attempts, a serious amount of caffeine, and a few obscenities, I discovered that the original beta 2 Reporting Services application files were still there, even though I uninstalled the beta before attempting the production install.  After deleting those, the installation completed without a hitch.  

Now you're ready to explore Reporting Services. It's finally here, and it's definitely here to stay. It doesn't matter how you work with SQL Server; you need to get to know it -- well. Stay tuned for Reporting Services Part II: Report Design.

Links

Reporting Services Website -- http://www.microsoft.com/sql/reporting/default.asp

Trial software -- http://www.microsoft.com/sql/reporting/productinfo/trial.asp

Licensing info -- http://www.microsoft.com/sql/reporting/howtobuy/faq.asp

ASP.NET registration tool -- http://msdn.microsoft.com/library/en-us/cptools/html/cpgrfASPNETIISRegistrationToolAspnet_regiisexe.asp?frame=true

Total article views: 36149 | Views in the last 30 days: 12
 
Related Articles
FORUM

Reporting Services Installation Problem - help requested

SQL Server 2005 Reporting Services installation help

FORUM

Install Reporting Services after Engine

Installing reporting services after installing sql

FORUM

Installing reporting services on SQL server default instance

Installing reporting services on SQL server default instance

ARTICLE

A Guide to Installing SQL Server 2008 R2 Reporting Services

This document shows how to install a Microsoft SQL Server 2008 R2 Reporting Services instance.

FORUM

Reporting Services installation problem

Reporting Services installation problem - Help me please!

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones