Scale-Out Deployment of Microsoft SQL Server Reporting Services

1. DBI405 Scale-Out Deployment of Microsoft SQL Server Reporting Services Carl Rabeler, Prash Shirolkar Program Managers Microsoft

2. Session Objectives And Takeaways • Session Objective(s): • Provide guidance on how to scale out your Reporting Services environment • Provide RS best practices on RS catalogs, scale out deployment, and performance optimizations

3. Reporting Services Scale Out Architecture • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Disaster Recovery and Scale-Out • Troubleshooting Tips

4. Scaling Out Architecture: Native ModeOverall Architecture Report Server RS Scale Out Deployment Report Catalog Reporting Data

5. Data Flow in Native Mode • 1. Report request from user to Reporting Services engine • 2. Reporting Services engine retrieval of report definition from RS catalog server • 3. Reporting Services engine query request to data source • 4. Return of the rendered report from the Reporting Services engine to the user

6. Scaling Out Architecture: SharePoint ModeOverall Architecture – Pre Denali SharePoint Farm Catalogs RS Application Servers SharePoint Web Front End Servers Reporting Data

7. Data Flow in SharePoint Mode • 1. Report request from user to the SharePoint web service • 2. SharePoint engine retrieval of the report definition from the SharePoint catalog (the master copy) • 3. SharePoint web service report request to Reporting Services engine • 4. Reporting Services engine query to the RS catalog server to verify that the report definition exists in the RS catalog and that it is same as the master copy • 5. Reporting Services engine query request to data source • 6. Reporting Services forward of rendered report to the SharePoint web service • 7. Return of the report from the SharePoint web service to the user

8. Scale Out Architecture Performance Illustration

9. Overview of Scale Out (and Scale Up) • Scale up RS front-end servers for performance (e.g. four-processor, quad-core) • Scale out to multiple RS front end servers for performance and high availability • Use dedicated RS Catalog servers - scale up for performance and cluster for availability • In SharePoint mode, use dedicated RS application server(s) and scale up and/or scale out WFE servers • Optimize disk I/O subsystem for maximum performance • Optimize network links to ensure sufficient bandwidth

10. Scale Out ArchitectureRead the manuals! • Read these SQL CAT papers: • Reporting Services Scale-Out Architecture http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx • Report Catalog Best Practices http://sqlcat.com/technicalnotes/archive/2008/06/26/report-server-catalog-best-practices.aspx • Reporting Services Scale-Out Deployment Best Practices http://sqlcat.com/technicalnotes/archive/2008/10/21/reporting-services-scale-out-deployment-best-practices.aspx • Reporting Services Performance Optimizations http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx • SQL Server Reporting Services Disaster Recovery Case Study http://sqlcat.com/technicalnotes/archive/2009/10/23/sql-server-reporting-services-disaster-recovery-case-study.aspx • Read these TechNet Articles • Configuring Reporting Services for Scale-Out Deployment http://technet.microsoft.com/en-us/library/ms156453.aspx • How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration) http://technet.microsoft.com/en-us/library/ms159114.aspx • Deployment Topologies for Reporting Services in SharePoint Integrated Mode http://technet.microsoft.com/en-us/library/bb510781.aspx

11. Scale Out ArchitectureImportance of Performance Testing • Need to understand your scenarios and reports • Scenarios are defined by user personas & usage patterns • Reports are either test reports or actual reports • Tests should isolate Report Server from other systems • Need tools to automate the testing • See white paper: Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server • Make single incremental changes between tests • Do not use SQL trace inside VSTE

12. Report CatalogBest Practices • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Disaster Recovery and Scale-Out • Troubleshooting Tips

13. Report Catalog Best Practices Native Mode Report Server RS Scale Out Deployment Report Catalog Reporting Data

14. Report Catalog Best PracticesReport Server Catalog Breakdown Report Server Catalog (RSDB) Stores all report metadata including report definitions, report / history snapshots, scheduling, etc. • These databases can be a bottleneck • Optimize by applying standard SQL DB techniques • Catalog has a lot of I/O and transactions • RS2005: Many inserts to ChunkData, SnapshotData, and SessionData tables • RS2008 and RS 2008R2: Many inserts Segment; takes majority of transactions of RSTempDB Report Catalog RS Temp DB Stores temporary snapshots while running reports

15. Report Catalog Best PracticesUse a dedicated catalog server Common (not best performance) scenarios • Same server as SSRS Server • Great for small environments • In enterprise environments, too much resource contention • Same server as data source database • SQL resource contention (TempDB, plan cache, memory buffer pool) between data source and RS catalogs • As load increases need to monitor CPU, I/O, network resources, and buffer pool For best performance • Reduce resource contention by having a dedicated RS catalog server you can tune

16. Report Catalog Best PracticesSharePoint Mode SharePoint Farm Catalogs RS Application Servers SharePoint Web Front End Servers Reporting Data

17. Report Catalog Best PracticesHigh Performance Disk • Check out Predeployment I/O Best Practices • Have more smaller size disks with faster rotation speeds (>=15k RPM) vs. fewer larger disks with slower rotations • Maximize/balance I/O across ALL available spindles • Separate disks between RSDB and RSTempDB • RSDB a lot of small transactions (report metadata) • RSTempDB has more (not as many) larger transactions • In SharePoint mode, use separate disks from the content DBs • Pre-grow your databases • Stripe dB files to number of cores (0.25 – 1.0) • Minimize allocation contention • Easier to rebalance database when new LUNs are available • Use RAID 10, not RAID 5

18. Report Catalog Best PracticesOperations Best Practices • Data in RSTempDB is highly volatile • Report lifetime policy of data = SessionTimeout value (10min) • CleanupCycleMinutes guides background cleanup thread • Once session timeout reached, cleanup temporary snapshot from tempDB • This is done every CleanupCycleMinutes • Data is RSDB is long lived; should be backed up • Backing Up and Restore Databases in SQL Server • Optimizing Backup and Restore Performance in SQL Server • Backing Up and Restore Encryption Keys • SharePoint backup does not back up the RSDB – until Denali • Maintain your RS catalogs • Remember, these are SQL databases • E.g. Re-indexing catalog tables or updating stats may improve query performance

19. Scale Out DeploymentBest Practices • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Disaster Recovery and Scale-Out • Troubleshooting Tips

20. Scale Out Deployment BP Report Server RS Scale Out Deployment Report Catalog Reporting Data

21. Scale Out Deployment BP Network • Load balancing important for many client connections to RS servers • Recommend: Use cookie persistence to preserve SSRS-to-client connection • IP affinity can work but may be overload for browser-based connections • Keep round-robin for initial connections • Recommend: dual NIC for RS • Split browser and RS/DB traffic RS Scale Out Deployment

22. Scaling Out Architecture: SharePoint ModeNetwork • Load balancing important for many client connections to RS servers • Balancing by WFE across multiple RS application servers handled by SharePoint • Recommend: dual NIC for RS • Split browser and AS/DB traffic RS Application Servers SharePoint Web Front End Servers

23. Scale Out Deployment BPReport Data Performance Considerations • Scale out works for RS but may not work (or may require different techniques) for underlying Report Data (data source) • Reporting loads Report Data, \ limit impact of large numbers of users • Limit data set size using report filters • SSIS to limit data from Operational data sources • Do not let all users access all of the reports • Use shared data sets to enable DBAs to optimize query performance without having to be Reporting Service experts Reporting Data

24. Performance Optimization Configurations • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Disaster Recovery and Scale-Out • Troubleshooting Tips

25. Performance OptimizationUse 64-bit hardware and software • RS Catalog • It definitely helps the RS Catalog SQL servers • Report Servers • RS requires each data set to fit in memory • E.g. 32-bit RS will hit 3GB ceiling • IIS process recycles = reports fails • 64-bit does not increase the speed of report rendering • Allow users to view and export larger reports • May get better throughput at higher workloads due to less memory contention • SharePoint application servers handle more concurrency with more memory – SharePoint 2010 is 64-bit only

26. Performance OptimizationFeatures Recommendations • Use Report Execution Timeouts to control how a long a report has to execute • For large reports with data processing bottlenecks, use Scheduled Snapshots • Rendering reports into non-browser formats like PDF and Excel, address this by scheduling Delivered Renderedreports • Pre-populate the report cache using data driven subscriptions • Remember, RS is designed for interactive use; If you want to export data, use file share delivery or SSIS as your mechanisms

27. Performance OptimizationConfigurations • Recommendation: • Disable report history to reduce/remove the report history snapshots • If history desired, establish global or report-specific limit to the number of snapshots • Recommendation: • Understand the need for big reports • Consider Cache and Snapshot execution options • Mitigates against load cause by concurrent users

28. Performance OptimizationMemory Configurations • MemoryLimitConfiguration • Default 60% of available memory • Increase help process more requests • Once threshold hit, no new requests are accepted • MaximumMemoryLimitConfiguration • Default 80% of available memory • If this threshold is met, processing is aborted • Changing values may solve RS only to bring up other contentions • Recommendation: If constantly hitting memory thresholds, consider scaling up and then scaling out

29. Disaster Recovery and Scale-Out • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Disaster Recovery and Scale-Out • Troubleshooting Tips

30. Basic Disaster Recovery Scenario • Primary Data Center • Use failover cluster for RS catalogs • Use asynchronous mirroring to synch RS database with DR site • DR Site • Closely duplicate primary data center • Geographically separated • Fiber connectivity between sites to minimize latency • Identically named instances • Content switch • To load balance traffic within site and between global sites (when needed)

31. Reporting Services Servers within the Primary Data Center Go Offline • Auto failover to RS Servers in DR Site • Content switch detects outage and will automatically direct traffic to RS Servers in DR site • Continue reading source data from primary site • The primary site has the most up-to-date data • Client connectivity to RS environment is minimally impacted

32. Active Cluster for RSDB in the Primary Data Center Goes Offline • Auto failover to secondary in failover cluster • Clustering services will detect failure and automatically fail over • Continue reading source data from promoted secondary in primary data center • Client connectivity to RS environment is minimally impacted

33. Entire Cluster for RSDB in the Primary Data Center Goes Offline • Manual failover to RS Servers in DR Site • RS servers must be manually configured to point to the RSDB in the DR site • Simplified by identical instance names • The DR Site may not have the most recent data due to asynchronous mirroring • May be some data loss, but it should be minimal as RSDB data not particularly volatile • Client connectivity to RS environment is minimally impacted

34. Reporting Services Troubleshooting Tips • Agenda • Reporting Services Scale Out Architecture • Report Catalog Best Practices • Scale Out Deployment Best Practices • Performance Optimization Configurations • Troubleshooting Tips

35. Troubleshooting TipsOverview • Reports can take up • a lot of memory • a lot of time to execute • a lot of CPU • It is possible for errors to occur as a result: • Out of Memory • Internal Errors • Rendering errors • So how do we diagnose issues when running reports in the Report Server?

36. TroubleshootingStart with the RS Logs Files • Reporting Services execution log • Contains data about specific reports and their performance • Stored in Report Server database • Report Server service trace log • Detailed error stacks showing what the problems are • Located under: • <install folder>\Reporting Services\LogFiles • Windows Application log • Major events are in the Windows application event log • References • Reporting Services Log Files details are in BOL at: http://msdn2.microsoft.com/en-us/library/ms157403.aspx • RS Error code info: http://msdn2.microsoft.com/en-us/library/ms165307.aspx

37. Troubleshooting TipsMonitoring Performance • Want to look at memory consumptions, application domain recycles, cpu usage, etc. • Good resources for this: • This topic describes the performance counters http://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx • Analyzing and Optimizing Reports – ExecutionLog2 View http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx • Monitoring Report Execution Performance with Execution Logs http://msdn2.microsoft.com/en-us/library/aa964131.aspx • Monitoring interactive report executions via ListJobs SOAP API to see which long running reports are currently executing http://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspx • Use the SQL Server Profiler to monitor actions of the Report Server database

38. Troubleshooting TipsUsing ExecutionLog2 • Syntax use ReportServer select * from ExecutionLog2 order by TimeStartDESC • Use to answer questions such as: • What are my long running reports, and where is the time spent for these reports? • Which reports might be good candidates for caching? • How many reports were returned from cache vs. live execution vs. execution snapshot? • What was the most popular report for the week? • What are the poor performing reports that need to be taken action upon? • Which reports have large outputs, such as PDF? • Which reports utilize a large amount of memory?

39. Troubleshooting TipsDiagnosing Problems Specific actions to help diagnose problems: • Adjust memory limits: • Refer to “Report Size in Memory” section of http://msdn2.microsoft.com/en-US/library/ms156002.aspx • Adjust schedule concurrency: • Do this to isolate which are your problem reports • May want to reduce number of simultaneous report executions to 1 especially for extremely large reports • In rsreportserver.config, set <MaxQueueThreads> • Zero (0) means RS server automatically determines the “right number”

40. Troubleshooting TipsCommon problems • Error 401 is an authentication error • Providing no or incorrect credentials • Also can be seen during an ASP.NET application reset as well • Happens if server starts running out of memory • Check logs for InternalCatalogException; • in the stack trace, you should see the method that failed • Within trace logs - Application Domain Recycles • App domain recycles used to clear out memory • Often will be correlated with interactive report execution failures • Indicates RS server is under memory pressure

41. Troubleshooting TipsCommon problems (Continued) • SQL Query Plan optimization • Stored Procedures require query plan optimization • Use the With Recompile directive in Stored Procedures • Use fully qualified names to access across DB objects • Refresh on SQL Query Performance: • http://msdn2.microsoft.com/en-us/library/ms187032.aspx • Report Design with drop-down parameter lists • Evaluating drop downs results in query load on DBMS • Optimize parameter, prompts, and valid values

42. What’s Next? • Follow, Tweet and Enter to win an Xbox Kinect Bundle • GAME ON! Join us at the top of every hour at the BI booth to compete in the Crescent Puzzle Challenge and Win Prizes • Sign up to be notified when the next CTP is available at: microsoft.com/sqlserver Join the Conversation @MicrosoftBI /MicrosoftBI

43. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

44. Complete an evaluation on CommNet and enter to win!

46. © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. 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 provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.