In this article, we will explore SQL Server Reporting Services (also known as SSRS) architecture and its main components. We will also discuss SSRS extensions and related tasks. Additionally, we will discuss the Report Server and Report Server temporary databases.
I want to make one point before starting this article, all the contents and instructions of this article are created according to SSRS 2017 Native Mode version. SSRS has another deployment option which is Share Point mode, so that it can run with Share Point, but Microsoft announced the following note “Reporting Services integration with SharePoint is no longer available after SQL Server 2016” and for this reason we will not mention about this option.
What is SQL Server Reporting Services?
SQL Server Reporting Services is a reporting platform which allows us to deploy, publish, schedule and manage reports. SSRS can be used to manage and publish enrichment and interactive reports, key performance indicators, datasets and mobile reports.
SQL Server Reporting Services was first published as an add-in with SQL Server 2000 and then Microsoft added and is still adding new features to all version of SQL SERVER REPORTING SERVICES. I want to give a very clear example according to my experience which is about this feature enhancement. When I was a junior developer, I used SSRS with SQL Server 2000 and in those days SSRS was working with IIS (Internet Information Service) and then it avoided this IIS dependency in the SSRS 2008 version and started to use HTTP.sys. As a result, SSRS experienced a significant transformation. SQL Server Mobile Reports is another good example of a key improvement.
If we want to develop and publish reports over SQL SERVER REPORTING SERVICES, we require three different components:
- Report Server
- Report Server and Report Server temporary database
- Report Builder
Now, we will explore these tools.
Report Server:
The Report Server is the core engine of the SSRS. Namely, we can think of the report server engine as a conductor of SSRS, because all processes, tasks and requests are handled by the report server. The Report server is installed as a Microsoft Windows Service so that we can find out this service under the Services Manager.
SSRS is based on a three-tier architecture and these tiers are Middle-Tier, Data-Tier, and Presentation-Tier. Now, we will explain these tiers functionalities.
Middle-Tier: The Middle tier involves windows service components and extensions and extensions are managed under the tier. The report server supports the following types of extensions: security extensions, data processing extensions, report processing extensions, rendering extensions, and delivery extensions however security, data processing and rendering extensions are enough to run SQL SERVER REPORTING SERVICES.
The SSRS architecture diagram below exposes the basic architecture of the SQL SERVER REPORTING SERVICES.
As you can see in the above SSRS architecture diagram, the Report Server includes various extensions in the middle-tier and each extension performs different tasks and these extensions can also be connected to each other. In addition to the usefulness of the the extension architecture, it allows us to create and use custom extensions.
For example, our objective may be to upload our reports to any ftp path. The first question that comes to our mind is, how can we achieve this? We can develop a custom delivery extension so that the delivery extension would acquire a new capability. Now, we will explain details and tasks of these extensions:
Security Extension: The Security extension provides us with the ability to perform user or group authentication and authorization operations. SSRS uses windows-based authentication extension in the default.
Data Processing Extension: The Data processing extension provides us the ability to get data from the specified data source. In other words, the data processing extension connects and communicates with the specified data source then retrieves data. We can find out the default data processing extensions in the SSRS report server configuration file, RSreportserver.config. The RSreportserver.config file stores the settings of SSRS in an XML format file.
In the default installation of SSRS, this file is placed under the “C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer” file path. Now, we will open this file in notepad and find the <Data> note and then we can see the default list of data processing extensions and their types.
- SQL
- SQLAZURE
- SQLPDW
- OLEDB-MD
- SHAREPOINTLIST
- ORACLE
- ESSBASE
- SAPBW
- TERADATA
- OLEDB
- ODBC
- XML
As we already noted about custom extensions, we can develop and implement custom data sources which are not supported by SQL SERVER REPORTING SERVICES and also we can extend the capability of the existing data sources.
Rendering Extension: This extension converts data and report components to a readable format. The following extension can be used by default in SSRS.
- HTML Rendering Extension
- Excel Rendering Extension
- CSV Rendering Extension
- XML Rendering Extension
- Image Rendering Extension
- PDF Rendering Extension
- Word Rendering Extension
- Power Point Rendering Extension
- Atom Service Document Rendering Extension
Delivery Extensions: The main responsibility of the delivery extension is to transfer reports through the specified method. By default, this extension involves two different extensions;
Report Server FileShare extension allows us to save reports to defined folder.
Report Server mail extension sends mail of the reports through Simple Mail Transport Protocol (SMTP).
Data-Tier: Reporting server database and data sources interactions exist in this tier.
Presentation-Tier: The client applications and built-in or custom tools are placed in the presentation tier. This tier involves web service, web portal, reporting service configuration manager, report designer.
ReportServer Databases:
Every SQL Server Reporting Services deployment requires two databases. These are the ReportServer and ReportServerTempDB databases. These databases options can be changed through SQL Server Reporting Service Configuration Manager. The ReportServer database isa vital part of SSRS because it stores various data and setting of the SSRS. Some of these are:
- Users and Roles
- Subscriptions
- Notifications
- Report definitions and reports metadata
- Dataset and Datasource source informations
- Execution Logs
- Report Comments
As an example, the ConfigurationInfo table stores the all configuration value of the SQL SERVER REPORTING SERVICES.
|
1 |
select * from ConfigurationInfo |
SSRS stores the database connection settings and credentials in the RSreportserver.config file in an encrypted format.
We can change these settings in two different ways. First, we can use the SSRS configuration manager and second, the rsconfig utility. The Rsconfig Utility allows us to configure report server connection settings in command prompt. The Rsconfig utility is located in the “C:\Program Files\Microsoft SQL Server Reporting Services\Shared Tools” path. When we run rsconfig with a question mark (?) parameter in command prompt, we can find out all switch parameters and descriptions.
rsconfig -?
According to these parameters, we will change the database connection setting and credentials in the RSreportserver.config file. The following script will change database connections to SQL server authentication because we will set authmethod (-a) parameter as SQL.
rsconfig -c -s <SQLServerName/SQLServerIP> -d <ReportServerDatabase> -a SQL -u <SQL_USER> -p <PassWord>
It is not recommended to directly change data from this database because it can cause several problems. Another consideration about this database is backups; we must take ReportServer database backup regularly.
The Report Server temporary database stores temporary objects such as execution cache, content cache and other temporary data stores in this database.
SSRS Report Builder
The Report Builder is a light-weight report designer tool which helps to design and deploy SQL server reporting service reports. We have two options to design and deploy reports to SSRS. These are SQL Server Data Tools (SSDT) and Microsoft SQL Server Report Builder. However, the SSRS report builder has much usage capability then SSDT and for this reason several report writers prefer that. The Report builder offers some benefits to us which are:
- Easy and stand-alone installation
- Simple usage and not require much technical knowledge
- Basic learning
- Advanced toolbox support (Table,Matrix,Charts,Map)
- Allow for implementation of a self-service BI approach
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023