SSRS Subscriptions and SQL Sentry Reports

SQL Server Reporting Services (SSRS) is a little gem of the SQL Server stack, under-appreciated, but a gem nonetheless. Building a report is relatively simple, and lots of people do this. As part of the SQL Sentry suite of tools, we have a number of reports that are built-in. But what not many people realize is that we can actually take these reports that we view in the client and deploy them to an SSRS server. Thereby allowing access to the monitoring data without the need of the client. Great when you have managers etc. that want things like the Management Summary report or one of the Server Performance reports.

Here we will look at the options for automatically scheduling reports via SSRS with both standard subscriptions and data-driven subscriptions (Enterprise Edition).

What are SSRS Subscriptions?

Essentially report subscriptions are a mechanism for automating the execution and delivery of SSRS reports. Providing the ability to have the report sent to a file share, via email, or simply used to warm the SSRS cache ahead of interactive executions of reports on the server itself. In order to be able to use subscriptions, you need to meet the following requirements:

• Have permissions to the report already, and be able to manage individual subscriptions.
• Have configured the data source with the appropriate credentials option (stored or none).
• Know the Parameter values for the report you want to run.
• Ensure that the report is not using a model as a data source.

There are two main flavors of SSRS Subscriptions: standard and data-driven. It should be noted that data-driven subscriptions are only available in Enterprise Edition. The main difference between them is that with data-driven subscriptions you can use a query as a source for the report parameters.

Subscription Prerequisites

Now that we have our reports in place, it is time to set up our subscriptions. I will break this down into two sections, first of all we will look at standard subscriptions, and after that, we will look at data-driven subscriptions. Prior to setting these up, it is important to make sure of a couple of things.

If you plan on using the "Credentials are not required" option on the data source, you should make sure that you have specified an "Execution Account" for the Report Server. This is done via the Reporting Services Configuration Manager. This account will need permissions to the SQL Sentry database, along with sufficient access to the file shares you plan to use.

If you are going to use email as a delivery method, then you need to setup the email server details. Again this is done via the Reporting Services Configuration Manager.

Standard Subscriptions

Standard subscriptions are, surprisingly enough, available in Standard Edition and above. They are used to automate the generation of reports with pre-set or default parameter values. This is one of the easiest ways to get SQL Sentry reports executed automatically and distributed via email or to a file share.

First of all, we select the report that we want. In this case, I will be looking to use the "Top SQL - By Server" report. The quickest and easiest way to subscribe to this report is to select "Subscribe" from the options available from the drop-down.

This will then take us to the page where we can configure our subscription, and it is at this point we can select our delivery method.

Email Delivery Configuration

Setting up the email subscription is one of the easiest methods. It is simply a case of defining the recipients and the reply-to address. Then it is possible to customize the subject line and add a comment to the body of the email.

It is here that we can also set the rendering format that we want the report delivered as. This will vary depending on the report and the audience that it is aimed at. For the purposes of this post, I will be setting it to use a PDF output.

File Share Delivery Configuration

The other option that is available to use is delivery to a file share location. Here it is a case of defining the following:

• File Name.
• Delivery location (UNC path).
• Credentials for accessing the file share.
• Behaviour when encountering existing files.

Note: In the case of SQL Server 2016, there is also an option to set a service level account for file share delivery. This is done via the SSRS Configuration Manager tool.

Subscription Processing Configuration

Once the delivery mechanism has been decided upon and configured, the report parameters need to be configured. This will vary depending on the way that the report was developed and will be based on what the defaults and available values are for each of the parameters.

The important thing to understand here is that the values that are supplied for the report are not dynamic. This is the major difference between standard and data-driven subscriptions. Here users need to define the report parameters that are desired. The alternative is to use the defaults that are built into the parameter configuration. Here the date parameters are being set using the defaults. This is the closest that we can get to dynamic data to drive our parameter selection.

Where there is no default option, this will mean that a value needs to be specified. Additionally, if there is a relationship between parameters, then the downstream ones will not be visible until the one on which they have a dependency have been set.

The final stage in the process is to set the schedule that we want the subscription to execute the report and perform the delivery of the report output. With this, delivery of reports to all is now possible without the need for the SQL Sentry client to be installed. Meaning that management and other users who do not have access to the SQL Sentry system can get insight into how the system is performing.