Post Updated: 20/05/2019
Here is a quick guide to getting started with the SQLUndercover Inspector.
We made some changes to the installation process, please read them here in the revised installation guide.
So you have ran through the installation of the Inspector and now you want to know what to do next, below we will guide you through some of the key steps to get you started and following on from that we will explain how you can configure the Inspector to get things working how you want them.
First thing to do:
Once you install the Inspector you will find that you have a new Schema called Inspector in the logging database you chose when you installed, within this schema you will find a bunch of tables, views and stored procedures that have been created upon installation along with 4 new SQL Agent jobs.
The collection jobs are responsible for collecting the data and populating the Inspector tables and the Report jobs are responsible for generating the HTML reports and emailing out.
The first thing you will want to do is to set a schedule for these jobs (Unless you set @CreateJobSchedules = 1 during the setup), I would suggest the following as a starting point but you should tailor these as you see fit:
SQLUndercover Inspector Data Collection – Set to 08:50am daily
SQLUndercover Inspector Report – Set to 09:00am daily
SQLUndercover Periodic Backups Collection – Set to run 2 hourly from 10:00 to 18:00
SQLUndercover Periodic Backups Report – Set to run 2 hourly from 10:10 to 18:10
The above are just guidelines and the other thing you really need to be concerned about is ensuring that the Collection jobs have sufficient time to run before their respective Report jobs start, 10 mins should be more than enough time and after you check the run times of the collections you may find that you can bring these closer together, for example we run them 5 mins apart and tend to have minutes to spare 🙂
To test simply run the collection job and once complete run the report job, if there was no email address specified in [Inspector].[EmailRecipients] you will not receive and email but you will be able to retrieve the HTML report from the [Inspector].[ReportData] table.
And that’s it you are up and running!
Using a linked server?
Ensure that when you installed the Inspector that you specified the linked server name in the @LinkedServername parameter at point of setup , this will setup the Inspector to insert the collected data into your central database tables rather than your local database tables.
If you find that the thresholds you set (or defaults used) at point of installation are not ideal you are able to change the following settings in the table [Inspector].[Settings]:
SELECT [ID] ,[Description] ,[Value] FROM [Inspector].[Settings]
Just update accordingly
One option you will not find in here unfortunately is you data drive letter/s and your log file letter/s these need to be passed into the setup script so if you got these wrong you will need to re run the setup with the correct drive letters.
The following tables will allow you to make custom changes for the report/s.
[Inspector].[Modules] – Enable and disable modules, create new moduleConfig too.
[Inspector].[EmailConfig] – Allows you to change the Subject of the email for each Moduleconfig.
[Inspector].[EmailRecipients] – Change email recipients , comma delimited.
[Inspector].[ADHocDatabaseSupression] – Suppress databases showing in the Adhoc DB check by setting Suppress = 1
[Inspector].[CurrentServers] – Specify custom hex colours for table column headers e.g #4286f4, and also specify specific module configs per server , for example you may have a server which needs to run a different set of modules to the rest , in this case create a new mobuleconfig in the Modules table then set the name of that module config against the server in this table.