Snapshots

Problem

We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database.

After deploying a report, you can specify your snapshot settings by using the Report Manager. There are three requirements that must be met in order to take advantage of report snapshots:

• Enable scheduled events and report delivery in SQL Server Surface Area Configuration (SQL Server 2005 only)
• Any data source used in the report must store the credentials for connecting to the database; a report snapshot can be created on a schedule so the data source cannot rely on using the credentials of the interactive user.
• All query parameters must have default values; since a report snapshot can be created on a schedule, there is no interactive user to specify these parameter values.

In this tip we will walk through the following steps to show how to enable report snapshots:

• Enable scheduled events and report delivery in SQL Server Surface Area Configuration (SQL Server 2005 only)
• Configure a data source
• Configure a report snapshot
• Execute a report from a snapshot

Enable Scheduled Events and Report Delivery

If you are running Reporting Services 2005, launch SQL Server Surface Area Configuration from the Microsoft SQL Server 2005 / Configuration Tools program group. Click on Surface Area Configuration for Features then verify that "Enable scheduled events and report delivery" is checked as shown below:

Configuring a Data Source

As an example we will take a look at a SQL Server data source. Navigate to the folder in the Report Manager where your data source is deployed, and fill in the "Connect using" part of the page as shown below:

Note the radio button "Credentials stored securely in the report server" is selected, a user name and password are specified, and the "Use as Windows credentials" checkbox is selected. Whatever user name you choose must have at least read permissions on the underlying database; i.e. add the user as a member of the db_datareader database role for the particular database. In addition the user must have execute permission on any stored procedures that are used by the report; i.e. GRANT EXECUTE ON OBJECT::dbo.[stored procedure name goes here] TO PUBLIC (since the stored procedure only reads data you may want to give everyone access rather than just specific users).

Configuring a Report Snapshot

You configure a report snapshot at the individual report level. Navigate to the folder containing your report in the Report Manager. Click the Show Details button to display the detailed view as shown below:

Click the icon in the Edit column for the report you want to enable the snapshot. The Properties page will be displayed as shown below:

Click on the Execution hyperlink to display the Execution Properties page for the report. Fill in the page as shown below:

Click the radio button "Render this report from a report execution snapshot". From now on whenever a user requests this report, they will always get the latest snapshot. If a snapshot does not exist, one will be created and saved to satisfy the current request as well as future ones. You can specify to create a new snapshot based on a report-specific schedule or a shared schedule. A report-specific schedule is used only by this report, a shared schedule can be used by multiple reports. Select your schedule option and fill in the page as shown below:

Return to the Properties page as shown above and click on the History hyperlink to display the History properties as shown below:

The following are the main points about the History properties:

• "Allow report history to be created manually" will allow you to create a report snapshot on demand. You can select a report snapshot to view by clicking the History tab on the Properties page where a list of available snapshots will be displayed; you can click the New Snapshot button on this page to create a snapshot on demand.
• "Store all report execution snapshots in history" will show the automatically generated snapshots in the history in addition to the scheduled snapshots.
• "Use the following schedule to add snapshots to report history" will create snapshots per your schedule and add them to the history.
• The default setting for the number of snapshots to keep is specified on the Site Settings page of the Report Manager.

There may be some confusion about specifying schedules for snapshots on both the Execution and History property pages. On the Execution properties page you are specifying when to refresh the report snapshot that is used to render the report when requested by a user. On the History properties page you are specifying when to create a report snapshot and add it to the history. You can navigate to the History property page and select which snapshot you want to view.

Execute a Snapshot Report

Let's take a look at the layout of the sample report that we will use to demonstrate the report snapshot:

The layout is intentionally simple; the textbox with the expression "Report Timestamp: " + Now provides a simple way to determine when the report snapshot was generated. On the Execution properties page I scheduled a new snapshot to be generated at 10:15AM each day. When I run the report I can see that the snapshot was generated at about that time this morning; e.g.:

Since I configured the History properties to add all snapshots to the history, I can navigate to the History tab and select a particular report snapshot to render as shown below:

Note that there are two reports. The first one was generated based on the schedule I specified on the Execution properties page;.this is the report snapshot that will be rendered automatically when someone requests the report. The second report snapshot was generated based on the schedule I specified on the History properties page. You can click on the hyperlinks to render these reports on demand.

Next Steps

• The report snapshot feature in SSRS is the best kind; it's just a configuration setting on a deployed report. Keep this in mind when you're trying to increase performance on your database servers.
• Report snapshots can be used to archive reports as of a certain point in time; e.g. month-end or year-end.
• Report snapshots take up space in the ReportServer database so you should think about limiting the number of snapshots that you keep.
• You can download the sample report and SQL scripts to create the test data here and use it as a basis for your own testing and experimentation.