Installing Reporting Services on SQL Server 2005 Express Edition | Database Journal
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
databases (ReportServer and 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. (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. (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 Template subfolder (residing by default under
DataProgram FilesMicrosoft SQL ServerMSSQL.xMSSQL
folder, where 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 EXEC sp_configure), connecting individually to all
‘user instances enabled’,0
active instances (after identifying values of the instance_pipe_name column of the sys.dm_os_child_instances dynamic
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
invoking EXEC sp_configure ‘user instances. In addition, if you are planning on upgrading an existing installation running on Vista, refer to the
enabled’, 1Upgrade 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
newly downloaded SQLEXPR_ADV.EXE
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
will use SQLEXPRESS named
instance). This decision determines the names and placement of the reporting
databases, as well as the naming convention applied when creating different
reporting components.
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 ReportServer$instance_name and ReportServer$instance_nameTempDB,
virtual directories for Report Server (http://computer_name/ReportServer$instance_name)
and Report Manager (http://computer_name/Reports$instance_name),
as well as Windows (ReportServer$instance_name)
and Web Service identities (where instance_name
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 Details...
command button, you will be informed that "The).
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.
»
See All Articles by Columnist Marcin Policht
