SQL Server Reporting Services Architecture

SQL Server Reporting Services is a Microsoft software system that you can use to create, manage, and deploy reports. SSRS includes three main components, which represent a server layer, a metadata layer, and an application layer, respectively:

  • Reporting Services Windows service
  • Report Catalog
  • Reporting Services web portal

NOTE The first two components are described in the following sections, after a brief introduction to the Report Definition Language. The Reporting Services web portal is, since SQL Server 2016, a component that replaces Report Manager and significantly extends its functionality. The Reporting Services web portal will be discussed in detail later in this chapter.

When the information concerning data definition and report layout is gathered, SSRS stores it using the Report Definition Language (RDL). RDL is an XML-based language that is used exclusively for storing report definitions and layouts. RDL is an open schema language, meaning that developers can extend the language with additional XML attributes and elements. (For descriptions of XML in general and XML elements and attributes in particular, see Microsoft Docs.) RDL is usually generated via Visual Studio, although it may also be used programmatically.

A typical RDL file contains three main sections. The first concerns page style, the second section specifies field definitions, and the last section defines parameters.

NOTE It is not necessary for you to understand the details concerning RDL to develop reports. The language is important only when you want to create your own RDL files.

1. Reporting Services Windows Service

As its name implies, the Reporting Services Windows service is implemented as a Windows component that comprises two important services in relation to the report server. The first one, the Reporting Services Web service, is the application used for the implementation of the SSRS web site, while the second one, the Reporting Services Windows service, allows you to use it as a programmatic interface for reports.

NOTE Both services, the Reporting Services Windows service and the Reporting Services Web service, work together and constitute a single report server instance.

As you can see from Figure 25-1, Reporting Services Windows service includes the following components:

  • Report processor
  • Data providers
  • Renderers
  • Request handler

The report processor manages the execution of a report. It retrieves the definition of the report, which is done in RDL, and determines what is needed for the report. Also, it manages the work of other components that are used to produce a report. The report processor also retrieves data from data sources. After that, it selects a data provider that knows how to extract information from the data source.

SQL Server Reporting Services includes a set of renderers for exporting reports to different formats. Each renderer applies rules when rendering reports. When you export a report to a different file format, especially for renderers such as the Adobe Acrobat (PDF) renderer, you might need to change the layout of your report to have the exported report look and print correctly after the rendering rules are applied.

The task of the data providers is to connect to the data source, get the information for the report, and return it to the processor in the form of corresponding datasets. After that, the report processor turns this information into a dataset, to be used by the report. When data providers deliver the data for the report, the report processor can begin to process the report’s layout. To render the report, the processor has to know its format (HTML or PDF, for instance).

The request handler receives requests for reports and sends them to the report processor. It also delivers the completed report. (The different forms of report delivery will be discussed later in the chapter.)

2. Report Catalog

The Report Catalog contains two databases that are used to store the definitions of all existing reports that belong to a particular service. The stored information includes report names, descriptions, data source connection information, credential information, parameters, and execution properties. The Report Catalog also stores security settings and information concerning scheduling and delivering data.

SSRS uses two databases, the Report Server database and the Report Server temporary database, to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are reportserver and reportservertempdb, respectively. The former is used to store the Report Catalog, while the latter is used as temporary storage for cached reports and the work tables that they generate. If you do not want to use the default names for these databases, you can specify different names in the configuration process.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *