Reporting Services Part I: Overview, Architecture and Installation

,

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

Rate

5 (3)

Share

Share

Rate

5 (3)