1 / 81

103 - Implementing Reporting Services in the Enterprise Douglas McDowell / Jay Hackney Intellinet

2003 PASS Community Summit November 11-14, 2003. 103 - Implementing Reporting Services in the Enterprise Douglas McDowell / Jay Hackney Intellinet. Agenda. Presentation Goals Reporting Services Overview Production Environment Content Management Application Integration

skip
Download Presentation

103 - Implementing Reporting Services in the Enterprise Douglas McDowell / Jay Hackney Intellinet

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 2003 PASS Community SummitNovember 11-14, 2003 103 - Implementing Reporting Services in the Enterprise Douglas McDowell / Jay Hackney Intellinet

  2. Agenda • Presentation Goals • Reporting Services Overview • Production Environment • Content Management • Application Integration • Real-world Case Study

  3. Agenda • Presentation Goals • Reporting Services Overview • Production Environment • Content Management • Application Integration • Real-world Case Study

  4. Presentation Goals • Demonstrate RS v1.0 is enterprise-ready • Present useful content to speed your first deployment • Real-world perspective of RS solution delivery • Awareness: considerations • “Crack-filler” for other MSFT content

  5. Not in the presentation… • “How to get started” • Reporting Services vs. the rest… • Anything we knew Microsoft was covering: • Report Authoring/Management how-to • Programming/Extending RS

  6. Agenda • Presentation Goals • Reporting Services Overview • Production Environment • Content Management • Application Integration • Real-world Case Study

  7. Ongoing Demo… • Install Prerequisites • SQL Server 2000 Standard or Enterprise • Auto-start SQL Agent • IIS • .NET Framework 1.1 • VS.NET 2003 (client) • Report Designer • Report Manager • http://localhost/ReportServer

  8. Reporting Services Overview • Report Definition Language (RDL) • Microsoft Implementation of RDL • Report Designer: VS.NET 2003 • Report Server: .NET Web Service • Report Catalog: SQL Server 2000 • Report Manager: ASP.NET App • Beta 2 now, RTM before 2004 • http://www.microsoft.com/sql/evaluation/betanominations.asp

  9. RS Architecture Report Manager Report Server Report Catalog

  10. Overview • Reporting Services is another great part of Microsoft SQL Server that is “too easy” to use • Let’s complicate it…

  11. Agenda • Presentation Goals • Reporting Services Overview • Production Environment • Content Management • Application Integration • Real-world Case Study

  12. Production Environment • Architecture • Configuration

  13. Architecture • How many servers do I need? • Scale Up or Scale Out? • Should I put the catalog on another server? • Web Farms • Distributed Environments

  14. How many Report Servers? • Depends on nature of the reports • What percentage are cached or snapshot? • How hard are they to render? • Depends on nature of the users • How many concurrent users? • Are subscriptions being used? • Consideration for peak usage time • Depends on cost – HW/SW

  15. How many Report Servers? • Its been said: 1 processor per 20 concurrent users • The product is not finished and will certainly perform even better • Benchmarking forthcoming – stay tuned

  16. Scale Up or Scale Out?

  17. Separate Database Server? • Large web app and SQL Server on the same box? • Single server best suited for small or low cost solution

  18. Report Server Farms • More than one Report Server sharing a single Report Catalog • High availability • Scale-out performance • Only helps one potential bottleneck • “Pay as you grow”

  19. Report Server Farms • Load Balancer, such as: • Foundry Networks ServerIron XL • Radware Web Server Directory • Windows Network Load Balancing (NLB) • Application Center

  20. Report Server Farm Load Balancer Report Server Report Catalog

  21. Distributed Environments • Multiple instances: multiple Report Servers, multiple Report Catalogs • Deploying geographically separate “regional” servers • Not an alternative to farming • Complicated content distribution • Cannot take advantage of shared cache • Not supported

  22. Distributed Environments Router Report Server Report Catalog

  23. Configuration • Report Server • Database Server • Network • Security

  24. Configuration • Report Server • Database Server • Network • Security

  25. Configuring the Report Server • Memory • 1 gigabyte per processor • More is better • Debatable benefits for tons of memory in a dedicated machine (benchmarking forthcoming) • Report Server is stateless

  26. Configuring the Report Server • DNS https://reportserver.local.com • Machine abstraction • Rudimentary failover • Seamless upgrades to side-by-side equipment • Future growth • A web farm needs a name anyway • Log pruning job

  27. Configuration • Report Server • Database Server • Network • Security

  28. Configuring the Database Server • Don’t underestimate catalog traffic • Multiple catalog requests per report • Not just metadata, cache/snapshots • Consider a separate SQL instance • Independent memory configuration • Isolated SQL Agent jobs • Security: Local System SQL Service (Beta 2) • Farm scenario: cluster Report Catalog

  29. Configuration • Report Server • Database Server • Network • Security

  30. Network Topology • Server / Catalog communication • Consider gigabit • Try to limit hops • WAN connections should be avoided

  31. Configuration • Report Server • Database Server • Network • Security

  32. Security • Use SSL (default in Beta 2 ) • Report Server to Catalog: • Connection strings • Apps to Report Server: • Login credentials • Report data • SSL has a ~5% performance cost

  33. Agenda • Presentation Goals • Reporting Services Overview • Production Environment • Content Management • Application Integration • Real-world Case Study

  34. Content Management • Report Authoring • Lifecycle Management

  35. Report Authoring • Visual Studio.NET 2003 Report Designer • Forced into VS paradigm • Solutions • Projects • Debug / deploy • Great warm-up for Yukon

  36. Report Authoring • Report Server does not see Solutions or Projects per se, only .ds and .rdl contents and some org structures

  37. VS.NET Solutions • Primary value = organization • Business areas – Finance, HR, … • Publishing destination – portal, dash • Audiences – execs, knowledge workers • Data Sources – data warehouse, OLTP • Source Control – VSS • Group with managed code for RS apps

  38. VS.NET Projects • VS.NET – no folders within a Project • All reports published together • Use Projects for logical groups • Publish to single folder, then move • Consider adding other files - .sql, etc.

  39. Report Authoring • Use stored procedures when possible • Don’t regress to two-tiered architecture • Note: cannot use • temp tables • select into • Use Shared Data Sources • Use Source Safe • Use dedicated RS database logins

  40. Lifecycle Management • Environments • Deployment Scenarios

  41. Environments • Development • Test/Staging/QA • Production

  42. Development Environment • Developers upload reports in progress • Not tested for prod-readiness • Designated area for publishing • Drop-off folder • “My Reports” • Isolates developers • No deletion or overwriting

  43. Development Environment • Isolate Development from production environment • Developer cannot change the production environment without passing through the testing process.

  44. Test Environment • For QA to validate Development reports pre-production. • Report Server administrator designates specific area for testing reports • Ideally Test is an exact copy of production environment

  45. Test Environment • Test all environmental changes • New Reports/Data Sources • Caching/Snapshot strategy • Hardware, software • Patches, service packs, drivers

  46. Production Environment • Organize: • Shared Data Source (.ds) folder • Linked Report source (.rdl) folder • Other special use folders – multiple “destinations” • Shared Schedules • Convergence of Web Server and SQL Server maintenance windows

  47. Deployment Scenarios • Three Instances • Two Instances • One Instance • Hybrid? “Instance” = physical and logical Report Server, stand-alone or farmed

  48. Three Instance Scenario Dev Test Prod

  49. Three Instance Scenario • Separate instance for each environment • Substantial management overhead

More Related