SQL Server 2008 Reporting Services for high-availability deployment

Solutions providers can use this chapter excerpt to learn about highly available Reporting Services installations and high-availability deployment considerations.

This chapter provides an overview of Reporting Services deployment scenarios (including Internet deployment), including SSRS hardware and software requirements, licensing, and security. More technical information about security is covered in Chapter 20, "Securing Report Server Items."

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.

About the book:
This chapter excerpt on Reporting Services Deployment Scenarios (download PDF) is taken from the book Microsoft SQL Server 2008 Reporting Services Unleashed. This book can help solutions providers to use SQL Server 2008 Reporting Services to deliver business intelligence services to customers, learn about new features in SQL Server 2008 R2, manage report data sources and models and more.

In a SQL Server Reporting Services enterprise production environment, support for web farms and scale-up capabilities of Enterprise Edition come in handy for high-volume reporting. Web farm deployment is flexible and enables 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 pick up the load. Thus, a web farm provides high availability for a report processing layer, but not the SSRS catalog (database).

To achieve complete high availability for a reporting solution, a company can install a Reporting Services catalog on a SQL Server 2008 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 2008.

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, that user can install all the necessary components and a subset of data sources on a laptop, as depicted in the Single Server Deployment in Figure 5.1.

There is no separate Books Online for SSRS. Books Online covers all the SQL Server 2008 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 installation. Table 5.1 presents approximate CPU needs that depend on the number of concurrent users.

TABLE 5.1 Estimates of Reporting Server CPUs Needs


Concurrent Users Approximate Number of CPUs
< 150 1
< 700 2
700 > < 2,000 4-8
2,000 > < 4,000 8-16
4,000 > 16+

Table 5.1 provides estimates for a 3GHz 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 to 30 seconds. The data source used in this analysis is well tuned and available without significant latency.

Keep in mind that your results will likely differ from the result in the table. A test is the best way to determine precise configuration needs best suited for your deployment scenario.

Configuration tips that you might want to consider when deploying SSRS (or specifically a Report Server) include the following:

  • 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 the OS). To effectively use servers with larger amounts of memory, consider installing multiple instances of SSRS per server.
  • For performance, start with scaling up (fastest CPU available, 4GB of RAM, and capable I/O 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.
  • For scale-up scenarios, SSRS 2008 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.
  • 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 better. With three servers, you can do maintenance on one of the servers and still have a high-availability configuration running in your environment.
  • For cost evaluation when deciding whether to buy 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 grows, so do the server management overhead and other costs, such as the cost of additional space, cooling, and energy.

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 6, "Installing Reporting Services." To protect the catalog database, companies can deploy a SQL Server 2008 cluster. If Windows authentication is being used between the Report Server and the SQL Server 2008, both Report Server and the SQL Server 2008 cluster have to be in either the same or in the trusted domains. Both nodes of the SQL Server 2008 cluster must have an exact match and all hardware and software installed on a cluster must be supported.

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

The database mirroring functionality of SQL Server 2008 is another high-availability option.

Overview of Deployment Scenarios

SSRS has two main deployment scenarios. The first is possibly the simplest: the single server deployment. In this scenario, a single machine is responsible for hosting both major components of SSRS: the database and the Report Server.

The second major scenario is the scale-out deployment, in which the database is on one machine, possibly a clustered virtual machine, and the Report Server is on another machine or on a web farm.

Figure 5.1 shows a sample SSRS deployment. When administrators install SSRS, they have a choice to install one or more client- and server-side components, as outlined in Table 5.2.

FIGURE 5.1 Deployment scenarios.

TABLE 5.2 Reporting Services Deployable Elements


Component Approximate Size Typical Install Location
Books Online 160MB Developer's or administrator's workstation
Basic management tools – commandline tools 880MB Developer's or administrator's workstation
SQL Server Management Studio (includes basic management tools) 900MB Developer's or administrator's workstation, .NET Framework
Business Intelligence Development Studio 1GB Developer's workstation

SSRS 2008 added the ability to separate out servers to do simply scheduled batch or subscription processing. Figure 5.2 shows an advanced scale-out scenario where servers are isolated for doing simply on-demand or batch processing.

FIGURE 5.2 Advanced deployment scenario.

Advantages/Disadvantages of the Standard Model

The standard model, or single-server deployment model, might sound simple and easy to do at first, and it is certainly the way to do it for a development workstation, or a simple trial or proof of concept. However, you should consider a couple of things when debating whether to use this model in a production environment.

About the authors:
Michael Lisin has more than 12 years experience in the software industry with expertise in SQL Server and .NET technologies. Lisin is a regular speaker on SQL Server at various events, including SQL Server PASS and SQL Server User Group meetings.  

Jim Joseph is the co-author of Microsoft SQL Server 2005 Reporting Services and is currently employed by Continental Airlines as a SQL Server DBA.  

Amit Goyal is the lead program manager for Microsoft's Reporting Services team and is a known expert on SQL Server 2008 Reporting Services.

Performance Impact of the Standard Model

The primary consideration for most administrators after cost is performance. Having both the database and the Report Server on the same machine might sound tempting on the financial front because SSRS is included with the SQL Server relational engine. However, both the relational engine and Report Server love RAM and CPU cycles. Although SSRS 2008 has made huge strides in rendering efficiency, SSRS is still going to use all the RAM it can get or whatever it needs (the lower of the two numbers) to render a report. Rendering reports, and especially rendering large reports, also chews up lots of CPU cycles. Adding this overhead to an older machine that is already struggling with the database server is not advisable.

Disk Space Requirements for SSRS

Anyone who has known a DBA, or who has been one, knows there is one thing all DBAs love: storage. They just can't seem to get enough of it. Even in today's environments with large storage area networks (SANs) and hundreds of spindles, the DBA always wants more. This is for good reason.

SSRS, like most databases, installs with a very small footprint. It's almost, and possibly is, negligible. However, depending on how SSRS is used, the disk space requirements can grow pretty large. To understand how space is used inside the SSRS database, an overview of the different types of objects and how they are stored is required.

By now, it should be understood that the SSRS database holds the Report Definition Language (RDL) files, data sources, models, and all metadata, such as folders and access control lists (ACLs). This might seem like a lot to store, but in reality this is rather small, and only in the most extreme cases should this cause issues. Session state information for SSRS is stored in the Report Server temporary database. Because only one row is generated per user session, this should not get very large, and grows at a predictable rate.

Other things stored in the database can, however, grow to be very large. Resources for reports are stored in the catalog as a binary large object (BLOB). It's a sure bet that your friendly neighborhood DBA hates BLOBs. When a BLOB is stored initially with the report RDL, it might not be such a big deal. However, if a resource is stored as part of a report in an archive solution, this can get very large very quickly. Cached reports or temporary snapshots are stored in the Report Server temporary database as a BLOB in intermediate format. Because cached reports include raw query results, the BLOB can get pretty large. Another disk space consideration when using cached reports with parameterized reports is that a separate copy of the cached report is generated for each combination of report parameters. The bottom line is that if you are using temporary snapshots, prepare to use disk space. In addition, you must consider report history snapshots, too. The only difference between them and temporary snapshots is that the report history is saved inside the Report Server database and not inside the Report Server temporary database.

Availability Impact of Standalone Deployment

If the performance impact of the single-server deployment can be shrugged off, the availability impact of it can't be. Having one machine be the central data store and Report Server creates a single point of failure in an enterprise environment. This makes having a backup essential to save the system from some unforeseen calamity. Not much more can be said about it. It is up to the administrator to decide how critical the functionality SSRS provides is. If it can be down for as much time as needed to restore from tape, or if SSRS is not yet important enough to be deployed in a redundant manner, a standalone deployment should suffice.

Advantages/Disadvantages of the Scale-Out Model

The scale-out model of deployment has two main advantages over the standalone model: performance and availability. However, it has one major downside: cost. Because in the scale-out model the database server is separate from the web server, the performance penalty of combining the database engine with the Report Server's rendering engine gets nullified. In addition, the database can be clustered in a virtual server to provide high availability.

With modern SAN technologies, the database can even be replicated to a remote site. The SSRS application server lives on a separate server. The server is simply the first node in what could become an NLB cluster. The cluster makes it possible to scale out for performance/ availability or both. Scaling out also helps with dispersing the workload generated by scheduled subscriptions, because each machine on the cluster looks for events that trigger a subscription to process. The cluster also allows one node to be removed for upgrades/maintenance and then be placed back online when the maintenance is complete.

NLB clusters are not a function of SSRS. Instead, they are a function of the OS or hardware. SSRS is just an application that can be placed on an existing NLB cluster.

All of this flexibility comes at a price (literally). The only editions to support a scale-out deployment are Developer and Enterprise. Microsoft does not offer support for the Developer Edition, and does not license it for use in a production environment. In addition, every machine in a scale-out deployment has to be licensed separately for Enterprise Edition. More than anything, the cost of a scale out is what keeps most shops from adopting it.

Requirements for a Standard Deployment

In a standard deployment, the web server/application server and the database server are installed on the same machine. For this reason, it is important that the minimum hardware requirements be met or exceeded. It is also helpful to have the NetBIOS name or IP address of the Simple Mail Transfer Protocol (SMTP) server handy and the service account used to execute the reports in unattended mode and the credentials with which to log in to the database.

After collecting all the necessary information, you just need to run setup and configure the Report Server. Sounds easy, doesn't it? While running, the installation program offers two main options. The first option is the default installation. This is the option used for running the standard deployment. This option sets up the database server and the Report Server on the same machine. The second option is called the Files Only option. This option is used primarily in scale-out deployments. For the brave or simply curious, this option can be used to set up SSRS locally; however, the administrator must run the Report Services Configuration tool after the install completes and configure the options herself.

Requirements for a Scale-Out Deployment

As discussed earlier in this chapter, SSRS can be deployed in a scale out on a web farm. Each machine in the web farm runs SQL Server Reporting Services Windows service, which contains the Report Server web services, and the scheduling and delivery processor. As anyone who has managed a web farm knows, in theory any machine on the farm should be easily replaceable with another in the same configuration, and ideally state should not be stored on any box on the farm. SSRS accomplishes this task by using data source configuration information and reports inside the Report Server database. The application servers just need to register themselves with the database server. This might sound simple, but it is not trivial. SSRS 2008 has given administrators much better tools to aid in this configuration process.

Overview of Report Server Initialization

Because SSRS uses potentially sensitive information, it is important to secure it appropriately. In addition, in a scale-out situation, multiple Report Servers need to encrypt and decrypt the data stored in the database. To understand how SSRS accomplishes this, you need a bit of knowledge about encryption and decryption techniques.

In general, there are two kinds of encryption: symmetric and asymmetric. Symmetric is very fast because it uses only one possible key to encrypt and decrypt the data. However, this form of encryption has its drawbacks. How can you share information that has been encrypted with the symmetric key without compromising the key? The answer is to use asymmetric encryption. Asymmetric encryption uses a combination of keys, one public and one private. The public key can be shared with another host and can be used to decrypt messages encrypted with the private key. The same can be said for the private key. Asymmetric encryption is relatively slow, so it should not often be used to encrypt/decrypt.

SSRS uses both types of encryption in a simple, yet intelligent way. For every Report Server database, SSRS generates a unique symmetric key that can then be used to encrypt the data. At this point, every Report Server that needs access to the data must publish its public asymmetric key along with its unique installation ID and client ID to the Report Server database. The Report Server database then uses the public to encrypt the internal symmetric key and share it with the client. After being encrypted with the client's public asymmetric key, the symmetric key cannot be decrypted by anyone else without the private key. Administrators can actually watch this process unfold by watching the changes in the Keys table during the activation process. The process of exchanging public keys and symmetric keys is called activation.

Activation is a two-phase process. The first phase is the Announce Self phase, and the second phase is the Activated phase. The Announce Self phase covers the reading of the keys from the Keys tables and, if needed, the writing of the client's public key to the Keys table. The Activated phase is the time the Report Server gets the symmetric key in encrypted form.

Because the private keys are stored under the user's profile in SSRS, changing the user the service runs under could force a reactivation.

The process of adding and removing machines in the scale-out deployment model is simply the process of running activation over again. The same is true for taking an SSRS installation and pointing it to a different database.

To use ASP.NET with a web farm, the validationKey and decryptionKey should be the same on every machine in the web farm. You can find information about how to accomplish this in the Microsoft Knowledge Base article at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312906.

To remove a server, just uninitialize it by opening the Reporting Services Configuration tool from any node on the cluster, select the node to be removed, and click the Remove button. To move a node, remove the node from its existing setup and follow the steps to add it to the new cluster.

SQL Server Reporting Services deployment scenarios
  SQL Server 2008 Reporting Services for high-availability deployment 
  SQL Server 2008 Reporting Services for Internet deployment 
  SQL Server 2008 hardware and software requirements
  Key features in SQL Server 2008 Reporting Services edition

Printed with permission from Sams Publishing. Copyright 2009. Microsoft SQL Server 2008 Reporting Services Unleashed by Michael Lisin, Jim Joseph and Amit Goyal. For more information about this title and other similar books, please visit Sams Publishing.

Dig Deeper on Database software management