Chapter 4. Reporting Services Deployment Scenarios

This chapter provides an overview of Reporting Services deployment scenarios (including Internet deployment), discusses SSRS’ hardware and software requirements, licensing, and security around Reporting Services deployments. More technical details of security are covered in Chapter 18, “Securing Report Server Items.”

An example of SSRS deployment is depicted in Figure 4.1. When an administrator installs SSRS, she has a choice to install one or more client- and server-side components outlined in Table 4.1.

Figure 4.1. Deployment scenarios.

Table 4.1. Reporting Services Deployable Elements

In the Enterprise Production Environment, support for web farms and scale-up capabilities of Enterprise Edition comes in very handy for high-volume reporting. Web farm deployment is very flexible and allows administrators to add capacity to a Report Server web farm as demand grows. In addition, if one of the servers in the web farm fails, the remaining servers will pick up the load. Thus, a web farm provides high availability for a report processing layer, but not the SSRS catalog.

To achieve complete high availability for a reporting solution, a company can install a Reporting Services catalog on a SQL Server 2005 cluster.

For an environment that does not have high performance or availability requirements, you can simplify deployment and use a single Report Server instance with a catalog placed in a nonclustered instance of SQL Server 2005.

You can further simplify deployment in a development environment, install all the Reporting Services components on a single server, and install development tools on a set of workstations.

If a developer or a user needs to be completely mobile, he can install all the necessary components and a subset of data sources on a laptop, as depicted in the Development Environment box in Figure 4.1.

SSRS is a fairly memory- and CPU-intensive application. It is hard to be precise with the exact hardware configuration that an administrator might need for her installation. Table 4.2 presents approximate CPU needs that depend on the number of concurrent users.

Table 4.2. Estimates of Reporting Server CPUs Needs

Table 4.2 provides estimates for a 3-GHz 32-bit Intel Xeon CPU server and is based on SSRS performance for rendering a report of an average layout complexity, which retrieves approximately 5,000 rows of data from a data source, and provides users with HTML output and reasonable completion times of no more than 25-30 seconds. The data source used in this analysis is well tuned and available without significant latency.

Please keep in mind that your results will likely be different from the result in the table. A test is the best way to determine precise configuration needs that are the best suitable for your deployment scenario.

Configuration tips that you might want to consider when deploying SSRS (or specifically a Report Server) are as follows:

1. A 32-bit instance of a Report Server can use memory up to 3GB (requires the /3GB switch in boot.ini). Because of this, efficient hardware use would be at 4GB per instance (3GB for a Report Server and 1GB for OS). To effectively utilize servers with larger amounts of memory, consider installing multiple instances of SSRS per server.
2. For performance, start with scaling up (fastest CPU available, 4GB of RAM, and capable IO subsystem), then move to scale out, and add capacity as necessary (add Report Servers to a web farm). Host the Report Server catalog in a SQL Server instance on a separate box from your data sources (transactional, data warehouse, or line-of-business database) or at least make sure that a SQL Server instance can handle additional workload.
3. For scale-up scenarios, SSRS 2005 supports a 64-bit platform for both x64 (Opteron, Athlon64, and Xeon EMT64T CPUs) and IA64 (Itanium CPU). A 64-bit platform overcomes the 4GB memory limitation of the 32-bit platform and should be considered for reporting applications with high memory demand. A reporting application that renders a fair amount of or large Microsoft Excel or PDF reports is an example of a high memory demand application.
4. For reliability, use redundant components: at least two SSRS web servers and a database cluster for the Reporting Services catalog database, redundant disk arrays, and network pathways. Although high availability requires at least two servers, three is a better number. With three servers, you can do maintenance on one of the servers and still have a high-availability configuration running in your environment.
5. For cost evaluation and decision about buying more servers with a smaller number of CPUs versus fewer servers with a larger number of CPUs in each, consider the price of the hardware, the additional costs associated with extra servers, and the cost of a reporting solution failure. As the number of servers grow, so does the server management overhead and other costs, such as cost of additional space, cooling, and energy costs.

High-Availability Deployment Considerations

To create a highly available Reporting Services installation, an administrator can deploy Reporting Services on a web farm and use clustering for the Reporting Services catalog database. Enterprise Edition of Reporting Services is the only edition that supports web farm deployment in the production environment. Developer Edition and Evaluation Edition can be deployed on a web farm, but only in a testing environment. No other editions support the web farm feature.

Although the Enterprise Edition of SSRS supports a web farm, it does not include a functionality to create and manage a web farm. This is why a company would have to use separate software (or hardware) to create and manage a web farm. An example of web farm management software is the Network Load Balancing (NLB) feature of Windows Server. The steps to install Reporting Services on a web farm (scale-out configuration) are covered in Chapter 21, “Deploying and Configuring SSRS.”

To protect the catalog database, companies can deploy a SQL Server 2005 cluster. Because of Windows authentication between the Report Server and the catalog database, both Report Server and the SQL Server 2005 cluster have to be in either the same or in the trusted domains. Both nodes of the SQL Server 2005 cluster must have an exact match and all hardware and software installed on a cluster must be supported; please see http://www.microsoft.com/windows/catalog/server/default.aspx?subID=22&xslt=category&pgn=904c28be-5a41-4db0-9c12-032dcb893c8b.

Alternative high-availability options can be used to protect from a database server failure: hardware-based data replication or the new peer-to-peer replication in SQL Server 2005.

Internet Deployment Considerations

Reporting Services is not specifically designed for Internet-facing scenarios. This is, partially, because the default authentication mechanism of Reporting Services is Windows integrated security. For security reasons, SQL Server setup does not provide options to deploy SSRS with anonymous access to reports.

Several deployment options are available to a SSRS administrator to make reports accessible over the Internet:

• Keep only public data in the SSRS catalog and enable Report Server for anonymous access.

• Deploy SSRS with Windows authentication and leverage Kerberos delegation to authenticate users.

• Use programmatic options (such as custom security extensions) to authenticate and authorize users.

Internet Deployment Option 1: Enable Report Server for Anonymous Access

This scenario is designed to distribute public information. In this scenario, none of the reports are secured and all of the users would get the same information. When accessing Reporting Services deployed in this fashion, Internet users will not be prompted for login credentials. Best practice for this scenario is to place the SSRS catalog database on the same server with an instance of the Report Server. Because the Report Server has web components, this option means that the SQL Server 2005 instance that hosts catalog data will also be running on the web server and there are no queries that cross boundaries of the web server.

To reduce data exposure in this scenario, the catalog must only contain a limited subset of public data. To further reduce data exposure, reports can be configured to be rendered from an execution snapshot; in this latter case, the SSRS catalog would only contain the snapshot data.

Because this scenario does not protect data from unauthorized access, it might only be used when a company intends to publish public data, such as a product catalog. Secure Sockets Layer (SSL) configuration is not required for this scenario.

To provide public data (or snapshots with public data) to the SSRS catalog in this configuration, an administrator can use replication or SQL Server Integration Services to “copy” public data (or snapshots) from an internal data source to the SSRS catalog placed on a web server.

Internet Deployment Option 2: Deploy Report Server with Windows Authentication

This scenario leverages a default authentication mechanism of SSRS and uses a corresponding security extension.

In this scenario:

1. A company would have a domain associated with web-facing servers and use Kerberos delegation to validate a user by interacting with a corporate domain inside the firewall.
2. Customers can configure Reporting Services virtual directories with either Windows Integrated or Basic authentication.
3. When accessing Reporting Services deployed in this fashion, Internet users will be prompted for credentials. After a user is validated, she will have the level of access to a report corresponding to her credentials.

If this option is chosen, an administrator must configure SSL for proper security, especially for basic authentication.

Internet Deployment Option 3: Use the Programmatic Approach

Some of the situations in which a programmatic approach can be used:

• Users do not have Windows accounts.

• User IDs and passwords are stored in a third-party security provider, which, in turn, is used for user authentication.

• Single sign-on technology (such as Microsoft Passport) is used in place of Windows authentication.

To programmatically handle security, a company can develop a custom Security Extension, handle security within a .NET application, or use the new Report Viewer control.

This book discusses some aspects of security extensions in Chapter 26, “Writing Custom Reporting Services Extensions.” An example of a security extension is provided with SQL Server 2005.

On a high level, to handle security within an application, a developer could

• Authenticate a user in the code by either collaborating authentication processing with a third-party security provider or perhaps simply comparing the user’s identifier and password to the values stored in a database.

• After the user is successfully authenticated, the code would either query a third-party security provider or a database for the user’s security access options.

• Lastly, the code needs to control access to a report, based on the user’s security access options.

You have several options to control a user’s access to a report. Depending on the need of the reporting application, a code can impersonate a Windows user, who mapped to the SSRS Content Manager role (an administrative access). In turn, the code itself would control which reports can be accessed by a user.

Alternatively, depending on the actions that the code must take, the code may impersonate different Windows users who have finer granularity of permissions. In this case, there could be a Windows user who has access to just a single report.

After a user is impersonated, the code can, for example, use the function Render to access the report’s data stream or use the Report Viewer control.

The Report Viewer control can process remote server as well as local reports. When the Report Viewer control processes local reports, it does it internally and does not need access to a Report Server.

Most data sources (like SQL Server) that a Report Viewer control uses require user identification and a password to access data. In this case, an application can collect, for example, a user’s SQL Server credentials and pass those credentials to a data source, thereby restricting the user’s access to data.

Enabling a Report Manager for Internet Access

A couple of options allow users to access the Report Manager over the Internet:

A. Install a Report Manager on the same computer as the Report Server.

B. Install a Report Manager on a different computer than the Report Server.

The following are the steps to enable report management over the Internet (unless marked otherwise, steps are applicable to both options A and B):

1. Install SSRS on the Internet-facing web server using the Install But Do Not Configure installation option.

2. Option B only. The instance installed in step 1 hosts only the Report Manager. Use the SQL Server Surface Area Configuration tool to disable the Reporting Services service.

3. Option B only. Repeat step 1 to install another instance of SSRS on a different computer, normally behind the firewall. This instance hosts the Reporting Services service.

4. Option A only. Secure the connection between the web server and SQL Server computer that will host the SSRS catalog. This can be achieved using IPSec.

5. Run the Reporting Services Configuration Tool, connect to the server that hosts the Reporting Services service, specify service accounts, configure virtual directories, and create the SSRS catalog (if SQL Server is in a different domain and Kerberos is not enabled, use SQL Server credentials to connect to the database server).

6. Option B only. On the computer that hosts Report Manager, modify RsWebApplication.config so the <UI> tag looks like:

<UI>
      <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
      <ReportServerUrl>https://{SERVER}/ReportServer</ReportServerExternalURL>
      <ReportServerExternalURL>https://{SERVER}/ReportServer</ReportServerExternalURL>
      <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>

Where <ReportServerUrl> indicates to the Report Manager that it should access remote server, <ReportServerExternalURL> allows Internet users to use the Report Builder, and {SERVER} is a fully qualified domain name (like www.microsoft.com) of the computer that runs the Reporting Services service.

Minimum Hardware Requirements

Table 4.3 outlines hardware requirements for SQL Server 2005 installations.

The following is the terminology used in relation to the 64-bit platform:

• IA64 refers to Itanium-compatible hardware architecture. This architecture can run IA64 software and 32-bit software using the Windows-On-Windows (WOW64) software emulator. The Itanium CPU cannot natively run 32-bit x86-compatible instructions and uses instruction emulation as a part of WOW64 processing.

• x64 refers to Extended Memory Technology support compatible architecture and includes systems, based on Opteron, Athlon 64, Intel Xeon EM64T, and Intel Pentium EM64T. x64 architecture can run classic 32-bit x86 compatible instructions natively on the CPU. One of the advantages of this architecture is an ability to support both 32- and 64-bit code. To ease an adoption of the 64-bit platform and optimize a hardware purchase, some companies might first deploy a 32-bit operating system and software on x64 hardware and then upgrade to 64-bit software on the same hardware.

Table 4.3. Minimum Hardware Requirements

Software Requirements

Authors recommend installing Reporting Services on Windows 2003 SP1 (Service Pack 1). Although Windows 2000 is a fully supported platform, Windows 2003 SP1 reflects the latest technological advances, including enhanced coverage in the areas of security and high availability.

All editions of Reporting Services support installation on Virtual Server or Virtual PC, provided that the supported operating system is installed.

Tables 4.4, 4.5, and 4.6 list operating system requirements and additional software requirements for installation of Reporting Services on 32- and 64-bit platforms correspondingly.

Table 4.4. Operating Systems That Can Run 32-Bit Versions of Report Server

Table 4.5. Operating System Requirements 64-Bit

Table 4.6. Additional Software Requirements 32- and 64-Bit

For situations with heavy memory or IO requirements, such as heavy graphics and PDF rendering, customers can benefit from deploying SSRS on a 64-bit platform. Table 4.5 outlines SSRS support on a 64-bit platform.

Table 4.6 outlines additional software requirements for both 32- and 64-bit platforms and optional software that can be installed to benefit Reporting Services.

Key Features of SSRS by SQL Server 2005 Editions

At least some components of SSRS are available in almost all editions of SQL Server 2005: Workgroup, Standard, Enterprise, Developer, and Evaluation.

Whether a customer is a large enterprise or a small company, the key features of Reporting Services that are always available include the following:

• Manageability—Reporting Services is easy to deploy and manage. In addition to having a convenient web-based management interface, both deployment and management of Reporting Services can be scripted.

• Security—Reporting Services keeps corporate data secure. Reports and information are not accessible, unless sufficient privilege is granted to a user.

• Programmability—Reporting Services allows developing of a custom functionality that can be embedded in a report, called from a report, or scripted.

• Reporting controls and wizard—Windows and web-based Report Viewer controls are supplied with Visual Studio 2005. Report controls simplify adding reporting functionality to Windows and web-based applications.

Additional features available in the Standard Edition of Reporting Services include the following:

• Extensibility—Reporting Services allows adding new server functionality. Report Definition Language (RDL) is an XML-based language and is designed to be extensible. SSRS also allows extending data processing, rendering, and delivery extensions.

Additional features available in Enterprise Edition of Reporting Services include the following:

• Scalability—Reporting Services Enterprise Edition supports large workloads and high-volume reporting. Support for web farms in Enterprise Edition allows easy scale out, providing an ability to add extra capacity as needed. In addition, Enterprise Edition scales up, supporting more than two CPUs.

• Availability—Web farm support of Reporting Services Enterprise Edition paired with the Reporting Services catalog installed on a SQL Server 2005 cluster enables high-availability reporting solutions.

• Data-driven subscriptions—Reporting Services Enterprise Edition allows customers to dynamically change the recipient list, report parameters, and processing options. In contrast, Standard Subscription, available in Standard Edition of Reporting Services, is for a single predefined user and single predefined parameter set.

To help determine the most appropriate version, you can refer to Table 4.7 to review key features of SSRS editions

Table 4.7. Key Features by Reporting Services Editions

Licensing

Complete details of SSRS licensing can be found on the Microsoft licensing website at http://www.microsoft.com/licensing/default.mspx and in the following document http://download.microsoft.com/download/e/3/7/e37e542f-f90c-4d5f-864c-3f428d5add5e/SQL2005_Licensing.doc.

In a “nutshell,” a Server license (for Workgroup, Standard, or Enterprise editions) is required for every operating system environment on which that edition of SQL Server software or any of its components (for example, Reporting Services) is running.

This means that a company does not have to buy a separate license if SSRS is installed with SQL Server 2005 together on a single computer. For scale-out (web farm) deployments, each web server that runs Report Server would have to have a SQL Server license.

Summary

In this chapter, you have learned about various SSRS deployment choices. Deployment choices for SSRS components range from a developer’s workstation, in which all SSRS components are installed on a single computer, to an enterprise, high-availability and high-performance, multiserver, web-farm deployment.

This chapter also covered several options of deploying SSRS for Internet access: enable Report Server for anonymous access and publish only public data, deploy SSRS on the Internet using default Windows authentication and programmatic authentication, and access options.

Lastly, this chapter covered hardware and software requirements, licensing, and key features of SSRS editions.

The next chapter delves into the SSRS installation process.