Which Of The Following Is Not Included In The Reporting Services Architecture

In this commodity, we will explore SQL Server Reporting Services (also known as SSRS) architecture and its main components. Nosotros will also discuss SSRS extensions and related tasks. Additionally, we volition talk over the Report Server and Report Server temporary databases.

I want to make ane signal before starting this article, all the contents and instructions of this article are created according to SSRS 2017 Native Mode version. SSRS has some other deployment option which is Share Point fashion, so that it tin run with Share Point, but Microsoft announced the following note "Reporting Services integration with SharePoint is no longer available subsequently SQL Server 2016" and for this reason we will non mention about this pick.

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 exist used to manage and publish enrichment and interactive reports, key performance indicators, datasets and mobile reports.

SQL Server Reporting Services was first published every bit an add-in with SQL Server 2000 and so Microsoft added and is nevertheless adding new features to all version of SQL SERVER REPORTING SERVICES. I desire to give a very clear example co-ordinate to my feel which is well-nigh 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 (Net 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 nosotros desire to develop and publish reports over SQL SERVER REPORTING SERVICES, we crave three different components:

Report Server
Report Server and Report Server temporary database
Written report Builder

Now, we will explore these tools.

Report Server:

The Report Server is the cadre engine of the SSRS. Namely, nosotros tin remember 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 nosotros can find out this service under the Services Manager.

SSRS is based on a three-tier architecture and these tiers are Middle-Tier, Information-Tier, and Presentation-Tier. Now, we will explicate these tiers functionalities.

Middle-Tier: The Eye 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 plenty to run SQL SERVER REPORTING SERVICES.

The SSRS architecture diagram below exposes the bones 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 tin can also be connected to each other. In addition to the usefulness of the the extension architecture, it allows united states of america to create and use custom extensions.

For example, our objective may exist to upload our reports to any ftp path. The get-go question that comes to our heed is, how tin can nosotros accomplish this? We can develop a custom delivery extension and then that the delivery extension would acquire a new adequacy. Now, nosotros volition explicate details and tasks of these extensions:

Security Extension: The Security extension provides united states of america with the power to perform user or group hallmark and authorization operations. SSRS uses windows-based authentication extension in the default.

Information Processing Extension: The Data processing extension provides u.s.a. 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 so retrieves data. Nosotros can find out the default data processing extensions in the SSRS written 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 nether 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> annotation and then we tin can see the default list of data processing extensions and their types.

SQL
SQLAZURE
SQLPDW
OLEDB-Doc
SHAREPOINTLIST
ORACLE
ESSBASE
SAPBW
TERADATA
OLEDB
ODBC
XML

As we already noted virtually custom extensions, we tin develop and implement custom data sources which are non supported past SQL SERVER REPORTING SERVICES and also nosotros tin extend the capability of the existing data sources.

Rendering Extension: This extension converts data and report components to a readable format. The post-obit extension can be used by default in SSRS.

HTML Rendering Extension
Excel Rendering Extension
CSV Rendering Extension
XML Rendering Extension
Paradigm Rendering Extension
PDF Rendering Extension
Give-and-take Rendering Extension
Power Signal Rendering Extension
Atom Service Certificate Rendering Extension

Delivery Extensions: The primary responsibility of the delivery extension is to transfer reports through the specified method. By default, this extension involves 2 different extensions;

Study Server FileShare extension allows us to save reports to defined binder.

Report Server post extension sends mail of the reports through Elementary Mail Transport Protocol (SMTP).

Data-Tier: Reporting server database and data sources interactions exist in this tier.

Presentation-Tier: The client applications and born or custom tools are placed in the presentation tier. This tier involves web service, web portal, reporting service configuration manager, written report designer.

ReportServer Databases:

Every SQL Server Reporting Services deployment requires two databases. These are the ReportServer and ReportServerTempDB databases. These databases options can exist changed through SQL Server Reporting Service Configuration Manager. The ReportServer database isa vital part of SSRS because it stores various information and setting of the SSRS. Some of these are:

Users and Roles
Subscriptions
Notifications
Written report definitions and reports metadata
Dataset and Datasource source informations
Execution Logs
Report Comments

Equally an case, the ConfigurationInfo tabular array stores the all configuration value of the SQL SERVER REPORTING SERVICES.

select * from ConfigurationInfo

SSRS stores the database connection settings and credentials in the RSreportserver.config file in an encrypted format.

We can alter these settings in 2 different means. Starting time, we can employ the SSRS configuration managing director and 2d, the rsconfig utility. The Rsconfig Utility allows united states to configure report server connectedness 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 marker (?) 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 volition change database connections to SQL server authentication considering we volition gear up authmethod (-a) parameter as SQL.

rsconfig -c -due south <SQLServerName/SQLServerIP> -d <ReportServerDatabase> -a SQL -u <SQL_USER> -p <PassWord>

It is non recommended to directly change information from this database because information technology tin cause several problems. Another consideration about this database is backups; nosotros must take ReportServer database backup regularly.

The Report Server temporary database stores temporary objects such as execution cache, content cache and other temporary information stores in this database.

SSRS Written report Builder

The Study Architect is a light-weight written report designer tool which helps to design and deploy SQL server reporting service reports. Nosotros have two options to blueprint and deploy reports to SSRS. These are SQL Server Data Tools (SSDT) and Microsoft SQL Server Report Builder. However, the SSRS study builder has much usage capability then SSDT and for this reason several study writers prefer that. The Report builder offers some benefits to u.s.a. which are:

Like shooting fish in a barrel and stand-solitary installation
Unproblematic usage and not require much technical noesis
Basic learning
Advanced toolbox support (Table,Matrix,Charts,Map)
Allow for implementation of a self-service BI approach

Writer
Contempo Posts

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Good.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business organisation Intelligence. You lot can find him on LinkedIn.

View all posts past Esat Erkec