Working with Report Snapshots in SQL Server Reporting Services (SSRS)

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. Read more..