Top 10 things you must document in SQL Server Reporting Services (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.

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:

Installation Mode – SharePoint Integrated or Native Mode
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.

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

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.

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.

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).
Custom data source drivers & ODBC drivers installed on your Report Server.
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.

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!

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

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.

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.

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.

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.

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.

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?
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

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!

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.

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.

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.

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:

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

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.

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.

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.

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous

Latest posts by Craig Porteous (see all)

How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
Contribute, contribute, contribute! - June 20, 2018
Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018