Reporting
Services combines a mix of Microsoft technologies to create a scalable
server-based reporting architecture. You can view this as a middle-tier
server in your enterprise planning scenarios. It is one part Web server
and one part database server, with a dash of .NET managed code to bring
all the pieces together. Using Visual Studio as the base development
platform is somewhat challenging for a business user but very
comfortable for the seasoned developer. Business-savvy users are better
suited using the report model and Report Builder tools, which are also
built into the .NET Framework.
Internet Information Services
The
Web server portion of Reporting Services requires Microsoft Internet
Information Services (IIS). You will experience significantly improved
performance using IIS 6.0, which is included in Windows Server 2003.
You gain even more substantial improvements using the 64-bit version of
Windows Server and SQL Server. Configure the server to support ASP.NET
2.0. (This happens automatically during installation.) Two virtual
directories are created in the same application pool (Figure 1).
It
is a good practice to keep the Reporting Services directories in a
separate pool on IIS 6.0 so that they can be stopped and started
without affecting other Web sites on the host machine. One of the
virtual directories is the Report Manager application, which is written
using the same API and Web service capabilities available to custom
developers. The second Web site is the Web service interface, where all
the heavy lifting occurs. Encrypting report delivery with SSL is
optional but highly recommended if your reports have sensitive data.
Keep in mind that SSL has a slight performance impact.
Both
virtual directories point to folders under the Reporting Services
installation. Note that these live on the C drive by default. Some
enterprises have policies that require all applications to be installed
on drives other than the root. Reports are actually stored in a
database, so these folders do not grow much in size other than their
error logs.
You
can change the virtual directory names and target site by using the
Reporting Services Configuration tool. The default names are Reports
for Report Manager and ReportServer for Web services.
SQL Server Databases
Reporting
Services creates two SQL databases. One stores reports, report models,
shared data sources, resources, and metadata managed by the server. It
is called ReportServer by default but can be changed using the
Reporting Services Configuration tool. Use this technique to move
reporting from one server to another, repoint a test instance to
production, or have multiple servers share one database for a scale-out
cluster. The other database is called ReportServerTempDB; it stores
temporary tables and data snapshots. This database can grow
significantly with heavy use and should be closely monitored.
Note
that the table structure in these databases can change over time, so
writing directly to the tables is not recommended. Use the SOAP Web
services and rendering extensions instead.
Windows Services
A
single Windows service called SQL Server Reporting Services runs
unattended on the report server. It is responsible for managing,
scheduling, executing, rendering, delivering, and granting access to
reports. It should be set to run automatically at startup and restart
automatically on failure. The service can run under the local system
account or a domain-level account.
Subscriptions
rely on the SQL Server Agent scheduled jobs function. The reporting
service polls the metadata tables in the database periodically to
determine whether a report job is ready to run. The job is actually
entered into SQL Server Agent and set to run at a scheduled time. If
one of the services fails, subscriptions might not complete. Make sure
that both services are running if you depend on the subscription
feature.
All
the SQL Server services are monitored and managed through SQL Server
Configuration Manager. You can get to this through the SQL Server menus
or the Computer Management console (Figure 2).
Events and Logs
The
Windows application log contains information about general events. Look
for the Report Server, Report Manager, and Scheduling and Delivery
Processor event sources. The Performance log has counters to track
number of reports and other metrics. Use the RS Web Service counters to
monitor report server performance, and use the RS Windows Service
counters to track monitored operations and report delivery.
The
report server trace logs are text files stored in the Reporting
Services installation LogFiles folder. There are four types of files:
ReportServerService_<timestamp>.log traces worker threads.
ReportServerService_main_<timestamp>.log traces management threads.
ReportServerWebApp_<timestamp>.log details Report Manager tasks.
ReportServer_<timestamp>.log traces Web service calls.
You
can customize the tracing details in the report server configuration
files. These files are very detailed and not easy to read, but they do
contain the information needed to debug or troubleshoot execution and
security issues.
Numerous
files in the SQL database store information about report execution and
delivery. You can extract this information into a table format that is
easy to query and report. Use the supplied extract packages and sample
reports in the Report Samples.
Deployment Modes
The
Reporting Services architecture can support small workgroups or large
enterprise accounts. A single server can run the reporting engine and a
copy of SQL Server that houses the reporting databases. You can split
these over two servers by using a remote instance of SQL Server for the
database portion. You might do this for disk space, memory, or
processor usage reasons.
Combining
Reporting Services with Windows SharePoint Services or SharePoint
Portal Server is popular. If both exist on the same server, install
Reporting Services with the Files Only option. You must run the
SharePoint STSADM configuration utility to exclude the Reporting
Services virtual directories. This prevents security and ISAPI filter
issues. Use the Reporting Services Configuration tool to complete the
settings for the report services and databases. Make sure the
SharePoint and Reporting Services virtual directories run in different
application pools on IIS 6.0 or later.
SQL
Server Enterprise Edition and Developer Edition support a scale-out
deployment model. Multiple report servers rely on a shared database
instance on a local or remote SQL Server. For optimum redundancy, run
the SQL instance on a Windows Server failover cluster.
Internet
facing scenarios are also supported with some custom configuration.
Installing the report server and Report Manager outside the firewall
allows full use of reporting features. There is no built-in forms-based
authentication, so you must develop a custom security processing
extension. A more secure approach is to have Report Manager live
outside the firewall but keep Reporting Services inside the firewall.
This mode does not support report drillthrough, e-mail subscription
links, or Report Builder.
Note
that an SQL Server license is required for all computers running
Reporting Services. If the database, services, and Report Manager are
split over three machines, three licenses are required.
Extensibility
Reporting
Services is designed for extensibility. You can use the .NET Framework
to build fully trusted managed code assemblies that provide delivery,
data processing, rendering, and security capabilities. Report Service
is typically managed by using the full-featured SOAP-based Web service
interface. For even more detail, consider using the WMI provider.
Delivery Extensions
Reporting
Services includes delivery extensions for SMTP e-mail and network file
shares. These are closely tied to subscriptions. When a user subscribes
to a report or runs a data-driven subscription, notifications are
generated and a delivery extension is called with the parameters set in
the subscription. The delivery extension is responsible for rendering
reports and
sending the data through some communications channel. The rendering
logic and delivery mechanism are completely controlled by the extension
developer. An XML configuration file defines the extension components
on the report server and Report Manager. Your .NET assemblies are
installed in the reportserver\bin folder.
Data Processing Extensions
Reporting
Services data processing extensions retrieve data from a connected
source. The built-in extensions connect to SQL Server, OLE DB, ODBC,
and Oracle. This covers a large percentage of the available data
sources in any organization. Some custom applications might require
data sources not handled by OLE DB and ODBC. These might include
scientific or industrial devices, older computing systems, or
proprietary technology. There are well-defined interfaces that must be
coded in a managed code assembly. The required classes include connection, command, and datareader.
The latter fills a dataset that is consumed by the rendering engine.
The XML configuration file is deployed to the report server and Report
Designer in BI Development Studio or Visual Studio.
Rendering Extensions
Reporting
Services rendering extensions create device-specific layout information
from a report definition. The built-in extensions include various HTML
flavors, Excel, PDF, TIFF, CSV, and XML, as described earlier. Consider
using variations of these built-in extensions or XSLT transforms to
create proprietary output. If your needs are more specific, you can use
a well-known set of interfaces called the Rendering Object Model, which
provides a collection of classes to an extension developer. Managed
code iterates through these classes to produce an output stream
suitable for the target device. Deploy the completed assembly to the
reportserver/bin folder and create an entry in the XML configuration
files.
Security Extensions
Reporting
Services security extensions authenticate and authorize users and
groups for report administration and delivery. The built-in extensions
work with basic Windows security providers such as Active Directory.
Consider writing a managed code extension for forms-based external Web
site access or integration with existing security providers. Implement
the LogonUser and CheckAccess methods based on the well-known interfaces.
WMI Provider
The
Windows Management Instrumentation (WMI) interfaces are the bedrock of
the Microsoft Dynamic Systems Initiative suite of products that
includes Operations Manager and Systems Management Server. Reporting
Services supports numerous WMI classes that allow discovery, control,
activation, and monitoring of Reporting Server instances. These classes
are defined in the root\Microsoft\SqlServer\ReportingServices\v8 namespace.
Report Integration
Reporting
Services is a powerful platform for report creation, delivery, and
management. As this technology matures, numerous ISVs are integrating
reports into their custom products. The SOAP-based Web service, open
RDL and RDS file formats, extension classes, and WMI support make a
wonderful foundation for the skilled developer.
Microsoft
is a leading implementer in this field. It offers reporting links to
many of its popular application products. Among the more popular ones
are the Web parts designed for the SharePoint portal suite. They make
customized Web-based report delivery a snap. Other prominent products
that use the full Reporting Services feature set include Microsoft
Operations Manager (MOM), Data Protection Manager, and some Dynamics
offerings. A new Systems Center Reporting release combines Analysis
Services cubes with Reporting Services to create a comprehensive suite
of infrastructure management reports. It marries the log and metric
data from MOM with the inventory and deployment details of Systems
Management Server. This is an excellent example of the evolutionary
integration work done by Microsoft product developers.
