Reporting Services High-Availability Deployment ...

1 CHAPTER 5 Reporting ServicesDeployment ScenariosIN THIS Features of SSRS chapter provides an overview of Reporting Servicesdeployment scenarios (including Internet Deployment ),including SSRS hardware and software requirements, licens-ing, and security. More technical information about securityis covered in Chapter 20, Securing Report server Items. NOTEA lthough the test (staging) environment might not beas powerful as production, it is best to have a totalmatch for the most effective and realistic a SQL server Reporting Services enterprise productionenvironment, support for web farms and scale-up capabili-ties of Enterprise Edition come in handy for high-volumereporting. Web farm Deployment is flexible and enablesadministrators to add capacity to a Report server web farmas demand grows. In addition, if one of the servers in theweb farm fails, the remaining servers pick up the , a web farm provides high availability for a report-processing layer, but not the SSRS catalog (database).

2 To achieve complete high availability for a Reporting solu-tion, a company can install a Reporting Services catalog ona SQL server 2008 an environment that does not have high-performanceor -availability requirements, you can simplify deploymentand use a single Report server instance with a catalogplaced in a nonclustered instance of SQL server 6/29/09 3:25 PM Page 79 Microsoft SQL server 2008 Reporting Services Unleashed, 0672330261 Copyright 2010 by Sams Publishing80 CHAPTER 5 Reporting Services Deployment ScenariosTABLE of Reporting server CPUs NeedsConcurrent UsersApproximate Number of CPUs< 1501< 7002700 > < 2,0004 82,000 > < 4,0008 164,000 >16+You can further simplify Deployment in a development environment, install all theReporting Services components on a single server , and install development tools on a setof a developer or a user needs to be completely mobile, that user can install all the neces-sary components and a subset of data sources on a laptop, as depicted in the Single ServerDeployment in Figure is no separate Books Online for SSRS.

3 Books Online covers all the SQL Server2008 components: Reporting Services , SQL server engine, T-SQL, and so is a fairly memory- and CPU-intensive application. It is hard to be precise with theexact hardware configuration that an administrator might need for installation. Table approximate CPU needs that depend on the number of concurrent 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 retrievesapproximately 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 datasource used in this analysis is well tuned and available without significant in mind that your results will likely differ from the result in the table. A test is the bestway to determine precise configuration needs best suited for your Deployment tips that you might want to consider when deploying SSRS (or specifically aReport server ) include the following.

4 A 32-bit instance of a Report server can use memory up to 3GB (requires the /3 GBswitch in ). Because of this, efficient hardware use would be at 4GB perinstance (3GB for a Report server and 1GB for the OS). To effectively use servers withlarger amounts of memory, consider installing multiple instances of SSRS per performance, start with scaling up (fastest CPU available, 4GB of RAM, andcapable I/O subsystem), then move to scale out, and add capacity as necessary ( 6/29/09 3:25 PM Page 80 Microsoft SQL server 2008 Reporting Services Unleashed, 0672330261 Copyright 2010 by Sams Publishing81 High-Availability Deployment Considerations5 Report Servers to a web farm). Host the Report server catalog in a SQL Serverinstance on a separate box from your data sources (transactional, data warehouse, orline-of-business database) or at least make sure that a SQL server instance can handleadditional scale-up scenarios, SSRS 2008 supports a 64-bit platform for both x64 (Opteron,Athlon64, and Xeon EMT64T CPUs) and IA64 (Itanium CPU).

5 A 64-bit platformovercomes the 4GB memory limitation of the 32-bit platform and should be consid-ered for Reporting applications with high memory demand. A Reporting applicationthat renders a fair amount of or large Microsoft Excel or PDF reports is an exampleof a high-memory-demand reliability, use redundant components: at least two SSRS web servers and a data-base cluster for the Reporting Services catalog database, redundant disk arrays, andnetwork pathways. Although high availability requires at least two servers, three isbetter. With three servers, you can do maintenance on one of the servers and stillhave a High-Availability configuration running in your cost evaluation when deciding whether to buy more servers with a smaller num-ber of CPUs versus fewer servers with a larger number of CPUs in each, consider theprice of the hardware, the additional costs associated with extra servers, and the costof a Reporting -solution failure.

6 As the number of servers grows, so do the server man-agement overhead and other costs, such as the cost of additional space, cooling, Deployment ConsiderationsTo create a highly available Reporting Services installation, an administrator can deployReporting Services on a web farm and use clustering for the Reporting Services catalogdatabase. Enterprise Edition of Reporting Services is the only edition that supports webfarm Deployment in the production environment. Developer Edition and EvaluationEdition can be deployed on a web farm, but only in a testing environment. No othereditions support the web farm the Enterprise Edition of SSRS supports a web farm, it does not include a func-tionality to create and manage a web farm. This is why a company would have to useseparate software (or hardware) to create and manage a web farm. An example of webfarm management software is the Network Load Balancing (NLB) feature of WindowsServer.

7 The steps to install Reporting Services on a web farm (scale-out configuration) arecovered in Chapter 6, Installing Reporting Services . To protect the catalog database, companies can deploy a SQL server 2008 cluster. IfWindows authentication is being used between the Report server and the SQL Server2008, both Report server and the SQL server 2008 cluster have to be in either the same orin the trusted domains. Both nodes of the SQL server 2008 cluster must have an exactmatch and all hardware and software installed on a cluster must be 6/29/09 3:25 PM Page 81 Microsoft SQL server 2008 Reporting Services Unleashed, 0672330261 Copyright 2010 by Sams Publishing82 CHAPTER 5 Reporting Services Deployment ScenariosReportServerDatabaseSingle ServerDeploymentReportServerReportServer DatabaseStandardDeploymentReportServerRe portServerDatabaseReportServerDatabaseSt andard Scale OutDeploymentSQL server Failover ClusterReportServerLoadBalancerReportSer verFIGURE Services Deployable ElementsComponentApproximateSizeTypical Install LocationReporting Services230 MBDeployed on the serverAlternative High-Availability options can be used to protect from a database server failure.

8 Hardware-based data replication or peer-to-peer replication in SQL server database mirroring functionality of SQL server 2008 is another of Deployment ScenariosSSRS 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 bothmajor components of SSRS: the database and the Report second major scenario is the scale-out Deployment , in which the database is on onemachine, possibly a clustered virtual machine, and the Report server is on anothermachine or on a web shows a sample SSRS Deployment . When administrators install SSRS, they have achoice to install one or more client- and server -side components, as outlined in Table 6/29/09 3:25 PM Page 82 Microsoft SQL server 2008 Reporting Services Unleashed, 0672330261 Copyright 2010 by Sams Publishing83 Overview of Deployment Scenarios5 SSRS 2008 added the ability to separate out servers to do simply scheduled batch orsubscription processing.

9 Figure shows an advanced scale-out scenario where servers areisolated for doing simply on-demand or batch of the Standard ModelThe standard model, or single- server Deployment model, might sound simple and easy todo at first, and it is certainly the way to do it for a development workstation, or a simpletrial or proof of concept. However, you should consider a couple of things when debatingwhether to use this model in a production of an Advanced Scale-Out ScenarioReportServerDatabaseReportServer DatabaseSQL server Failover ClusterLoadBalancerReportServerReportSer verReportServerFile ServerorEmailClientOn Demand Report ProcessingScheduled or Batch ProcessingFIGURE Deployment Install LocationBooks Online160 MBDeveloper s or administrator s work-stationBasic management tools - command-line tools880 MBDeveloper s or administrator s work-stationSQL server Management Studio(includes basic management tools)900 MBDeveloper s or administrator s work-station.

10 NET FrameworkBusiness Intelligence DevelopmentStudio1 GBDeveloper s 6/29/09 3:25 PM Page 83 Microsoft SQL server 2008 Reporting Services Unleashed, 0672330261 Copyright 2010 by Sams Publishing84 CHAPTER 5 Reporting Services Deployment ScenariosPerformance Impact of the Standard ModelThe primary consideration for most administrators after cost is performance. Having boththe database and the Report server on the same machine might sound tempting on thefinancial 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 2008has made huge strides in rendering efficiency, SSRS is still going to use all the RAM it can getor 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.