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,

• Instances –
• Databases
• Logins
• Users and permissions
• Agent Jobs

with many more modules planned in future releases.

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 0.9.385 The Catalogue interrogation scripts use the brilliant dbatools Powershell modules, these are available free from https://dbatools.io
• CI Collation, unfortunately, only case insensitive collations are currently supported (this will change in v0.2)

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.

The database tables are only needed on the configuration server, the procs are needed on all SQL Servers.  At the moment, the installation script will add both tables and procs but this will change in a future release with the provision of an automatic Powershell based installation.

Configuration

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.

ConfigInstances

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

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

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.

What’s To Come

This is only version 0.1, we’ve got some big plans for the Catalogue.

Future versions will feature…

• On top of the list is the addition of more modules to gather even more useful information on your SQL Servers (v0.2 will include details of server hardware)
• Support for case insensitive collations as well as support for earlier version of SQL Server (planned for v0.2).
• Auto-update and auto-installation features (planned for v0.2).
• Python based interrogation routines
• A Power BI dashboard to show you pretty pictures of your SQL Servers and the data held about them.

We hope that you find the Undercover Catalogue useful, this is a very early pre-release version so keep checking back as new modules and functionality are added.