Microsoft SQL Server 2000 Reporting Services Deployment Guide
Author: John H. Miller
Microsoft Corporation
Published: December 2003
Summary: SQL Server Reporting Services extends the Microsoft Business Intelligence (BI) vision by providing a comprehensive, server-based reporting platform. Reporting Services combines the speed, scalability, and manageability of centrally managed reporting with the flexibility and 'on-demand' nature of desktop and Web-based applications. This deployment guide provides an overview of the solution, discusses installation and deployment issues, and outlines the basic operation of Reporting Services.
Copyright
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
2003 Microsoft Corporation. All rights reserved.
Microsoft, Windows, Active Directory, ActiveX, PivotTable, and Visual Studio are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
1
Table of Contents
Introduction
Overview
A New Reporting Paradigm
Key Features of Reporting Services
Report Authoring Features
Report Management Features
Report Delivery Features
Architecture and Components
Server Deployment Alternatives
Reporting Terminology
Pre-Deployment Considerations
Naming Conventions
System Requirements and Prerequisites
Server Requirements
Report Server Database
.NET Framework Dependency
Client Requirements
Report Designer Requirements
System Account Requirements
Installation Credentials
Accounts/Credentials for Ongoing Operations
Installation and Configuration
Running Setup
Setting the ReportServer Database Connection Using the rsconfig Command-Line Utility
Server Configuration Files
Configuring E-Mail Delivery
Launching Reporting Services Components
Report Authoring
Data Source Support
Publishing Reports
Report Server Folders
Using Scripts to Publish Reports
Using a Test Server
Viewing Reports
URL Addressability
Understanding Report Processing
Understanding Session State
User Context for Reports
Report Rendering
HTML Rendering
Other Rendering Extensions
Report Subscription and Delivery
Subscriptions
Personal Subscriptions
Data-Driven Subscriptions
Delivery Extensions
E-Mail Delivery
File Share Delivery
Security
Report Server Security Model
Using Default Security
Report Management Roles
Configuring Application Security in Reporting Services
Securing Reports for Global Access
Managing Reports
Managing Reports with Report Manager
Adding, Modifying, and Deleting Reports
Importing Reports from Microsoft Access
Managing Data Source References
Managing Data Source Credentials
Managing Report Execution and Processing
Report Execution Options
Running Cached Reports
Running Reports from Snapshots
Managing Schedules
Managing Report History
Managing Subscription and Delivery
Administering Reporting Services
Backup and Restore
Scripting
Service Configuration
Troubleshooting the Server
Conclusion
1
Introduction
Microsoft® SQL Server™ Reporting Services is a reporting platform that combines the speed, scalability, and manageability of centrally managed reporting with the flexibility and on-demand advantages of desktop and Web-based applications.
This document provides an overview of Reporting Services, discusses deployment issues, offers installation instructions, and outlines the basic operation of the product. It is meant to be sufficient to install, configure, and start working with Reporting Services.
However, this guide does not provide comprehensive information about Reporting Services. For detailed information on using the product as well as development interfaces, see Reporting Services Books Online. The product documentation is referenced throughout this guide.
This document assumes that you have some familiarity with the following topics:
- Microsoft SQL Server
- Internet Information Services (IIS)
- Web-based application access
- The .NET Framework
Information on these topics is available on the MSDN Web site ( and the SQL Server home page (
Overview
SQL Server Reporting Services extends the Microsoft Business Intelligence (BI) vision by making it easy to get the right information to the right people in virtually any business environment. With Reporting Services as an integrated part of the database environment, organizations can manage costs by using a single, comprehensive platform for BI.
Reporting Services is a comprehensive server-based platform for creating, managing, and delivering traditional, paper-oriented reports as well as interactive, Web-based reports. The product's modular design and extensive application programming interfaces (APIs) enable software developers and enterprises alike to integrate reporting with legacy systems or third-party applications.
Reporting Services is a component of SQL Server 2000 and includes:
- A complete set of tools for creating, managing, and viewing reports.
- An engine for hosting and processing reports.
- An extensible architecture and open interfaces for embedding reports or integrating the solution in diverse IT environments. Specifically, you can extend the product's native capabilities for data sources, rendering, or report delivery.
A New Reporting Paradigm
Reporting Services offers a unique combination of benefits:
- A complete platform for reporting. Reporting Services supports the full reporting lifecycle, from authoring to delivery and ongoing management of reports.
- Flexible and extensible reporting. Reporting Services creates both static and interactive reports in numerous formats. Open APIs and an extensible architecture support integration into many environments and solutions.
- Scalability. The product's server-based design scales easily to support high-volume environments. You can create a reporting server farm with multiple report servers serving thousands of clients.
- Integration with Microsoft products and tools. Reporting Services ships as a component of Microsoft SQL Server and integrates easily with familiar Microsoft tools, such as Office, without requiring programming and customization. Using a single, integrated platform controls costs and speeds deployment.
Because Reporting Services combines a single, complete reporting platform with a scalable and extensible architecture, it meets a wide variety of reporting needs. The following table lists examples of the needs customers might address with Reporting Services.
Application / DescriptionEnterprise reporting / Corporate IT staff can design a variety of reports for internal reporting and BI applications. Staff can receive interactive reports or access them on a corporate portal.
Embedded reports / ISVs and ASPs can use Reporting Services to deliver pre-defined reports as part of a packaged application. The customer's IT organization can run reports unmodified or customize them for specific needs.
Web-based reporting for partners/customers / Organizations can use traditional or Web-based reports to interact with customers or partners over extranets.
Key Features of Reporting Services
A complete reporting platform, Reporting Services supports the entire report lifecycle, from authoring through deployment.
Report Authoring Features
Reporting Services includes the tools you need to start creating traditional or interactive reports, including a graphical report designer tool with report design wizards. The following table describes many of these features.
Report authoring feature / DetailsWide range of supported data sources / Microsoft SQL Server
Oracle databases
Any ODBC or OLE DB-compliant data source
Open report authoring options / Report Designer (uses Visual Studio .NET)
XML-based Report Definition Language (RDL)
Third-party tools generating RDL
Flexible report designs / Freeform
Table
Matrix
Charts
Parameterized reports using run-time filtering
Drillthrough
Linked reports
Report Management Features
Reporting Services includes comprehensive functionality for managing reports in a wide variety of environments.
Reporting Services includes Report Manager, a Web-based tool for managing reports. You can also create interfaces with other tools using the Reporting Services Web Services API. The following table describes many of these features.
Report management feature / DetailsReport information and metadata / Name
Description
Data sources and connection information / Data source type
Connection string
Credentials
Parameters for parameterized reports (default values, prompts) / Manage prompts and defaults for parameterized reports
Execution properties / On-demand (live)
Multi-user shared cache
Snapshots (static)
Report scheduling / Integrated with SQL Server Agent
Report history / Archival copies of processed reports for review and auditing
All of these capabilities are supported by a flexible, role-based security model that protects reports and resources. Security can be tailored to meet a wide variety of needs; users can have access to run reports on demand with their own parameters, for example, or tailor individual report subscriptions. Administrators can delegate content management functions for different reports to specific individuals.
Report Delivery Features
You can post reports to a portal, e-mail them to users, or give users access to a Web-based application for retrieving reports. Navigation and search features help users locate and run the reports they need. Personalized subscriptions let them select the rendering formats and schedules they prefer. The following table describes many of these features.
Report delivery feature / DetailsRange of report rendering options / Web formats (HTML)
Print formats (PDF, TIFF)
Data (Excel, XML, CSV)
Others through open API
Flexible delivery options / Scheduled or event-driven delivery
Personal subscriptions
Delivery of rendered reports or links
Data-driven subscriptions
Architecture and Components
Reporting Services is designed with a modular, distributed architecture to ensure both scalability and flexibility.
Figure 1: Reporting Services architecture
The three main components to Reporting Services are an application layer, a server layer, and a data layer, corresponding to Report Manager, Report Server, and the ReportServer database, respectively.
Component / FeaturesReport Manager / A Web-based report access and management tool distributed with Reporting Services. This component is installed on a Web server.
Report Server / A Web service with multiple components that:
- Handles Web Services API and URL requests.
- Processes reports including executing queries, evaluating expressions, and generating output formats.
- Provides snapshot and report cache management.
- Supports and enforces security policies and authorization.
ReportServer database / A SQL Server database that stores the information used by Report Server, including report definitions, report metadata, cached reports, snapshots, and resources. It also stores security settings, account information, and scheduling and delivery data.
Figure 1 also shows a number of clients interacting with Report Server.
Client / DetailsReport Designer / Report Server includes this visual report authoring tool, integrated with Microsoft Visual Studio® .NET 2003.
Third-party tools / Third party management and authoring clients are supported through open interfaces and a Web Services API.
Data source processing, report rendering, and delivery are all implemented using modules resident in Report Server. These extensions use published APIs.
As a result, you can use the product without modification, or you can customize it to meet specific needs by:
- Creating applications to manage a report server by using a Web Services interface.
- Creating applications or usinga Web browserto manipulate report output through URLs.
- Creating additional rendering, delivery, and data processing extensions using the Microsoft .NET Framework.
Server Deployment Alternatives
Reporting Services components may run on a single server or on multiple servers, allowing for a variety of scalable, flexible implementations. Small businesses often can achieve acceptable report processing performance using a single server deployment, as depicted in Figure 2.
Figure 2: Typical deployment profile for small business
Medium to large business organizations can spread the report processing load across separate servers if they want to by taking a multiserver approach, as depicted in Figure 3.
Figure 3: Typical deployment profile for medium to large businesses
Enterprise business organizations with stringent scalability requirements can scale out their Report Server deployments using a "Webfarm" approach and also reduce the chance of service outages by incorporating clustering to protect their databases, as depicted in Figure 4.
Figure 4: Typical deployment profile for enterprise businesses
Reporting Terminology
Familiarity with the following terminology is helpful to understanding Reporting Services.
Term / DefinitionData source / An input source for reporting data, typically other databases or applications used by Report Server in the report definition to generate the report.
Linked report / A report that derives its definition through a link to another report. You can use a linked report to keep a version of the report that retains your settings and input values.
Namespace or folder hierarchy / A bounded namespace that uniquely identifies all reports, folders, data source references, and resources stored and managed by a report server.
Parameterized report / A published report that accepts input values through parameters.
Report definition / The XML blueprint for a report containing information about the query and layout.
Report snapshot / A processed, format-independent report that contains data captured at a specific point in time. It is identical to a report definition, except that it contains a data set instead of just query information.
Rendered report / A final, fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).
Resource / A file (such as a bitmap) stored in the ReportServer database that can be shared by multiple reports.
Pre-Deployment Considerations
The following section describes various installation and configuration aspects you should consider before installing and deploying Reporting Services.
Naming Conventions
Reports are organized in a hierarchical namespace and accessed through virtual directories in the Report Server. You will be asked to configure the virtual directories for both Report Server and Report Manager during setup.
The default virtual directory for Report Server is /ReportServer. If Report Server is installed on a named instance, the instance name is appended to the virtual directory: /ReportServer$NamedInstance. For more information, see "URL Addressability," later in this paper.
The default virtual directory for Report Manager is /Reports. During Setup, the virtual directory path you enter is relative to the base server URL. If the server address is servername, then enter /Reports to access Report Manager.
Virtual directory names must comply with IIS conventions.
System Requirements and Prerequisites
Report Server installation requires the following:
- An IIS Web server to host server-side reporting components.
- A SQL Server instance to host Report Services data.
- User accounts with privileges necessary to log on and perform the database creation on the SQL Server instance that will host the reporting database.
- An SMTP server for e-mail delivery of reports.
The general requirements for each component are outlined in the following table.
Component / General system requirementsReport Server / Web server (requires IIS)
Report Manager / Web server (requires IIS)
ReportServer database / SQL Server
Configuration tools and utilities / Client workstation with access to a report server
Report Designer / Client workstation with Visual Studio .NET 2003 (any edition)
The requirements for each type of system are described in the following sections.
Server Requirements
The server components of SQL Server 2000 Reporting Services required by each supported operating system are described in the following table.
Operating system / Operating system-specific requirementsMicrosoft Windows® 2000 Server with Service Pack 4 (SP4) or later
Windows 2000 Advanced Server with SP4 or later
Windows 2000 Datacenter Server with SP4 or later / IIS
SQL Server 2000a + SP3a
SQL Server Agent
Windows XP Professional with Service Pack 1 (SP1) or later / Machine is a member of a domain
Simple File Sharing is turned off
IIS
SQL Server 2000a + SP3a
SQL Server Agent
Windows Server™ 2003, Standard Edition
Windows Server 2003, Enterprise Edition
Windows Server 2003, Datacenter Edition / Application Server role (ASP.NET enabled)
SQL Server 2000a + SP3a
SQL Server Agent
Both Report Server and Report Manager require IIS. IIS should be installed prior to running Setup.
For requirements for the Report Designer, see "Client Requirements," later in this paper.
The hardware requirements for Reporting Services are described in the following table.
Component / General system requirementsProcessor / 500-megahertz (MHz) Pentium II-class processor minimum, 600-MHz Pentium III-class processor recommended
Memory / 256 MB minimum for server components, 512 MB recommended
Hard disk / 100 MB on installation drive
Report Server Database
Reporting Services Setup installs and configures a SQL Server relational database, ReportServer, for storing report definitions, metadata, and history.Reporting Services can be installed on a machine already running an instance of SQL Server 2000, or it can connect to a remote instance of SQL Server. Reporting Services does not support multiple instances on a single server.