Having completed our fairly extensive coverage of the Service Broker characteristics specific to SQL Server 2005 Express Edition, we now turn our attention to Reporting Services, which, while not included in the core product, can be easily added by installing a supplemental download. While the resulting functionality is somewhat limited (when compared with equivalent components incorporated into full-featured editions of SQL Server), there are significant benefits you can realize by taking advantage of extra capabilities (especially considering that there are no software or licensing costs involved). In this article, we will present the initial setup process, leaving discussion of configuration, management, security, and usability until future installments of this series.
The main purpose of Reporting Services implementation available in SQL Server 2005 Express Edition is to provide end users the ability to extract content of local databases in an easy to analyze and comprehensive manner, through a collection of predefined reports, rendered interactively as Web pages (in HTML format), Adobe Acrobat PDF files, or Excel spreadsheets. Accomplishing this goal is facilitated by a straightforward development environment (with wizard-driven and template-based projects) that leverages the familiar Visual Studio interface. The primary drawback of the version of Reporting Services we will be discussing here is the lack of more advanced features present in its full-fledged counterparts, such as support for remote data sources (in addition to access to data derived via Analysis Services or Integration Services), ad-hoc and scheduled report generation, the ability to produce additional rendering formats (XML, CSV, or TIFF), SharePoint integration, or the availability of custom authentication mechanism.
From an architectural perspective, Reporting Services consist of several
components, which in the case of SQL Server 2005 Express Edition have to reside
on the same computer (this restriction does not apply to other editions). At
their foundation, there is the Report Server, operating as an independent
Windows service, responsible for report processing and rendering, as well as
overall security. Data, from which reports are derived, resides in local
ReportServerTempDB) designated for this
purpose. Access to reports as well as a number of administrative tasks is
handled by leveraging Report Manager, running as an ASP.NET Web application.
Finally, there are also several management and development tools, including
Windows Management Instrumentation (WMI) based Reporting Services Configuration
utility and Business Intelligence Development Studio with its Report Designer,
which we will discuss in more detail later. (More sophisticated utilities, such
as Model Designer or Report Client Builder, which deliver ad-hoc reporting capabilities,
are available only in full-featured editions of SQL Server 2005).
In order to take advantage of the reporting functionality we just briefly described, you will first need to satisfy all software-related prerequisites. In particular, since the underlying components rely on ASP.NET technology, both .NET Framework 2.0 (which you should be present on your computer already if you are running SQL Server 2005 Express Edition) and Internet Information Services (IIS) 5.0 or later have to be locally installed. The former is available in both x86 and x64 versions from the Microsoft Download Center. The setup process of the latter is rather straightforward. In the case of Windows XP Professional, this is typically done via the Add/Remove Windows Components section of Add and Remove Programs applet in the Control Panel, which initiates the Windows Components Wizard, allowing you to select Internet Information Services (IIS) entry along with its World Wide Web Service. (Note that Windows XP Home Edition cannot be used in this case, since it lacks IIS support). A similar approach can be used in the case of Windows Server 2003 and its predecessors (Windows 2000 Server and Workstation). An equivalent task on a Windows Vista computer is a bit more challenging, but fortunately well documented (for step-by-step instructions, refer to the Knowledge Base article 934164). If you intend to install Reporting Services on a computer running Windows x64 operating system, you should follow procedures described in the Knowledge Base 934162 and SQL Server 2005 Books Online How to: Install 32-bit Reporting Services on a 64-bit Computer articles (note that Reporting Services for SQL Server 2005 Express Edition are available only in 32-bit version).
Once these preliminary steps have been completed, you will need to obtain
the SQL Server 2005 Express Edition with Advanced Services source files,
published on the Microsoft Download Center as a self-extracting executable
SQLEXPR_ADV.EXE, in addition to the
SQL Server 2005 Express Edition and SQL Server Management Studio Express,
includes the Full Text search and Reporting Services components) as well as SQL
Server 2005 Express Edition Toolkit also available from the Microsoft Download
Center in the form of
SQLEXPR_TOOLKIT.EXE contains the Business
Intelligence Development Studio component, which facilitates report designing
and editing functionality via Reporting Services-based projects and templates).
When dealing with an existing SQL Server 2005 Express Edition installation,
before you start the setup process, you should also make sure that there are no
active User Instances (for more information about this feature, refer to an
earlier article of this series). Since active User Instances place locks on
database files residing in the
Data subfolder (residing by default under
Program Files\Microsoft SQL Server\MSSQL.x\MSSQL
x is an
integer assigned sequentially to each new SQL Server 2005 instance), they
prevent changes that need to be applied during the upgrade procedure. This can
be accomplished using the procedure described in the Microsoft SQL Server 2005
Express Edition with Advanced Services Readme
file, which involves temporarily disabling the User Instances configuration
option (by executing
user instances enabled,0), connecting individually to all
active instances (after identifying values of the
instance_pipe_name column of the
management view), and shutting them down (with
SHUTDOWN WITH NOWAIT T-SQL statement). Keep in mind that
you will need to re-enable the User Instances feature following the upgrade by
EXEC sp_configure user instances
enabled, 1. In addition, if you are planning on upgrading an existing installation running on Vista, refer to the
Upgrade Tips for Applying SP2 After Upgrading to Windows Vista section of How to: Upgrade Reporting Services on Windows Vista article in the SQL Server 2005 Books Online.
At this point, you are ready to proceed with the installation. While logged
on with an account that is a member of local Administrators group, launch the
to initiate the Microsoft SQL Server 2005 Setup wizard. Assuming that you are
modifying an existing instance, you do not need to be concerned about
prerequisites (otherwise, refer to the
first article of this series) or System Configuration Check. Providing that
both of them complete successfully, you will next be prompted for registration
information. On the Feature Selection page of the wizard, you will find an
option to add Reporting Services, including Report Manager and Shared Tools.
The Instance Name page that follows prompts you to choose a target database
engine instance that Report Server will be associated with (in our case, we
instance). This decision determines the names and placement of the reporting
databases, as well as the naming convention applied when creating different
Once you reach the Service Account page, you will need to decide if Reporting Services will be running in the security context of one of the built-in System accounts (Network Service, Local System, or Local Service) or a designated domain user account, as well as whether you want to start the Reporting Services at the end of the setup. Local Service is typically recommended in the case of SQL Server 2005 Express Edition (we will discuss the reasoning behind this decision and its implications later in this series).
You will also be presented with the "Report Server Installation
Options" page, where you are expected to pick one of two available
settings. The first one allows you to "Install the default
configuration", which creates reporting databases named
virtual directories for Report Server (
and Report Manager (
as well as Windows (
and Web Service identities (where
is the name of the target SQL Server 2005 Express Edition instance). The
resulting SSL settings will depend on whether the computer has the appropriate
certificates already present in its personal store. The other option installs
the software but leaves its configuration to you, which you can perform
following the completion of the wizard (this is accomplished using Reporting
Services Configuration tool). In scenarios where you are re-running setup
against an existing SQL Server 2005 Express Edition instance, the Report Server
Installation Options page will have both options grayed out, with the latter
being enforced (if you click on
command button, you will be informed that
prerequisite check failed for a default report server installation").
After you click on Next, the content of Ready to Install page should confirm
that Reporting Services will be installed as the result of your earlier
selections. Keep in mind that (as the informational text states) you must
download and install the latest SQL Server 2005 Express Edition Service Packs
to update all newly installed components.
If you decided (and were able to) select default the configuration option on the Installation Options page, the setup should result in the creation of a fully functional Report Server. Otherwise, core components are installed and associated with the database engine instance you designated, but not configured yet. To deliver a working outcome, you will need to make additional modifications using Windows Management Instrumentation-based Reporting Services Configuration Tool (which shortcut appears in the Configuration Tools subfolder of Microsoft SQL Server 2005 program group menu). We will describe its interface and characteristics in the next article of this series.