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.
