A quick glance behind, and ahead
You’re likely in week 2 of your 30 day Minion Enterprise trial!
Last time, we introduced the Database Files module and the Script Schemas module.
This time we’ll look at schemas in Minion Enterprise, and at the Drive Space module.
Minion Enterprise Schemas
You may have noticed what big fans we are of grouping objects logically by schemas. Some of the most common schemas you’ll see are:
- Collector – Tables in the Collector schema hold collected data from managed instances. As you might suspect, Collector stored procedures play a part in the actual data collection.
- dbo – Data in the dbo schema tables tends to be more static than in other schemas – for example, the data in dbo.Servers will change as servers are added, removed, and upgraded, but the frequency of server changes is far less than data collections or alerts.
- Report – Report denotes stored procedures that are used in SSRS reports, and which can (of course) be used ad hoc for your own purposes.
A few of the other, rather important, schemas include:
- Alert – The Alert schema includes alert-related stored procedures, and tables that control alert thresholds and deferments.
- Archive – The Archive schema includes tables and stored procedures related to Minion Enterprise’s self-cleanup. For example, the Archive.Config table determines how many days’ worth of data to keep for each of the tables in the Minion database. Be sure to look at the archival settings in this table to customize your data retention.
- Clone – The Clone schema houses objects in the Clone Users utility.
- History – Alert details are saved to tables in the History schema, before those alerts are emailed. For example, one row in the table History.Backups represents one detected missed backup. This allows you to (among other things) report on databases that have frequent trouble with backups.
The Drive Space Module
The Drive Space module collects disk space usage data, including drive name, type, label, capacity, free space, percent free, and file system. And, it allows you to set up alerts based on your own custom thresholds, so you’ll hear about it when a drive starts to get low on space.
Related video: Managing and Projecting Disk Space with ME
Objects in the Drive Space Module
Tables
- Collector.DriveSpace – Holds data collected from the servers. This is where the alerts come from.
- dbo.DriveSpaceExceptions – This is an alert configuration table. It holds data for the drives that you do not want to alert on.
- dbo.DriveSpaceThresholdServer – This is an alert configuration table. It holds default disk space thresholds for an entire server – in other words, thresholds for all the drives on a server.
- dbo.DriveSpaceThresholdDrive – This is an alert configuration table. It holds default disk space thresholds for individual drives. Each drive can have its own threshold. (Note that drive level settings take precedence over server level settings; so if ServerA has an entry in dbo.DriveSpaceThresholdServer, an entry for drive ServerA’s drive D: in dbo.DriveSpaceThresholdDrive will override that setting for that drive.
- Alert.DriveSpaceDefer – Holds drive space deferrment orders. Insert a row here to prevent ME from sending drive space alerts for a particular drive, for a set period of time.
Views
- Collector.DriveSpaceCurrent – Provides the most recent collection of drive space data.
- Collector.DriveSpacePrevious – Provides the next-to-most recent collection of drive space data.
Stored Procedures
- Setup.DriveSpaceDefer – Allows you to set up a drive space deferment for a specific drive, for a set period of time.
Examples: Set up deferments, exceptions, and thresholds
Drive Space is a particularly flexible module, in that you can set your own drive-level alert deferments, drive-level alerting exceptions, server-level alert thresholds, and even drive-level thresholds.
Drive level alert deferment: On Svr1, the E:\ drive is slowly filling up. Today it hit the threshold configured or the server – 5GB space free – and sent you an alert. You did your due diligence and determined that the E:\ drive really needs more space, and so you put in your request. Since you know about the issue, and now have to wait a week for the disk team to fulfil your request, you can defer further alerts about that drive for the next seven days. You shouldn’t keep getting the alert if the situation is under control!
Insert a row to the Alert.DriveSpaceDefer table, using the Setup.DriveSpaceDefer procedure, for Svr1 drive E:\, that ends 7 days from now:
EXEC Setup.DriveSpaceDefer @ServerName = 'Svr1',
@DriveLetter = 'E:\',
@DeferDate = '2016-02-23',
@DeferEndDate = '2016-03-01',
@DeferEndTime = '08:00:00';
Alert deferments can be extremely powerful. For example, you may want to turn off all alerts for C:\ drives across all “Bronze” level servers. One simple insert statement will do this for you:
INSERT INTO Alert.DiskSpaceDefer (InstanceID
, DriveLetter
, Caption
, DeferDate
, DeferEndDate
, DeferEndTime )
SELECT InstanceID
, 'C:\' AS DriveLetter
, 'C:\' AS Caption
, '01/01/2015' AS DeferDate
, '12/31/2100' AS DeferEndDate
, '00:00:00' DeferEndTime
FROM dbo.Servers
WHERE ServiceLevel = 'Bronze';
You could just as easily defer alerts in this way by environment, by SQL Server version or Windows version, or in any other way you can image. The data is there.
Drive level alerting exception: Let’s say that there’s a drive you never, ever want to receive an alert on (in our case, drive D:\ on Svr10, which has InstanceID 10). Perhaps it’s someone else’s responsibility; perhaps there are no, and never will be, any SQL files on that drive. For whatever reason, you just don’t want to hear about it. In that case, insert a row to the dbo.DriveSpaceExceptions table:
INSERT INTO dbo.DriveSpaceExceptions
( InstanceID , DriveLetter , Caption , Label )
-- Svr10:
VALUES ( 10 , 'D:\' , 'D:\' , 'D:\' );
Now, you will never be alerted on the Svr10 D:\ drive.
Server-level thresholds: You can set the drive space alert threshold at the server level using the dbo.DriveSpaceThresholdServer table (which makes this a sort of “server level default” for drive space thresholds, right?). What’s more, you can use one of a handful of alert methods: percent, GB, MB, or KB remaining on disk.
Let’s configure Svr1 to have a 10% threshold for alerts, and Svr2 to have a 5GB threshold:
INSERT INTO dbo.DriveSpaceThresholdServer
( InstanceID , AlertMethod , AlertValue)
VALUES
-- Svr1:
( 16 , 'Percent' , 10),
-- Svr2:
( 22 , 'GB' , 5);
Done!
Drive-level thresholds: Drive level thresholds are extremely similar to server level; they’re just drive-specific. If you have a server-level threshold, and a drive-level threshold, for the same server, then of course the drive-level threshold will apply for that drive; and the server level threshold will apply for all the other drives on the server.
Let’s configure Svr1’s Z:\ drive to have a 2GB threshold for alerts:
INSERT INTO dbo.DriveSpaceThresholdDrive
( InstanceID, DriveLetter, Caption, Label, AlertMethod, AlertValue )
VALUES
-- Svr1:
( 16 , 'Z:\', 'Z:\', 'Z:\', 'GB' , 2);
Now, we will receive alerts when the Svr1 Z:\ drive is below 2 GB free space; and when any other drive on Svr1 is below 10% of its free space.
We seriously love this module; it’s one of those that’s good for all servers, whether or not SQL Server is installed!
Write to us at Sales@MinionWare.net for a demo, a trial, and a quote. Write to us at https://minionware.freshdesk.com for help and suggestions. And, get more information on our Minion Enterprise YouTube playlist.
Next time, we’ll look at the Service Properties Module.