• High-Availability Deployment Considerations
• Internet Deployment Considerations
• Minimum Hardware Requirements
• Software Requirements
• Key Features of SSRS by SQL Server 2005 Editions
• Licensing
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
Although the test (staging) environment might not be as “powerful” as production, it is best to have a total match for the most effective and realistic scalability testing.
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.
There is no separate Books-On-Line for SSRS. Books-On-Line covers all the SQL Server 2005 components: Reporting Services, SQL Server engine, T-SQL, and so on.
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:
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.
The new database mirroring functionality of the SQL Server 2005 would have been another high-availability option and although the database mirroring is included in this release of the SQL Server 2005—the mirroring is not currently supported in production environments; see http://support.microsoft.com/kb/907741.
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.
When an administrator deploys Reporting Services on the Internet, his web server security might not allow installation to proceed. The work-around is to block the target server from Internet access, relax security, install SSRS, and tighten security again.
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.
To configure a report’s rendering from a report execution snapshot, an administrator can use the Report Manager, navigate to a report that needs to be configured, then navigate to the Properties tab, Execution screen and select the Render This Report from a Report Execution Snapshot option.
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.
This scenario leverages a default authentication mechanism of SSRS and uses a corresponding security extension.
In this scenario:
If this option is chosen, an administrator must configure SSL for proper security, especially for basic authentication.
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.
Please keep in mind that security breaches can have far-reaching financial consequences for a business; thus, use custom security solutions with caution, especially when a reporting solution is exposed on the Internet.
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.
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.
Just like with any SSRS deployment, to make sure that Reporting Services Internet deployment is successful, users should be able to access SSRS by typing: http(s)://
{SERVER}/ReportServer
.
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
System Configuration Check blocks setup from running if the CPU type (Pentium III or higher) requirement is not met. Setup issues a warning, but allows you to proceed, if the CPU speed or minimum memory requirement is not met.
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
Systems that are not explicitly listed in Table 4.4 are not supported by Reporting Services; for example, Reporting Services 32-bit is not supported on Windows 2003 64-bit Itanium.
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.
64-bit versions of SQL Server Workgroup and Express editions are not available in this release.
Development tools such as Business Intelligence Development Studio are neither installed nor supported on the IA64 platform. For IA64 deployments, use development tools installed on a separate 32-bit or x64 workstation.
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.
At least some components of SSRS are available in almost all editions of SQL Server 2005: Workgroup, Standard, Enterprise, Developer, and Evaluation.
The Reporting Services install is now bundled with SQL Server Install. This is unlike Reporting Services 2000, which has an independent setup.
At the time of writing, SQL Server Express Edition did not have a version of SSRS. SQL Server Express Edition may eventually include an Express Edition of SSRS.
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
Developer and Evaluation editions have the same capabilities as the Enterprise Edition of SSRS. However, the Developer Edition is only licensed and supported in the development environment and Evaluation Edition expires after 180 days.
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.
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.