sql2012 ssrs_您必须在SQL Server Reporting Services（SSRS）中记录的十件事

sql2012 ssrs

Documentation is never fun. I curse having to do it and I curse the person who didn’t do it. It’s a no-win situation.

文档从来都不是一件好事。我诅咒必须这样做，而我诅咒没有这样做的人。这是双赢的局面。

Luckily I don’t want to tell you to write long documents for setting up or maintaining Reporting Services or creating reports etc. This is more about the main aspects of SSRS that you should be keeping a backup of or maintaining to make your life (and other people’s lives) easier in future. I’ll keep your typing to a minimum.

幸运的是，我不想告诉您编写冗长的文档来设置或维护Reporting Services或创建报告等。这更多地是关于SSRS的主要方面，您应该备份或维护这些方面才能使自己的生活（和未来的生活）。我会尽量减少您的输入。

安装设定 (Installation settings)

This is likely the first thing you’ll come across that’s worth keeping a record of. You’re installing Reporting Services and regardless of version you are presented with quite a few choices throughout the process:

这很可能是您会遇到的值得记录的第一件事。您正在安装Reporting Services，无论使用什么版本，在整个过程中都会有很多选择：

Installation Mode – SharePoint Integrated or Native Mode
安装模式– SharePoint集成或纯模式
Instance Name
实例名称
Installation Location
安装位置
Service Account
服务帐号
etc
等等

Once you’ve made all the relevant choices and before you hit the Install button, note the Configuration File location.

做出所有相关选择之后，在单击“安装”按钮之前，请注意配置文件的位置。

sql2012 ssrs_您必须在SQL Server Reporting Services（SSRS）中记录的十件事

You can use this to quickly deploy new installations, useful for scale out deployments, disaster recovery solutions or as another backup option for server rebuild.

您可以使用它来快速部署新安装，这对于扩展部署，灾难恢复解决方案或服务器重建的另一个备份选项很有用。

Microsoft provide great documentation for installing SSRS using a Configuration file: For SQL Server 2016 & 2017 and SQL Server 2014

Microsoft提供了使用配置文件安装SSRS的出色文档：对于SQL Server 2016和2017和SQL Server 2014

自定义配置 (Custom configurations )

You’ve installed Reporting Services and now need to configure the environment to suit your organisation or environment needs. Some configurations you might make include a custom URL for Report Manager & Report Server. You may also have more advanced configuration changes to make in the rsReportServer.config file to add or disable report export options or setup a load balanced URL.

您已经安装了Reporting Services，现在需要配置环境以适合您的组织或环境需求。您可能要进行的某些配置包括报表管理器和报表服务器的自定义URL。您可能还需要在rsReportServer.config文件中进行更高级的配置更改，以添加或禁用报告导出选项或设置负载平衡URL。

The majority of these changes made either from the Configuration Manager or in the .config file itself can be documented by simply backing up the file itself.

从配置管理器或.config文件本身进行的大多数更改都可以通过简单地备份文件本身来记录。

Other custom configurations you make that would need to be documented separately include:

您进行的其他需要单独记录的自定义配置包括：

IIS HTTP Redirection for your Report Manager URL (Redirecting users to the /Reports URL from the base URL).
报表管理器URL的IIS HTTP重定向（将用户从基本URL重定向到/ Reports URL）。
Custom data source drivers & ODBC drivers installed on your Report Server.
在报表服务器上安装的自定义数据源驱动程序和ODBC驱动程序。
Some load balanced configurations require machine keys to be generated and stored in multiple places.
某些负载平衡配置需要生成机器**并将其存储在多个位置。

加密金钥 (Encryption key)

This has got to be one of the most important files to back up. When you’ve installed a fresh Report Server you take a backup of the encryption key using the Configuration Manager. This is where you can also update or restore the encryption key.

这必须是要备份的最重要的文件之一。安装新的Report Server后，可以使用Configuration Manager备份加***。您也可以在此处更新或还原加***。

This encryption key secures all of your data source credentials (ie. sensitive information). If you ever need to restore your Report server to another instance or server (or maybe you are using the production database to create a development copy) you will need this key or you will lose ALL of your sensitive data.

此加***可保护您所有的数据源凭据（即敏感信息）。如果您需要将报表服务器还原到另一个实例或服务器（或者您正在使用生产数据库来创建开发副本），则将需要此**，否则将丢失所有敏感数据。

This key will also need to be backed up again if you change the service account that Reporting Services runs under or have to reset its password! That’s important to remember!

如果您更改了Reporting Services运行的服务帐户或必须重置其密码，则也需要再次备份该**！要记住这一点很重要！

All scenarios for renewing a backup or restoring the encryption key are listed at Microsoft’s documentation page.

Microsoft的文档页面中列出了所有续订备份或还原加***的方案。

As part of storing or restoring this key you will also need to provide a password to secure it. This needs to be documented in a secure way as you’re not likely to use it on a regular basis.

作为存储或还原此**的一部分，您还需要提供密码来保护它。由于您不太可能定期使用它，因此需要以安全的方式对其进行记录。

备份rsReportServer.config (Backup rsReportServer.config)

As mentioned above, this file contains the majority of Reporting Services configurations. It’s good practice to back this up after every change, but be careful; each file contains a “DSN” unique to each Report Server installation so you can’t just copy and paste the file into another server. Backing this file up will save you hours for an in-place recovery but its contents will also provide a road map for configuring other environments if you haven’t documented your custom configurations (mentioned above) elsewhere.

如上所述，此文件包含大多数Reporting Services配置。最好在每次更改后都进行备份，但是要小心。每个文件都包含每个Report Server安装所独有的“ DSN”，因此您不能仅将文件复制并粘贴到另一台服务器中。备份此文件将为您节省原地恢复的时间，但是如果您没有在其他地方记录您的自定义配置（如上所述），则其内容还将提供配置其他环境的路线图。

报告模板 (Report template)

You now have your environment set up and configured. Most BI developers or administrators won’t have the luxury of enforcing a template on users or a development team from the outset but in every organisation I have worked, it has been implemented eventually.

现在，您已经设置并配置了环境。大多数BI开发人员或管理员从一开始就没有奢侈地在用户或开发团队上执行模板，但是在我工作的每个组织中，模板最终都已实施。

This is a great way to not only keep reports looking professional and consistent but to make development easier too. If you create a report template in SSDT that includes things like the company header & footer, standardised layouts, perhaps a common data source or naming convention for datasets, this will save developers a lot of time setting these aspects up for each report developed. It will also reduce human error and prevent minor discrepancies between reports developed over time.

这不仅是使报表保持专业性和一致性的一种好方法，而且也使开发更加容易。如果您在SSDT中创建报告模板，其中包括公司页眉和页脚，标准化布局，也许是通用数据源或数据集命名约定等内容，这将为开发人员节省大量时间来为开发的每个报告设置这些方面。它还将减少人为错误，并防止随着时间的推移而生成的报告之间的细微差异。

This point goes hand in hand with the next aspect. This can sometimes be enforced by a Marketing dept. or management but if not, it’s usually beneficial to adopt. This one does require some documentation I’m afraid.

这一点与下一个方面息息相关。有时可以由市场营销部门强制执行。或管理层，但如果没有的话，采用通常是有益的。恐怕这需要一些文档。

组织标准 (Organisational standards)

Organisational standards can start with things like fonts, logos, text sizes, colour schemes.

组织标准可以从诸如字体，徽标，文本大小，配色方案之类的东西开始。

Tying all of these together in your report templates and even your Report Server environment through a brand package (SQL 2016 Brand Package breakdown) give a consistent and professional look & feel to your reports. As the previous point highlights, implementing this once and using the same template will save developers a lot of time.

通过品牌包（ SQL 2016品牌包明细表）将所有这些内容结合在您的报表模板甚至Report Server环境中，可以为您的报表提供一致且专业的外观。如前所述，仅执行一次并使用相同的模板将为开发人员节省大量时间。

Going beyond layout & branding, organisational standards can also include coding standards for report development that can be checked as part of a code review process.

除了布局和品牌之外，组织标准还可以包括报告开发的编码标准，可以在代码审查过程中进行检查。

Has the developer used a stored procedure as a data source, instead of embedded T-SQL?
开发人员是否使用存储过程而不是嵌入式T-SQL作为数据源？
Have datasets and data sources been named appropriately?
数据集和数据源是否已适当命名？
Do report elements such as charts and graphs adhere to standards? (ie. Legend, colours, sizing etc).
报表元素（例如图表）是否符合标准？（即图例，颜色，大小等）。

I could continue for pages on the possible coding standards that you might want to implement or you already adhere to.

我可以继续介绍您可能要实施或已经遵守的可能的编码标准的页面。

执行日志 (Execution log)

This isn’t a mission critical component but more of a long term maintenance plan. The report server execution log views (dbo.ExecutionLog3 is the recommended view for its user friendly field names) contain information on all report executions taking place in SSRS. This includes subscriptions, Report Builder executions, caching & user report runs. All executions have detailed timing, source and result information. More details on the Execution log can be found in Microsoft’s documentation

这不是关键任务组件，而是更多的长期维护计划。报表服务器执行日志视图（建议使用dbo.ExecutionLog3作为其用户友好字段名称的视图），其中包含有关SSRS中发生的所有报表执行的信息。这包括订阅，报表生成器执行，缓存和用户报表运行。所有执行都具有详细的时间，来源和结果信息。可以在Microsoft文档中找到有关执行日志的更多详细信息。

The problem is that this information is trimmed daily and only stored for 60 days by default. In my opinion it’s good practice to setup a process to scrape this data regularly and store it in another “metadata” database. In doing so, you will be able to build up a usage & performance history of all reports. This is perfect for identifying reports that are no longer used and can be retired, or reports that are suffering from progressive performance issues over a number of months. Assessing these could lead to improving indexes on source databases & making users happy!

问题是该信息每天都会修剪，默认情况下仅存储60天。我认为，良好的做法是设置一个过程以定期抓取这些数据并将其存储在另一个“元数据”数据库中。这样，您将能够建立所有报告的使用和性能历史记录。这对于识别不再使用且可以退休的报告或在数月之内遭受渐进的性能问题的报告来说是理想的选择。评估这些可能会导致改进源数据库上的索引并使用户满意！

It is possible to increase the retention time or retain the execution log data indefinitely, described by Shiyang Qiu here but I prefer to keep this data in a separate metadata database away from my production SSRS database.

它可以增加停留时间或无限期保留执行日志数据，通过石羊河邱描述在这里，但我更喜欢把这个数据在一个单独的元数据数据库从我的生产SSRS数据库了。

订阅失败 (Subscription failures)

I’ve covered how to monitor these in this SQLShack post. This still isn’t a built in feature in Reporting Services but in my opinion it’s a must for supporting an SSRS environment.

我已经在这篇SQLShack文章中介绍了如何监视这些内容。这仍然不是Reporting Services的内置功能，但我认为这是支持SSRS环境所必需的。

Kerberos身份验证？备份您的SPN！ (Kerberos authentication? Backup your SPNs!)

If you’re using Kerberos authentication with Reporting Services you’ll at least have to update the rsReportServer.config file with the correct authentication mode. Beyond that you have SPNs on your SSRS domain Service account to consider. This may be managed by your domain administrators but it’s still a good plan to back these up or check that someone is backing these up. It is too easy to clear SPNs or constrained delegation settings accidentally.

如果将Kerberos身份验证与Reporting Services一起使用，则至少必须使用正确的身份验证模式更新rsReportServer.config文件。除此之外，您还可以考虑在SSRS域服务帐户上使用SPN。这可以由您的域管理员管理，但仍然是备份这些备份或检查是否有人备份这些备份的好计划。意外清除SPN或受约束的委托设置太容易了。

Having a backup will also give you a good reference point for building new environments. You can of course easily see SPNs from the command line using this command:

进行备份也将为您提供一个构建新环境的良好参考点。您当然可以使用以下命令从命令行轻松查看SPN：

SETSPN –L DOMAIN\SERVICEACCOUNT

SETSPN –L DOMAIN \ SERVICEACCOUNT

Constrained delegation settings (if you choose to use them) are not so easy to check if you’re not a domain administrator, as shown below. You have a snapshot view at the list but not a full view due to the controls being disabled.

约束委派设置（如果您选择使用它们）很难检查您是否不是域管理员，如下所示。由于禁用了控件，列表中有快照视图，但没有完整视图。

You can capture these using PowerShell though. No admin required. I describe that in this post

不过，您可以使用PowerShell捕获它们。无需管理员。我在这篇文章中描述了这一点

共享数据源设置 (Shared data source settings)

I demonstrate the PowerShell scripts for backing up & restoring Data Source settings in my post about Migrating Reporting Services. Again this recommendation is born of experience. Not only is it very useful for rebuilding development environments quickly but it allows you to easily fix issues from misconfigurations. That’s a nice way of saying when (not IF) someone accidentally deletes a data source, it’s stored credentials or changes the connection string and you’re not quite sure what it should be. Standing up a database backup is a long way for a single connection string or username.

我在有关迁移Reporting Services的文章中演示了用于备份和还原数据源设置的PowerShell脚本。同样，该建议是由经验产生的。它不仅对于快速重建开发环境非常有用，而且还使您可以轻松地解决配置错误的问题。这是一个很好的表达方式，当某人（不是IF）意外删除数据源，存储凭据或更改连接字符串时，您不确定应该是什么。对于单个连接字符串或用户名，建立数据库备份是很长的路要走。

I should point out that these PowerShell scripts won’t store passwords for stored credentials but you’ll at least have the username & connection string there.

我应该指出，这些PowerShell脚本不会存储已存储凭据的密码，但是您至少会在其中拥有用户名和连接字符串。

结论 (Conclusion)

As I mentioned at the start, these points aren’t all about writing long-winded articles or documents. Some might be common sense; others best practice or even a “lessons learned the hard way” situation. There’s a lot more you could do, like creating a map of your reports & the metrics they contain giving users something to search, but that’s not always relevant and can be difficult to maintain.

正如我在开始时提到的，这些要点并非全部与编写冗长的文章或文档有关。有些可能是常识；其他人的最佳做法，甚至出现“教训难”的情况。您还可以做更多的事情，例如创建报告及其包含的指标的地图，从而为用户提供搜索的内容，但这并不总是相关的，并且很难维护。

In terms of administration, I have documented service account usage in a database (in shared data sources, file share and data-driven subscriptions) to aid in password resets but this requires regular maintenance & is only as good as the process keeping it up to date.

在管理方面，我已在数据库中记录了服务帐户的使用情况（在共享数据源，文件共享和数据驱动的订阅中），以帮助重置密码，但这需要定期维护，并且仅能使其保持最新状态。日期。

Though not all will be relevant in every situation, these points should help you improve the resilience and efficiency of your existing or new Reporting Services environment.

尽管并非每种情况在每种情况下都相关，但这些要点应有助于您提高现有或新Reporting Services环境的弹性和效率。

参考资料 (References)

翻译自: https://www.sqlshack.com/top-10-things-must-document-reporting-services/