Introduction to:
SQL Server Reporting Services
Architecture, Installation,
About Me
• Bret Stateham
• Owner of Net Connex
• Consulting and Training Services • Working with SQL Server since v6.5 • Teaching it SQL Server since v7.0 • Contact Me: – Blog: blogs.netconnex.com – Twitter: @Bstateham – Email: [email protected] • Glad to be here! gettag.mobi
Agenda
• Introduction to SQL Server Reporting Services • Architecture
• Installation
Introduction
• What is SQL Server Reporting Services • History of SQL Server Reporting Services • Reporting Lifecycle Demonstration
“SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface.
Reporting services features a web services interface to support the development of custom reporting
applications.
SSRS competes with Crystal Reports and other business intelligence tools, and is included in Express, Workgroup,
Standard, and Enterprise editions of Microsoft SQL Server
as an install option…”
“SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface.
Reporting services features a web services interface to
support the development of custom reporting applications.”
“SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface.”
“SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports.”
“SQL Server Reporting Services (SSRS) is a server-based
report generation software system from Microsoft.”
“SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface.
Reporting services features a web services interface to support the development of custom reporting
applications.
SSRS competes with Crystal Reports and other business intelligence tools, and is included in Express, Workgroup,
Standard, and Enterprise editions of Microsoft SQL Server
as an install option…”
History of SSRS
• Originally Released in 2004 as a web release for SQL Server 2000
• Included as part of the product in SQL Server 2005 with many enhancements
– Report Models and Report Builder – Multi-Valued Parameters
– Printing Support
• Enhanced again for inclusion in the SQL Server 2008 release
– New “Tablix” implementation of report elements – Inclusion of Dundas charting and gauge components – Out of the box support for SharePoint integration – New & Improved Report Builder 2.0
The Reporting Lifecycle
• Authoring
– Reports syntax is Report Definition Language (RDL) – Included report authoring tools include:
• SQL Server Business Intelligence Development Studio • Report Builder 1.0 and Report Builder 2.0
• Management
– Report Manager web interface
– Role Base security model based on Windows Authentication – Control over “Execution” (When reports get their data)
• Delivery
– Web based delivery via Report Manager web site – Subscriptions allow for automated report delivery – URL Access, Web Services and Report Viewer control
Architecture
• SQL Server Requirements • Web Server Requirements • Architecture Overview
SQL Server Requirements
• SSRS is included with all editions of SQL Server including Express
• Features grow with the Edition (link)
– Remote Data Sources – Subscriptions
– Memory Limits
• SSRS stores its own data in SQL Server DBs:
– ReportServer
Web Server Requirements
• IIS is required for SQL 2000 and 2005 • SSRS 2008 no longer uses IIS
– Registers its URLs with the HTTP.SYS kernel driver – Simplifies install of SSRS
Architecture Overview
SQL Server Windows Server SQL Oracle Other Users Running IE Admins Developers ReportServer ReportServerTempDB Reporting ServicesIIS / HTTP.SYS /Reports
SSRS Databases
• ReportServer
– Stores all the reporting services assets – Reports
– Datasources – Security
– Snapshots & Histories
– Schedules & Subscriptions
• ReportServerTempDB
– Stores temporary “Intermediate Reports”
• Sizes of the DBs depends on
– Number of Reports – Execution Properties
Reporting Services Installation
• Installs as part of the SQL Server Install • Can choose from:
– Default Configuration – SharePoint Integrated – Un-Configured
SSRS Administration
• Administration Tools
• Reporting Services Configuration
• Managing Report Execution & Subscriptions • Reporting Services Security
• Backup And Restore
Administration Tools
• Report Manager Web Site
• SQL Server Management Studio • Command Line Tools
• Reporting Services Configuration Manager • WMI
Reporting Services Configuration
• .Net Configuration Files
– C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER
Managing Report Execution
• “Execution” is when Reporting Services gets the data for a report
• Reporting Services caches report data • Caching options include
– Temporary caches stored in Report ServerTempDB
• Session Level • Shared
– “Permanent” caches stored in ReportServer DB – Snapshots
Subscriptions
• Deliver reports to a destination • Can deliver to
– File Share – Extendable
• Can be created by the user or an admin • Data driven subscriptions
– Enterprise Edition only
Report Execution & Subscriptions
Demo
SSRS Security
• Defaults to Windows Authentication • Can use Forms Authentication
• Uses “Roles” to configure security • Item level granularity
SSRS Backup and Restore
• Normal SQL Server Database Backups
– ReportServer Database: As often as possible – ReportServerTempDB: Post Install / SP
• Reporting Services Encryption Key
– Use Configuration Manager
– Rskeymgmt.exe command line tool – Do post install, or after changing
Summary
• Introduction to SQL Server Reporting Services • Architecture
• Installation
