Pre-requisites:
SQL 2012 or higher
SQL Server Agent
A database to store the the data collected by the agent jobs (this can be one you use for your other DBA needs it doesn’t have to be a dedicated database)
Optional:
For instances with availability groups where you take backups on the secondary replicas you will need Linked Servers from your SQL server replicas to point to a central central database, this is so that the backups check can look for backups against all replicas and insert this in a central database allowing the report to aggregate the data. Hopefully in a later version we will be able to remove this requirement.
There is a new setup process for the Inspector, below we have outlined the steps required to get you started.
- Grab a copy of SQLUndercoverinspectorV2.sql from GitHub
- Run the setup script in the context of your logging database
- You have now created the Inspector schema and InspectorSetup stored procedure in your logging database, the messages tab will look like the below.
- If you are ready to upgrade/Install now, copy the contents of the messages tab and paste into to a query window, these are a bunch of default values but change them as you please, optional Parameters will be ignored when @InitialSetup = 0. For more details regarding individual Parameter settings during setup scroll to the bottom of this post for an explanation of what each parameter controls.
- Forgot to copy and paste the example procedure call? not to worry just run the following code for an example procedure call.
EXEC [Inspector].[InspectorSetup] @Help = 1
- When you are ready execute the InspectorSetup procedure, this procedure will create all Tables, Indexes, Procedures, Views and agent jobs required by the Inspector.
Do not worry if you forgot to set @InitialSetup for your first install, we added code to validate this setting during execution where it gets changed to a 1 if required.
Upgrading from an older version?
Nothing more you need to do.
Installing for the first time?
You should now have a bunch of new tables and stored procedures in your logging database and a single SQL Agent job (Inspector Auto [Name of your Inspector DB]) which runs on a minutely schedule looking for Modules/Reports that are scheduled to run from the Modules table and ModuleConfig table.
To see what’s going on you can query the Inspector.ExecutionLog table this will show you history runs for today only, otherwise for collections you can check the LastRunDate column in the Inspector.Modules table, and for Reports check the LastRunDate column in the Inspector.ModuleConfig table.
If you set an email address during the setup or directly in the EmailRecipients table then you will be receiving an email shortly with the report (make sure you are on the target of your linked server if using linked servers otherwise you won’t be receiving a report). If you didn’t set an email during setup or set in the Inspector.Settings table then you will find the html code stored in the table Inspector.ReportData.
Setup Parameters explained:
@LinkedServername – Name of the linked server you will be using or leave as NULL if you are not going to be using linked servers.
@Databasename – Name of the Logging database (Cannot be NULL)
@DataDrive – SET the drive letter where your database Data files are stored
@LogDrive – SET the drive letter where your database Log files are stored
@InitialSetup – For initial setup leave as 1, if re running or upgrading then set this to 0 to retain all previously logged data
@StackNameForEmailSubject – e.g. ‘SQLUndercover’ Specify a name for the stack you are reporting on that you want to show in the email subject
@EmailRecipientList – Set email recipient addresses (comma delimited) This will populate the EmailRecipients table for ‘DBA’
@BackupsPath – e.g ‘F:\Backups’ – Backup Drive and path
@DriveSpaceHistoryRetentionInDays e.g. 90
@DaysUntilDriveFullThreshold e.g. 56 – Estimated days until drive is full – Specify the threshold for when you will start to receive alerts (Red highlight and Alert header entry)
@FreeSpaceRemainingPercent e.g. 10 – Specify the percentage of drive space remaining where you want to start seeing a yellow highlight against the drive
@DriveLetterExcludes – Exclude Drive letters from showing Yellow Advisory warnings when @FreeSpaceRemainingPercent has been reached/exceeded e.g C,D (Comma Delimited)
@DatabaseGrowthsAllowedPerDay e.g. 1 – Total Database Growths acceptable for a 24hour period If exceeded a Yellow Advisory condition will be shown
@MAXDatabaseGrowthsAllowedPerDay e.g. 10 – MAX Database Growths for a 24 hour period If equal or exceeded a Red Warning condition will be shown
@AgentJobOwnerExclusions e.g. ‘SA’ – Exclude agent jobs with these owners (Comma delimited)
@FullBackupThreshold e.g. 8 – X Days older than Getdate()
@DiffBackupThreshold e.g. 2 – X Days older than Getdate()
@LogBackupThreshold e.g. 60 – X Minutes older than Getdate()
@DatabaseOwnerExclusions e.g. ‘SA’ – Exclude databases with these owners (Comma delimited)
Not seeing a report?
Double check that a report has been produced by querying the Inspector.ReportData table, if a report was produced it will be shown here, if not then might be worth checking the ExecutionLog for any errors.
For more details on getting started be sure to head over to our getting started guide here Getting started with the SQLUndercover Inspector
SQL Undercover 