Documentation forSQL Sentry

SQL Sentry Sample Views & Shared Resources

Sample Views Overview

Sample Views can be modified to further restrict event instances shown to only those utilizing the same shared resource, such as a Network Attached Storage (NAS), Storage Area Network (SAN) device, or even a network segment. 

From the Navigator pane, expand the Event Calendars node. This opens the Local and Shared sub-nodes. Local views are only available to the user that created them. Shared views are available to any user on any SQL Sentry client. The setup of a view is the same for either option.

SQL Sentry Navigator Pane with Event Calendars expanded, and the Sample Views node highlighted.

Sample Views Example

In this example, we'll demonstrate the steps required to modify an existing Sample View to create a shared resource view for all Red-Gate backups writing to a SAN to level the backup activity across the SAN to eliminate any bottlenecks, thus reducing contention and maximizing performance of the SAN and network. 

If you expand the Shared node, you'll notice the pre-existing Sample Views sub-node. Select the SampleView for the backup system you use. We'll use the Sample View for the Red-Gate SQL backups.

Navigator Pane with Sample Views expanded, and the Red-Gate SQL Backups view selected and highlighted.

The first time you double-click on SampleViews or select Open from the context menu, the following message box appears.

Add Sources window displaying that there are no sources for the selected view and prompting you to select Yes to add sources.

Select Yes to open the Select Instances box.

SQL Sentry Select Instances window displaying options to select from Connections, SQL Server Groups, and Filters.

The Select Instances box has three sections: 

  • Connections
  • SQL Server Groups
  • Connection Text Filters

This allows you to select individual instances (SQL Server or Task Scheduler), all SQL Servers in a group, or a combination of both. Since we want to see all Red-Gate backup jobs across all SQL Servers in the enterprise, we'll select All under SQL Server Groups.

The key to each Sample View is the Instance Text Filter. With it you can easily restrict the jobs placed in the view by the actual T-SQL or CmdExec text used in a job's steps. Multiple keywords are separated with a semicolon, and a plus sign (+) is used in front of any keyword(s) that you want to apply using an and, otherwise the keyword is applied with an or.

The default Step Text filter for the Red-Gate SQL backups sample view is master sqlbackup; SqlBackupC. This filter returns only jobs that contain any of those strings somewhere in the step command text. It returns all matching backup jobs, not just those writing to a particular SAN.

To restrict to only those jobs writing to a SAN, one minor adjustment to the filter should be made, shown as follows. Add an and filter using the base SAN name or IP address. In this example we've used +\\SAN01. This causes the view to return any jobs that contain master sqlbackup, SqlBackupC, and contain \\SAN01.

After selecting OK, the Custom Event View is ready to use.

SQL Sentry Event Calendar displaying events for the Red-Gate Backups view.

Custom Event View Filters

The jobs with an orange background indicate scheduling conflicts; we have several cases where backups coming from different servers hit the SAN at the same time. The drag-and-drop functionality can be used on future job instances on the calendar to eliminate the scheduling conflicts and reduce associated contention on the SAN or network.

When all jobs have a white background, you have a leveled schedule. A leveled schedule ensures that any bottlenecks and associated IO errors on the SAN's disk subsystem and network are minimized and that backup jobs run at optimal speed.