Installing Reporting Services on SQL Server 2005 Express Edition

April 27, 2009

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 Data subfolder (residing by default under Program Files\Microsoft SQL Server\MSSQL.x\MSSQL 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 ‘user instances enabled’,0), connecting individually to all 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 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 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