Easily Keep Track of All Your SQL Servers
We know what it’s like when you’ve got 101 SQL Servers to manage, or even just a couple. It can be almost impossible to keep track of all these servers, their versions, the databases that they host, permissions, dependencies… the list goes on.
We developed the Undercover Catalogue to help with these issues. The Undercover Catalogue will go out, interrogate your servers and store details in a centralised location.
Where Can I Get the Catalogue From?
The Undercover Catalogue is available from our GitHub site.
What Does the Catalogue Store?
The Undercover Catalogue stores all manner of useful information on your SQL Servers,
- AD Group Memberships
- Agent Jobs
- Availability Groups
- Database Tables
- Linked Servers
Auditing and Change Tracking
All new in version 0.3.0, the Undercover Catalogue now contains an audit of all modules so it’s easy to see when things were changed and what those changes were.
A PowerBI dashboard gives you easy, visual access to all manner of data relating to your SQL estate, from an overview of the estate itself to a break down of individual servers, databases, security and more…
How Does it Work?
The Undercover Catalogue uses a powershell script to go out and interrogate all of the SQL Servers in you estate. The results are stored in a centralised database on a specified configuration server.
What Are the Requirements
There are a couple of requirements
- SQL Server version 2012 and above (future versions may include support for 2008 and possibly 2005).
- dbatools 1.0.0, The Catalogue interrogation scripts use the brilliant dbatools Powershell modules, these are available free from https://dbatools.io
Please note that the Catalogue now supports case sensitive collations
Installing the Undercover Catalogue
To install the Undercover Catalogue, simply run UndercoverCatalogueSetup.sql on all SQL Servers that will be interrogated as well as a central configuration server.
Upgrading From a Previous Version
If you’re upgrading from a previous version it couldn’t be simpler. Just run UndercoverCatalogueSetup.sql on all the SQL Servers that you need to upgrade and they’ll be brought up to the latest version.
Once upgraded, please be sure that you’re running with the latest Interrogation Powershell script, these are version specific.
NOTE: We only support upgrades from Catalogue v0.2.0 and onwards, it’s not possible to upgrade from version 0.1.
If you’re running with the compatibility patch for versions prior to 2012, this will need to be rerun after any upgrade.
The compatibility patch is unsupported so we can’t guarantee that it’ll continue working after an upgrade although we don’t see any issues with it at the moment.
The Catalogue contains a number of configuration tables, these are initially populated with default values. Again, note that although the configuration tables are created and populated on all SQL Servers, only those on the configuration server are actually used.
The ConfigInstances table contains the names of the SQLServers that you wish to interrogate.
The active flag can be switched to determine if a particular server is interrogated.
The ConfigInstances table is populated automatically if automatic instance detection is switched on.
ConfigModules hold the details of the available modules in the Catalogue and gives the option to enable or disable them.
- ModuleName – The name of the module
- GetProcName – The name of the proc which gathers the specified information
- UpdateProcName – The name of the proc which updates the appropriate tables in the configuration database.
- StageTableName – Every module requires a staging table
- MainTableName – The table used to store the gathered data
- Active – Switch to enable or disable the module
ConfigPoSH contains a number of variables that are required by the Interrogation script. Some of these can be changed to switch on or off certain features.
- CatalogueVersion – The installed version of the UndercoverCatalogue. DO NOT CHANGE
- AutoDiscoverInstances – Switch to enable or disable the auto discovery of SQL instances. USER CONFIGURABLE
- DBAToolsRequirement – minimum required version of dbatools DO NOT CHANGE
- AutoInstall – Automatically install the Catalogue when trying to interrogate servers that do not currently have it installed (not implemented in 0.1) USER CONFIGURABLE
- AutoUpdate – Automatically update the Catalogue on servers found to be running an old version (not implemented in 0.1) USER CONFIGURABLE
- InstallationScriptPath – Network path containing the installation script for use by AutoUpdate and AutoInstall (not implemented in 0.1) USER CONFIGURABLE
Running The Interrogation
The interrogation is run using the CatalogueInterrogation.ps1 PowerShell script this can either be run manually as required or it can run on schedule (recommended).
When running the interrogation there is a variable that will need changing at the top of the script. $ConfigServer will need to be changed to point to the SQL Server that hosts you centralised database.
Running The PowerBI Dashboard
The first time you load up the PowerBI dashboard, you’re going to need to point the connection string at your configuration database.
Select the existing connection and hit ‘Change Source…’
Pop in the instance name and database of your configuration server and you’re good to roll.
Now all you need to do is hit ‘Refresh’ and you’ll have a lovely view of you’re SQL Server estate.
SQL 2008r2 and Earlier
While we don’t officially support SQL Server versions prior to 2012 we know there are plenty of you still out there using it. To help you guys out, there’s an unsupported patch that contains compatible versions of the modules.
This patch should ONLY be run against those unsupported instances. https://github.com/SQLUndercover/UndercoverToolbox/blob/master/SQLUndercoverCatalogue/UndercoverCatalogue%20SQL2008%20Compatibility.sql