扩展Reporting Services和SQL Server 2016中的更改

最新推荐文章于 2021-03-02 14:09:33 发布

culuo4781

最新推荐文章于 2021-03-02 14:09:33 发布

阅读量851

点赞数 1

文章标签：数据库 java 大数据 linux python

原文链接：https://www.sqlshack.com/scaling-out-reporting-services-changes-in-sql-server-2016/

版权

Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL 2016 with next to no guidance from Microsoft on the changes.

将Reporting Services扩展到多个节点本身是一个相对简单的过程。当我们开始解决问题并研究性能时，我们开始发现在幕后还存在着很多尚无法通过实施实现的事情。 SQL 2016中也有一些基本元素发生了变化，Microsoft对此变化几乎没有指导。

计划你的方法 (Plan your approach)

There’s always several ways to do things & that’s no different with scaling out SSRS. In my opinion, the best approach is to have SSRS on 2-3 servers, not sharing any servers with the DB engine or other component (SSAS or SSIS etc.). These would then all sit behind a hardware based Network load balancer (NLB) such as an F5. This gives you one URL that feeds users into all your SSRS “nodes”. The users never have visibility of server names & you can easily scale servers in or out, more so, if you’re using virtual technology.

总是有几种方法可以做到这一点，并且与扩展SSRS一样。我认为，最好的方法是在2-3台服务器上使用SSRS，而不与数据库引擎或其他组件（SSAS或SSIS等）共享任何服务器。这些都将放在基于硬件的网络负载平衡器（NLB）（例如F5）后面。这为您提供了一个URL，可将用户送入您的所有SSRS“节点”。用户永远不会看到服务器名称，如果您使用的是虚拟技术，则可以轻松地扩展或扩展服务器。

The problem with this approach and one that is solved easily (as detailed on MSDN) is “View State Validation” which I’ll go into in greater detail later. You can, of course, achieve a scale out deployment without an NLB. You can also have each of your SSRS instances share a server with other applications or SQL components. The number of users you have & the report executions you see will usually dictate the setup you require.

这种方法的问题很容易解决（在MSDN上进行了详细介绍）是“视图状态验证”，我将在后面详细介绍。当然，您可以在没有NLB的情况下实现横向扩展部署。您还可以让每个SSRS实例与其他应用程序或SQL组件共享服务器。您拥有的用户数量和看到的报告执行通常将决定您需要的设置。

发牌 (Licensing)

The process of setting up a scaled out SSRS deployment is near identical from 2008 R2 through to 2014. So don’t worry if some screenshots look slightly different to your setup. Ill also explain what’s changed in 2016 at the end.

从2008 R2到2014年，建立扩展SSRS部署的过程几乎相同。因此，不必担心某些屏幕截图与设置略有不同。生病还解释了2016年末的变化。

One thing I do need to point out is that the Scale out deployment feature isn’t available to all versions. In both SQL 2012 & 2014 you need to have Enterprise or Business Intelligence Edition.

我需要指出的一件事是，横向扩展部署功能并非对所有版本都可用。在SQL 2012和2014中，您都需要拥有Enterprise或Business Intelligence Edition。

Features Supported by the Editions of SQL Server 2012

SQL Server 2012版本支持的功能

Features Supported by the Editions of SQL Server 2014

SQL Server 2014版本支持的功能

In SQL 2008 R2 & 2016 (as the BI edition was scraped) you will need Enterprise edition.

在SQL 2008 R2和2016（由于取消了BI版本）中，您将需要企业版。

Features Supported by the Editions of SQL Server 2008 R2

SQL Server 2008 R2版本支持的功能

For all versions above, Developer & Evaluation are also supported so you can still try these steps out if you can’t implement it in a production environment.

对于以上所有版本，开发人员和评估版也受支持，因此，如果您不能在生产环境中实现它，仍然可以尝试这些步骤。

假设条件 (Assumptions)

Ill detail the basics of scaling out SSRS & also highlight any pitfalls you may come across before going into more detail with my preferred method above. For this I’m going to use SQL Server 2014 on Windows Server 2012 R2

我将详细介绍扩展SSRS的基础知识，并突出显示您可能遇到的任何陷阱，然后再使用我上面的首选方法进行详细介绍。为此，我将在Windows Server 2012 R2上使用SQL Server 2014

Ill assume you have a SQL Server engine already set up & running. Im going to use an Active directory account (eg. Domain\SSRSService) as the service account in the SSRS nodes so this should have permissions to create DBs on the SQL engine. (DB01). The SSRS installation process is the same for all nodes & only varies at the point you create the ReportServer DB from the first node. You then simply pick the DB when attaching subsequent nodes.

假设您已经设置并正在运行SQL Server引擎。我将使用Active Directory帐户（例如Domain \ SSRSService ）作为SSRS节点中的服务帐户，因此它应具有在SQL引擎上创建数据库的权限。（ DB01 ）。所有节点的SSRS安装过程均相同，并且仅在您从第一个节点创建ReportServer DB时才有所不同。然后，您可以在附加后续节点时简单地选择数据库。

Starting with our SSRS01 server, follow these steps to install & configure each SSRS server. 2 in our case

从我们的SSRS01服务器开始，请按照以下步骤安装和配置每个SSRS服务器。在我们的案例中为2

先决条件 (Prerequisites)

The .NET Framework 3.5.1 feature is required for our SQL installation & must be installed from the Server Manager as it’s not a default feature on Windows Server 2012 R2

.NET Framework 3.5.1功能是我们SQL安装所必需的功能，并且必须从服务器管理器中安装，因为它不是Windows Server 2012 R2的默认功能

You should also add the service account we’ll use as an admin on the server, at least for now, to help with setup & configuration.

您还应该至少在目前至少在服务器上添加我们将用作管理员的服务帐户，以帮助进行设置和配置。

安装步骤 (Installation Steps)

Setup.exe as Administrator Setup.exe
New SQL Server stand-alone installation… 新建SQL Server独立安装…
Accept the pre-written default on the Product Key page
接受“产品密钥”页面上的预设默认值
Accept the 接受下一页的License Terms on the next page 许可条款
Use Microsoft Update but this is personal choice (or company dictated) 使用Microsoft Update，但这是个人选择（或公司决定）
Install Rules page, all Rules should show Passed. Rectify anything blocking the install 安装规则”页面上，所有规则应显示“通过”。纠正阻止安装的所有内容
SQL Server Feature Installation on the 安装角色”页面上选择“ Setup Role page SQL Server功能安装 ”
2. Reporting Services – Native
  报表服务-本机
4. Client Tools Connectivity
  客户端工具连接
6. Client Tools Backwards Compatibility
  客户端工具向后兼容性
8. Management tools – complete
  管理工具–完整
Default 默认 Instance Configuration for the SQL instance (there should be no other instance installed on the target server). Hit Next 实例配置 （目标服务器上不应安装其他实例）。打下一个
Server Configuration page服务器配置”页面上 enter the service account added to the Server Admin group earlier (eg. 输入之前添加到Server Admin组中的服务帐户（例如Domain\SSRSService). Domain \ SSRSService ）。
SQL Server Reporting Services SQL Server Reporting Services
Install only” – It will be the only selectable choice. 仅安装” –这将是唯一可选择的选择。
Ready to Install page & proceed through the confirmation pages to the point of installation 准备安装”页面上单击“下一步”，然后继续进行确认页面直至安装点
Once complete Close the setup program.
完成后，关闭安装程序。

SSRS配置 (SSRS Configuration)

Reporting Services Configuration Manager Reporting Services配置管理器
Go to the Web Service URL page & Hit Apply to set up the URL with default settings.
转到“ Web服务URL”页面并单击 “ 应用”以使用默认设置来设置URL。
Database tab and click 数据库选项卡，然后单击Change Database 更改数据库
This step varies depending on if this is the first SSRS server in a Scale Out deployment: 此步骤取决于是否是横向扩展部署中的第一台SSRS服务器：
2. Create a new report server database 创建新的报表服务器数据库”。
4. Choose an existing report server database 选择现有的报表服务器数据库
Current User. 当前用户”。
Test Connection & hit Next (if successful)
测试连接并点击下一步（如果成功）
2. ReportServer) ReportServer ）
4. ReportServer & language should be set to ReportServer ，语言应设置为English (United States) (or your chosen language).英语（美国）（或您选择的语言）。 This is fine, Hit Next 很好，点击下一步
Service Credentials as the Authentication Type. Hit Next. 服务凭据”保留为“身份验证类型”。点击下一步。
A summary page will show all the settings you have set. Hit Next to complete the configuration.
摘要页面将显示您已设置的所有设置。点击下一步完成配置。
Finish. 完成。
Report Manager URL tab and hit 报表管理器” URL选项卡，然后再次单击“ Apply again to accept the default settings 应用”以接受默认设置
Email Settings tab with a sender address of your choice & your mail server. 电子邮件设置”标签。
Execution Account is required as Im using kerberos authentication for all reports & elements. Im also using stored credentials within specific datasources (in Report manager) for subscriptions. 执行帐户 。我还使用特定数据源（在报表管理器中）内存储的凭证进行订阅。
Scale Out Deployment section as we’ll cover that later. 横向扩展部署”部分，稍后将进行介绍。
Encryption Keys tab select 加密密钥”选项卡中，选择“ Backup to back up a copy of the encryption key. Save this to a secure, backed up location or a version control system. 备份”以备份加密密钥的副本。将其保存到安全的备份位置或版本控制系统。
Hit Exit to complete configuration
点击退出以完成配置

配置文件编辑 (Configuration file Edits)

Here is where we need to make manual changes to configuration files to allow Kerberos authentication to work. (This should be handled within the configuration manager by now. This process has been the same since SQL Server 2008!)

这是我们需要手动更改配置文件以允许Kerberos身份验证起作用的地方。（现在，这应该在配置管理器中处理。此过程自SQL Server 2008起一直是相同的！）

TIP: Kerberos authentication needs to be set up for all elements of the total environment before it will be functional from client machines. You may need to add or open delegation on the AD service account you use to run the SSRS service.

提示：必须在整个环境中的所有元素上都设置Kerberos身份验证，然后才能在客户端计算机上起作用。 您可能需要在用于运行SSRS服务的AD服务帐户上添加或打开委托。

rsreportserver.config file into: rsreportserver.config文件备份到：（ (Create folder) 创建文件夹）
- C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\Config Backups\
  C：\ Program Files \ Microsoft SQL Server \ MSRS12.MSSQLSERVER \ Reporting Services \ ReportServer \ Config Backups \
- C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\C：\ Program Files \ Microsoft SQL Server \ MSRS12.MSSQLSERVER \ Reporting Services \ ReportServer \ rsreportserver.configrsreportserver.config

TIP: You can now add users or groups to Report manager, to open this up to others but as long as you are part of the BUILTIN\Administrators group on the SSRS server, we can leave that alone for now, you’ll have all the access you need.

提示：您现在可以将用户或组添加到报表管理器，以向其他人开放，但是只要您是SSRS服务器上BUILTIN \ Administrators组的成员，我们现在就可以不理会它，您需要的访问权限。

Congratulations! You now have a single node SSRS installation.

恭喜你！现在，您已经安装了一个单节点SSRS。

向外扩展部署步骤 (Scale Out Deployment Steps)

When configuring a scale out deployment behind a load balancer (like an F5), you will need to adjust the report manager web.config file on every SSRS server to work properly in a load balanced namespace.

在负载平衡器（例如F5）后面配置向外扩展部署时，您将需要调整每个SSRS服务器上的报表管理器web.config文件，以使其在负载平衡的名称空间中正常工作。

Assuming you’ve now followed the above steps for 2 or more SSRS Servers, we can now bring them together.

假设您现在已经针对2个或更多SSRS服务器执行了上述步骤，那么我们现在可以将它们组合在一起。

Reporting Services Configuration Manager on your first server (SSRS01) Reporting Services配置管理器 （SSRS01）
Connect 连接
Scale-Out Deployment tab. The new server should be listed there with a Status of 横向扩展部署”选项卡。新服务器应在此处列出，状态为Waiting to Join. 等待加入 。
SSRS02 server and click SSRS02服务器，然后单击“ Add Server. Repeat this for each server you have listed. 添加服务器” 。对列出的每个服务器重复此操作。

We now need to make configuration file changes so that user sessions can travel between servers securely. These steps are for your first SSRS server, however, if you are just adding a server to an existing farm you will just copy the values we set from one server to the next.

现在，我们需要更改配置文件，以便用户会话可以在服务器之间安全地传播。这些步骤适用于您的第一台SSRS服务器，但是，如果您只是将服务器添加到现有服务器场中，则只需将我们设置的值从一台服务器复制到下一台服务器即可。

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager

C：\ Program Files \ Microsoft SQL Server \ MSRS12.MSSQLSERVER \ Reporting Services \ ReportManager
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer
C：\ Program Files \ Microsoft SQL Server \ MSRS12.MSSQLSERVER \ Reporting Services \ ReportServer
<system.web> section. <system.web>部分。
<machineKey> section to in the <machineKey>部分添加到<system.web> section: <system.web>部分中：
<machineKey validationKey=”AutoGenerate,IsolateApps” decryptionKey=”AutoGenerate,IsolateApps” validation=”3DES” decryption=”3DES”/>

<machineKeyvalidationKey =” AutoGenerate，IsolateApps” decryptionKey =” AutoGenerate，IsolateApps” validation =” 3DES” decryption =“ 3DES” />

NOTE: Im using “3DES” encryption here to get round a FIPS Compliance error detailed HERE. This may not be relevant in your organisation or setup so other encryption algorithms also work
注意：在使用IM“3DES”这里加密得到全面详述的FIPS遵从性错误的位置。这可能与您的组织或设置无关，因此其他加密算法也可以使用
Save the Web.config file
保存Web.config文件
Take a backup of the备份 rsreportserver.configrsreportserver.config file, default location is: 文件，默认位置为：
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer
C：\ Program Files \ Microsoft SQL Server \ MSRS12.MSSQLSERVER \ Reporting Services \ ReportServer
<Service> section, and add the following information to the configuration file, replacing the Hostname value with the virtual server name for your NLB server: <Service>部分，并将以下信息添加到配置文件中，用NLB服务器的虚拟服务器名称替换Hostname值：
<Hostname> reporting.company. com</Hostname>
<主机名> report.company。 com </ Hostname>
<urlroot> section, and update it to reflect the url for your report server application, for example: <urlroot>部分，并对其进行更新以反映报表服务器应用程序的url，例如：
<UrlRoot>http:// reporting.company. com/reportserver</UrlRoot>
<UrlRoot> http：// 报告公司。 com / reportserver </ UrlRoot>

关于ReportServerUrl属性的说明 (Note on the ReportServerUrl Property)

Do not modify <ReportServerUrl>. If you modify this URL, you will introduce an extra roundtrip through the virtual server each time an internal request is handled. For more information, see URLs in Configuration Files (Reporting Services). For more information about editing the configuration file, see How to: Modify a Reporting Services Configuration File in SQL Server Books Online.

不要修改< ReportServerUrl> 。如果修改此URL，则每次处理内部请求时，都会在虚拟服务器中引入额外的往返。有关更多信息，请参见配置文件（Reporting Services）中的URL 。有关编辑配置文件的更多信息，请参见SQL Server联机丛书中的如何：修改Reporting Services配置文件。

You can now restart the Reporting Services service on each server to ensure the changes you made to the configuration files have taken effect.

现在，您可以在每台服务器上重新启动Reporting Services服务，以确保对配置文件所做的更改已生效。

You will need to setup the load balanced URL in your Network Load balancer but these steps vary depending on hardware. Also, in the absence of a Network load balancer, look into windows load balancing. There is a great tutorial HERE

您将需要在网络负载均衡器中设置负载均衡的URL，但是这些步骤因硬件而异。另外，在没有网络负载平衡器的情况下，请查看Windows负载平衡。有一个伟大的教程HERE

With these configuration changes, you can now navigate to SSRS Report Manager using http://reporting.company. com/Reports or using the individual server names (http://SSRS01/Reports). Both will work, although when using the server names you are actually being redirected through the load balanced URL without knowing it. You can see this if you disable or remove the load balanced URL. You will no longer be able to render SSRS using the server URLs.

通过这些配置更改，您现在可以使用http://reporting.company导航到SSRS报告管理器。 com / Reports或使用单个服务器名称（ http：// SSRS01 / Reports ）。两者都可以使用，尽管在使用服务器名称时，实际上是通过负载平衡URL重定向的，而不知道它。如果禁用或删除负载平衡URL，则可以看到此信息。您将不再能够使用服务器URL呈现SSRS。

SSRS will also load balance its subscriptions across all servers too which helps reduce the impact on users if you have a lot of report schedules.

SSRS还将在所有服务器上实现其订阅的负载平衡，如果您有大量的报告计划，这将有助于减少对用户的影响。

SQL Server 2016有何不同…… (How SQL Server 2016 is different…)

I go into a lot of detail from discovering the issue to getting confirmation of the solution (or changes in SQL 2016 under the hood) in a post on my site CraigPorteous.com. The TL;DR version of that story is that setting up as SSRS 2016 Scale out deployment no longer requires us to configure “View State validation” for the front end Report Manager. You still need to ass your machine keys (as above) to the ReportServer Web.config file but you won’t find one in the Report Manager folder. Its also worth mentioning that the documentation Microsoft provide does not reflect this change for 2016. Handy 🙂

在我的网站CraigPorteous.com上的帖子中，我从发现问题到确认解决方案（或对SQL 2016进行了更改）进行了很多详细介绍。该故事的TL; DR版本是，将其设置为SSRS 2016横向扩展部署不再需要我们为前端报表管理器配置“查看状态验证”。您仍然需要将机器密钥（如上所述）关联到ReportServer Web.config文件，但是在“ 报告管理器”文件夹中找不到该密钥。还值得一提的是，Microsoft提供的文档并未反映出2016年的这一更改。