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

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

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

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

5. 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

6. 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

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

8. 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

9. 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

10. RS Architecture Report Manager Report Server Report Catalog

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

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

13. Production Environment • Architecture • Configuration

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

15. 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

16. 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

17. Scale Up or Scale Out?

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

19. 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”

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

21. Report Server Farm Load Balancer Report Server Report Catalog

22. 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

23. Distributed Environments Router Report Server Report Catalog

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

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

26. 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

27. 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

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

29. 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

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

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

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

33. 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

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

35. Content Management • Report Authoring • Lifecycle Management

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

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

38. 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

39. 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.

40. 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

41. Lifecycle Management • Environments • Deployment Scenarios

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

43. 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

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

45. 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

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

47. 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

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

49. Three Instance Scenario Dev Test Prod

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