Settings to Configure Snapshot Caching from SQL Server Management Studio
As we have learned, the primary
objective of Snapshot caching is to support the maintenance of snapshots
(or “histories”) of selected reports over time for comparison or other
purposes. “Fringe benefits” include
enhanced general reporting performance, especially for more complex or larger
reports, because the Snapshot reports are generated from cached
intermediate files in the Report
Server database.
In a manner similar to that of setting other caching
options in Reporting Services, we make the required settings within the Report execution properties for the respective report. In the subsections that follow, we will examine
these settings for a sample report from the perspective of the SQL Server Management Studio. We will do so, as we have in
other articles of this subseries, using a report from among the samples that
are available to anyone installing Reporting Services 2005.
If you have not installed
the samples, you’ll need to install them to use the sample report with which we
perform the steps below. If you prefer to use a local report, the steps will
be approximately the same for accessing the property settings for your
report of choice.
Configure Snapshot Caching
In Part I, we noted that, among the graphical user, command-line
and programmatic interfaces that Reporting Services makes available to
us for performing management activities, Report Manager is perhaps the
most user-friendly. In this article, we will examine the use of SQL Server
Management Studio to achieve similar ends. Among many other management
capabilities, SQL Server Management Studio affords us
a straightforward means of flexibly performing administration of reports and related
Reporting Services resources.
Let’s take a look at how we can work with Snapshot
caching settings for a report within the SQL Server Management Studio.
To do this, we will
take the following steps:
1.
Start Microsoft
SQL Server Management Studio.
2.
Select Reporting
Services in the Server type selector of the Connect to Server
dialog that appears, if necessary.
3.
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.
4.
Supply
authentication information, as required in your own environment.
The Connect to Server
dialog appears similar to that depicted in Illustration 3.
Illustration
3: Connecting to Reporting Services ...
5.
Click the Connect
button to connect with the specified server.
The Microsoft SQL
Server Management Studio opens. The Reporting Server to which we
have connected appears atop the Object Explorer pane.
6.
In the Object
Explorer pane, expand the Home folder (click the “+” sign to its
immediate left), appearing underneath the server within which we are working.
7.
Navigate to,
and expand, the subfolder containing a sample report with which to complete the
steps of this section, as appropriate (my sample reports have been deployed
within the AdventureWorks Sample Reports folder, and appear, with this
step, as shown in Illustration 4.
Illustration
4: The Sample Reports Appear
8.
Right-click
the Sales Reason Comparisons report (or another report of your choice).
9.
Select Properties
from the context menu that appears next, as depicted in Illustration 5.
Illustration
5: Accessing Report Properties from the Object Explorer ...
The Report Properties dialog
for the selected report opens.
10.
Click Execution
in the Select a page pane on the left side of the dialog, as shown in Illustration
6.
Illustration
6: Accessing the Execution Settings for the Report ...
Note: Credentials must be stored, and default parameters
must be in place, for any report for which we wish to configure Snapshot
caching.
The Execution properties
page opens in the area to the right of the Select a page pane. Here we
can set execution properties for the currently selected report only.
These options determine when report processing occurs, as we have seen in other
articles of this series. Among other activities, we can make settings to govern
a report run during off-peak hours, and the like, for flexible resource
scheduling within our own environments.
11.
On the Execution
page, click-select the radio button to the immediate left of Render this
report from an execution snapshot.
12.
Select (by
placing a checkmark in the checkbox to its left, if necessary) Create a
snapshot on the following schedule.
13.
Leaving the
radio button to the immediate left of Report-specific schedule selected,
click the Configure button to its immediate right.
We arrive at the Create Custom Schedule page, where
we can establish a schedule for Snapshot report generation.
14.
Within the Range
of Recurrence section in the upper half of the page, to the right of the Begin
running this schedule on, select January 1, 2008 (or another, more
relevant, date for the time at which you are performing these procedures) using
the calendar selector, as depicted in Illustration 7.
Illustration
7: Selecting a Date to Begin Snapshot Execution ...
15.
In like
manner, select a date exactly a year away in the input box labeled Stop this
schedule on, ensuring that the selection is enabled via a checkmark in the
checkbox to the left of the label.
16.
Select Month
as the Type, within the Recurrence Pattern section just beneath
the Recurrence Range section.
17.
Within the scheduling
details settings in the boxed section that next appears below, modify the Start
time atop the section to read “12:00:00 AM.”
18.
Ensure that
all months have been selected via the associated checkboxes.
19.
Select the
radio button labeled On calendar day(s), at the bottom of the
boxed section, and supply a “1” in the
input box to the right of the label.
Our Create Custom Schedule page settings, which
establish a schedule for Snapshot generation in the earliest minutes of
the first day of every month, to continue through one annual cycle, appear as
shown in Illustration 8.
Illustration
8: Schedule Details, with Our Input
It is useful to consider that we can, as we found in our
examination of performing parallel settings via Report Manager (in Part I), initiate Snapshot execution
from either the individual report level (the foregoing procedure) or from a
shared schedule.
20.
Click OK
(at bottom left) to save settings and to return to the Execution page
within the Report Properties dialog.
21.
Select the
radio button labeled Create a snapshot of the report when this page is saved,
if you wish to create a Snapshot report immediately.
Our settings on the Execution page, among which is
included a summary of our newly established schedule for Snapshot
generation, appears as depicted in Illustration 9.
Illustration
9: Execution Page Settings with Schedule Summary
NOTE: For details surrounding the Report Execution Timeout
Defaults settings in the bottom section of the Execution page, see Report Execution Caching Parts I and II.
22.
Click OK
to apply our settings (creating a Snapshot in the process, if we have directed
the Report Server to do so using the Create a snapshot of the report
when this page is saved setting we examined above), and to dismiss the Properties
dialog for the report we have chosen.
Unless we dictate differently, a single Snapshot
will be maintained with the ReportServerDB database. By default,
whenever a new Snapshot report is created, it will be swapped with the
single Snapshot already in place. To maintain historical Snapshots
within the database, we need to make the appropriate settings on the History
tab, as we shall see in the next section.
» 