2

SQL Server 2008 Reporting Services: Architecture Overview

Ron van MoorselBusiness Intelligence ConsultantProphesy Ltd

Session Code: 352

4

END USER TOOLS & PERFORMANCE MANAGEMENT APPS

ExcelPerformancePoint

Server

BI PLATFORM

SQL Server Reporting Services

SQL Server Analysis Services

SQL Server DBMS

SQL Server Integration Services

SharePoint Server

DELIVERY

Reports Dashboards Excel Workbooks

AnalyticViews Scorecards Plans

Integrated End-to-End BI Offering

5

Objectives And Takeaways

Session ObjectiveLearn about Microsoft SQL Server 2008 Reporting Services (SSRS) architecture and impact on management tools, upgrades and deployment

TakeawaysSSRS Architecture is designed for greater scalability, reliability, performance and ease of deploymentExisting applications and reports continue to workUnderstand SSRS management tools changes and any impact on deployment and upgrade scenarios

6

Delivery Targets(E-mail, SharePoint, Custom)

Output Formats (HTML, Excel, PDF, Custom)

Data Sources(SQL, OLE DB, XML/A, ODBC, Oracle, Custom)

Security Services(NT, Passport, Custom)

Report Server

XML Web Service Interface

SQL Server Catalog

Report Engine

Scheduling & DeliveryRendering

Data Processing Security

Model Designer

Report Builder

Report Designer

Report Manager

SQL Mgmt Studio

Config Tool

SQL Server Reporting Services

7

Agenda

• Service Architecture

• Management Tools Update

• Scale & Memory

• Deployment & Upgrade

8

Service Architecture Changes

One service architectureEasier to deploy, configure and manage

Does not depend on Internet Information Services (IIS)Report Server tools provide required IIS capabilitiesDo not need IIS on your database servers

Shares internal components with SQL ServerNetwork interfaces are shared with SQL ServerReport Server service hosts ASP.NET in SQL CLR

Does not change deployment topologiesSupports SOAP, URL Access and Report Manager Upgrades are supported

Design Goal: Don’t break “anything”!

9

Reporting Services 2005 Service Architecture

ASP.NET

IIS

Web Service

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder Report Manager

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI ProviderWindows Service

10

ASP.NET

IIS

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder Report Manager

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI ProviderWindows Service

Web Service

SOAP / URL Access

Report Processing

Extensibility

Reporting Services 2005 Service Architecture

11

Web Service

ASP.NET

IIS

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder Report Manager

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI ProviderWindows Service

Scheduling & Delivery

Database Maintenance

Report Processing

Extensibility

Reporting Services 2005 Service Architecture

12

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder

Report Manager

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI Provider

Web Service

Background Processing

Platform

Windows Service

Reporting Services 2008 Service Architecture

13

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI ProviderPlatform

Web Service

Report Manager

Background Processing

Report Processing

Scheduling & Delivery

Database Maintenance

Extensibility

Reporting Services 2008 Service Architecture

14

3rd Party ApplicationsReport Designer

Management Studio

Configuration Tool

Report Builder

SharePoint V3

Report Server Database

Configuration Files

Report Data Sources

SharePoint V3 Databases

WMI

WMI Provider

Web Service

Report Manager

Background Processing

Platform

Service Network Interface Service Platform

ASP.NET

Authentication

App Domain Management

Memory Management

Reporting Services 2008 Service Architecture

15

Networking Capabilities

SSRS is an Application server that does reportingIt is not a general purpose web server

Report Server supports HTTP and SSL Specific IP address, host headers, multiple portsSSL certificates and usageSecurity modes

NTLM, Kerberos, Negotiate, Basic, Custom

Tools support is provided for common settingsConfiguration tool is updated WMI API Support for setting URLs, SSL bindings

SSRS has a W3C compliant HTTP request logCan be analyzed using standard IIS tools

16

Report Server & IIS Coexistence

IIS is not required for RS to functionReport Server & IIS 6+ use HTTP.SYS

Single network stack built into WindowsRS & IIS can share a single portException: Windows XP 32-bit due to IIS 5.1

HTTP.SYS sends requests to Report ServerRS makes explicit URL Reservations for is Virtual DirectoriesIIS gets all requests not covered by explicit URL Reservations

SPN conflicts are possibleSolved by running RS under the Network Service accountUse only NTLM rather than Negotiate or KerberosConfigure host headers and specific SPNs

17

URL Reservations

URL Reservations (“URLs”) replace IIS Web SitesUsed to direct requests to a particular serviceDefine the protocol, URL String, port, and virtual directory

Multiple URL Reservations are allowedIndependently specified for Report Server, Report ManagerRecommend using identical URLs for both

Virtual Directory is simplifiedIn IIS, Virtual Directories have many settingsIn Reporting Services 2008, Virtual Directory is just a nameOnly one virtual directory name is allowed for Report Server or Report Manager

18

Deployment Differences

Some IIS functionality is not providedAnonymous and Digest authentication Client certificatesHosting application domains in SSRS

Programmatically processing requestsUse ASP.NET HTTP Modules

ISAPIs are not supportedMost impacted are Single Sign-On (SSO) solutionsISA/Intelligent Application Gateway server is the recommended solution

19

Agenda

• Service Architecture

• Management Tools Update

• Scale & Memory

• Deployment & Upgrade

20

Reporting Services Configuration Manager

SSRS Configuration ToolPurpose: Help configure Report ServerChanges to reflect IIS removal

Set URLs with VDIRsUsability enhancements

Database WizardSet SharePoint mode at creation

21

SQL Server Management Studio

SQL Server Management StudioPurpose: Administer report server with focus on Admins.No namespace management

Removed home nodeAdded SharePoint server mode supportAdded job management Added system propertiesAdded enable/disable features

22

Report Manager

SSRS Report Manager Purpose: Default tool to view and manage reports with focus on end users.Hosted inside the report server process Moved server management to SSMSAdded Model management

Model click-throughModel item security

23

SharePoint Add-in

SSRS SharePoint Add-in Purpose: Reports Management via SharePoint UI when report server is setup in SharePoint modeSupports in-place upgrade of 2005 SP2 Add-inAdded Data Driven SubscriptionsAdded support for URL parameters

24

Management Tools

Ron van MoorselBusiness Intelligence ConsultantProphesy Ltd

demo

25

Agenda

• Service Architecture

• Management Tools Updated

• Scale & Memory

• Deployment & Upgrade

26

Report Engine Scalability2008 Report Engine design goals

Scale to any size report (millions of rows!)Reports are not memory boundMany simultaneous report executionsSmall reports are not starved by large reports

Design ChangesOn demand processing engineCursor-based Rendering Object ModelRendering re-architectureRewritten renderers

2008 reduces workload and memory footprintReport items are calculated on demand Renders only the pages requested by usersData sets no longer must be entirely in memory

27

Page Response Time Implications

0 2000

500

Response Time (Large Table with Constant Row Visibility)

RS2005 SP1

RS2008 CTP6

Page Number

Resp

onse

Tim

e [m

s]

0 200 4000

500

1000

Response Time (Table With Grouping)

RS2005 SP1RS 2008 CTP6

Page Number

Resp

nse

Tim

e [m

s]

TableMatrix

ListChartG

rou

p

Calcs

Storage

Data

ArchitectureSQL Server

2005SQL Server

2008

Tablix

Winforms

Webforms

CSV

XML

PDF

Image

ROM

HTML

Image

HTML

Excel

Print

Image

Hard PageLayout

Data

Soft PageLayout

Chart

30

Memory Management

Server infrastructure for process memory monitoringDynamic, self-managing with memory pressureReduces throughput in memory pressure situations

File system cache Used to adapt to memory pressureUsed when processing receives memory events from server

Administrator is able to set targets (Min, Max)Minimum threshold defines the amount of memory the server thinks ‘belongs’ to itThe memory is used only if a request needs itMaximum threshold defines the not to exceed value

Adapts to other processes consuming memory

31

Memory ImplicationsApp Domain recycle stops report execution

2005 Simulated

2008 Real

32

Memory and IO Implications

2008 Real

34

Agenda

• Service Architecture

• Management Tools Updated

• Scale & Memory

• Deployment & Upgrade

35

What is supported in RS 2008Usage Scenario Support Statement

Applications built for Reporting Services 2005 Supported

Applications built for Reporting Services 2000 • 2000 SOAP APIs are not supported• URL Access supported

RS Database hosted in SQL Server 2008 Supported

RS Database hosted in SQL Server 2005 Supported

RS Database hosted in SQL Server 2000 Not supported

SharePoint integration New Reporting Services 2008 SharePoint Add-in is provided

SharePoint v2 Web Parts Supported

Single Server deployment Supported

Multi-Server deployment Supported – Reporting Services on computer 1 and RSDB on computer 2

Scale-out deployment Supported

36

Upgrading to Reporting Services 2008

Support for upgrading from SSRS 2000 or SSRS 2005Upgrade moves required settings out of IIS and creates URL reservationsNot supported scenarios will BLOCK upgradeReport server database auto upgrade

RSDB schema is upgraded on the fly Executed by ReportServer service

Pre-published reports upgrade on the flyOccurs on first use by a user

Side-by-side with SSRS 2008 supported

37

Programmatic Interface Changes

SOAP Namespaces Versions have not changedOld clients continue to workNew methods have been added inline

Revised RDL schemaBetter Charts supportTablix structure replaces Table, Matrix, List

WMI Namespaces Changed to work better across versions2005 WMI namespace is not supported

New Rendering Object ModelNot backward-compatible with SQL 2005 ROMNew 2008 CRI interface; 2005 CRI interface is supported

39

Resources

www.microsoft.com/teched Tech·Talks Tech·Ed BloggersLive Simulcasts Virtual Labs

http://microsoft.com/technet

Evaluation licenses, pre-released products, and MORE!

http://microsoft.com/msdn

Developer’s Kit, Licenses, and MORE!

Related Content

Breakout Session

•BIN252 Report Authoring with Microsoft SQL Server Reporting Services 2008

Hands-on Labs

•Sunday 31st August 2:00pm – 9:30pm •Monday 1st September 10:00am – 8:00pm•Tuesday 2nd September 8:00am – 6:30pm•Wednesday 3rd September 8:00am – 6:30pm

Microsoft Business IntelligenceResourcesBI Conference

October 6-9, 2008 in Seattle, WashingtonRegistration opened in June 200863 breakout sessions, 40 chalk talks, 20+ hands on labsMore information at http://www.microsoft.com/bi/conference/

All Content and Informationhttp://www.microsoft.com/biProduct Training, Labs, Demos, White Papers, Webcasts and Product DemosBlogs and CommunitiesCustomer Evidence and Case Studies

Business Intelligence NewsletterPublished quarterlySubscribe: http://www.microsoft.com/bi/about/newsletter.aspx

42

Please complete anevaluation

43

© 2008 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.

44

Q & A