SQL Server Reporting Services体系结构和组件拓扑

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.

在本文中，我们将探讨SQL Server Reporting Services（也称为SSRS）体系结构及其主要组件。 我们还将讨论SSRS扩展和相关任务。 此外，我们将讨论报表服务器和报表服务器临时数据库。

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.

在开始本文之前，我想指出一点，本文的所有内容和说明均根据SSRS 2017纯模式版本创建。 SSRS还有另一个部署选项，即共享点模式，因此它可以与共享点一起运行，但是Microsoft宣布了以**释“ SQL Server 2016之后不再提供与SharePoint的Reporting Services集成”，因此，我们不再赘述此选项。

什么是SQL Server Reporting Services？ (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是一个报告平台，使我们能够部署，发布，计划和管理报告。 SSRS可用于管理和发布充实和交互式报告，关键绩效指标，数据集和移动报告。

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.

SQL Server Reporting Services最初是作为SQL Server 2000的外接程序发布的，然后Microsoft添加了，并且仍在向所有版本SQL SERVER REPORTING SERVICES添加新功能。 我想根据我的经验给出一个非常清晰的示例，该示例涉及此功能增强。 当我是一名初级开发人员时，我将SSRS与SQL Server 2000一起使用，并且在那时，SSRS与IIS（Internet信息服务）一起使用，然后在SSRS 2008版本中避免了这种IIS依赖性，并开始使用HTTP.sys 。 结果，SSRS经历了重大转变。 SQL Server移动报表是另一个关键改进的好例子。

If we want to develop and publish reports over SQL SERVER REPORTING SERVICES, we require three different components:

如果我们要通过SQL SERVER REPORTING SERVICES开发和发布报告，则需要三个不同的组件：

• 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的核心引擎。 即，我们可以将报表服务器引擎视为SSRS的引导者，因为所有流程，任务和请求都由报表服务器处理。 报表服务器作为Microsoft Windows服务安装，因此我们可以在服务管理器中找到此服务。

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.

SSRS基于三层体系结构，这些层是“中间层”，“数据层”和“演示层”。 现在，我们将解释这些层的功能。

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.

中间层：中间层涉及Windows服务组件和扩展，扩展在该层下进行管理。 报表服务器支持以下类型的扩展：安全扩展，数据处理扩展，报表处理扩展，呈现扩展和交付扩展，但是安全性，数据处理和呈现扩展足以运行SQL SERVER REPORTING SERVICES。

The SSRS architecture diagram below exposes the basic architecture of the SQL SERVER REPORTING SERVICES.

下面的SSRS体系结构图展示了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.

从上面的SSRS体系结构图中可以看到，Report Server在中间层包括各种扩展，每个扩展执行不同的任务，并且这些扩展也可以彼此连接。 除了扩展架构的有用性之外，它还允许我们创建和使用自定义扩展。

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:

例如，我们的目标可能是将报告上传到任何ftp路径。 我们想到的第一个问题是，我们如何实现这一目标？ 我们可以开发定制的交付扩展，以便交付扩展将获得新功能。 现在，我们将解释这些扩展的细节和任务：

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.

安全 扩展 ：安全扩展使我们能够执行用户或组的身份验证和授权操作。 默认情况下，SSRS使用基于Windows的身份验证扩展。

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.

数据 处理 扩展 ：数据处理扩展使我们能够从指定的数据源获取数据。 换句话说，数据处理扩展连接并与指定的数据源通信，然后检索数据。 我们可以在SSRS报表服务器配置文件RSreportserver.config中找到默认的数据处理扩展名。 RSreportserver.config文件以XML格式文件存储SSRS的设置。

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.

在默认安装的SSRS中，此文件位于“ C：\ Program Files \ Microsoft SQL Server Reporting Services \ SSRS \ ReportServer”文件路径下。 现在，我们将在记事本中打开该文件并找到<Data>注释，然后我们可以看到数据处理扩展及其类型的默认列表。

• SQL
  SQL
• SQLAZURE
  SQLAZURE
• SQLPDW
  SQLPDW
• OLEDB-MD
  OLEDB-MD
• SHAREPOINTLIST
  SHAREPOINTLIST
• ORACLE
  Oracle
• ESSBASE
  ESSBASE
• SAPBW
  SAPBW
• TERADATA
  海量数据
• OLEDB
  OLEDB
• ODBC
  ODBC
• XML
  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.

正如我们已经提到的有关自定义扩展的内容一样，我们可以开发和实现SQL SERVER REPORTING SERVICES不支持的自定义数据源，并且我们可以扩展现有数据源的功能。

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

渲染 扩展 ：此扩展将数据和报表组件转换为可读格式。 在SSRS中，默认情况下可以使用以下扩展名。

• HTML Rendering Extension
  HTML渲染扩展
• Excel Rendering Extension
  Excel渲染扩展
• CSV Rendering Extension
  CSV渲染扩展
• XML Rendering Extension
  XML呈现扩展
• Image Rendering Extension
  图像渲染扩展
• PDF Rendering Extension
  PDF渲染扩展
• Word Rendering Extension
  文字翻译扩展
• Power Point Rendering Extension
  Power Point渲染扩展
• Atom Service Document Rendering Extension
  Atom服务文档渲染扩展

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.

报表服务器FileShare扩展名允许我们将报表保存到定义的文件夹。

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

报表服务器邮件扩展名通过简单邮件传输协议（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.

演示层： 客户端应用程序和内置或自定义工具放置在表示层中。 该层涉及Web服务，Web门户，报告服务配置管理器，报告设计器。

ReportServer数据库： (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:

每个SQL Server Reporting Services部署都需要两个数据库。 这些是ReportServer和ReportServerTempDB数据库。 可以通过SQL Server Reporting Service配置管理器更改这些数据库选项。 ReportServer数据库是SSRS的重要组成部分，因为它存储了各种数据和SSRS的设置。 其中一些是：

• 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.

例如，ConfigurationInfo表存储SQL SERVER REPORTING SERVICES的所有配置值。

select * from ConfigurationInfo

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

SSRS以加密格式将数据库连接设置和凭据存储在RSreportserver.config文件中。

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.

我们可以通过两种不同的方式更改这些设置。 首先，我们可以使用SSRS配置管理器，其次，可以使用rsconfig实用程序。 Rsconfig实用程序允许我们在命令提示符下配置报表服务器连接设置。 Rsconfig实用程序位于“ C：\ Program Files \ Microsoft SQL Server Reporting Services \ Shared Tools”路径中。 当在命令提示符下运行带有问号（？）参数的rsconfig时，我们可以找到所有开关参数和说明。

rsconfig -?

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.

根据这些参数，我们将在RSreportserver.config文件中更改数据库连接设置和凭据。 以下脚本将数据库连接更改为SQL Server身份验证，因为我们将authmethod（-a）参数设置为SQL。

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

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.

不建议直接更改此数据库中的数据，因为它可能会导致一些问题。 关于此数据库的另一个考虑因素是备份； 我们必须定期进行ReportServer数据库备份。

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

报表服务器临时数据库在此数据库中存储临时对象，例如执行缓存，内容缓存和其他临时数据存储。

SSRS报表生成器 (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:

报表生成器是一种轻量级的报表设计器工具，可帮助设计和部署SQL Server报表服务报表。 我们有两种选择来设计报告并将其部署到SSRS。 这些是SQL Server数据工具（SSDT）和Microsoft SQL Server报表生成器。 但是，SSRS报表生成器比SSDT具有更多的使用功能，因此，一些报表编写者更喜欢这样做。 报表构建器为我们提供了一些好处，包括：

• 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
  允许实施自助服务BI方法

翻译自: https://www.sqlshack.com/sql-server-reporting-services-architecture-and-component-topology/