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