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.
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.
The first time you double-click on SampleViews or select Open from the context menu, the following message box appears.
Select Yes to open the Select Instances box.
The Select Instances box has three sections:
- 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.
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.