Undercover Catalogue 0.4.0

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
  • Databases
  • Database Tables
  • Permissions
  • Linked Servers
  • Logins
  • Servers
  • Users

Auditing and Change Tracking

The Undercover Catalogue contains an audit of all modules so it’s easy to see when things were changed and what those changes were.

PowerBI Dasboard

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_040.sql on your central configuration server.

Once installed, we strongly recommend running it once with auto-updates switched on to ensure that you’re running on the latest version.

As of 0.4.0, there is no longer any need to install the Catalogue on all the servers that you want to monitor. Simply install it onto your central server and it’ll go out and interrogate all your SQL instances.

Upgrading From a Previous Version

NOTE: We only support upgrades from Catalogue v0.2.0 and onward, it’s not possible to upgrade from version 0.1.x

Upgrading from version 0.2.x

If you’re upgrading from a previous version prior to 0.3.0, you’ll first need to upgrade to 0.3.0. To do this, simply run UndercoverCatalogueUpgrade_030.sql, found in the updates folder in GIT.

This will bring you up to version 0.3.0, see “Upgrading from version 0.3.0” to install the next upgrade.

Upgrading from version 0.3.0

If you’re upgrading from 0.3.0, run the Catalogue_UD040.sql script. This will take you to version 0.4.0.

Once on 0.4.0, we recommend running the PowerShell script at least once with auto-updates switched on to ensure that you’re running on the latest version, see “Upgrading from version 0.4.0 and onward” for more details.

Upgrading from version 0.4.0 and onward

Automatic Updates

Version 0.4.0 introduced the option to automatically update the Catalogue to the latest version. This can be enabled in the ConfigPoSH table.

Schema Updates

If auto updates are enabled and the powershell Interrogaton script has access to the SQL Undercover GitHub repository, it will check for updates and install them each time it is run.

Module Updates

Modules now have an ‘Online’ flag, found in ConfigModuleDefinitions, this will allow the Catalogue to run the latest module definition from Git and update the locally stored definition of the module.

In the event that the repository can’t be accessed, the Catalogue will revert to the locally stored definition.

We strongly recommend running with auto updates switched on.

Manually Updating

If you choose not to updates automatically, you can run the updates manually.

Schema updates can be found HERE, updates are incremental so will need each script running, in order on your configuration server.

The latest module definitions can be found HERE, the definitions in ConfigModuleDefinitions will need to be updated with the latest definition

The PowerShell Interrogation script isn’t updated automatically. Please ensure that you’re running the latest version of the script to ensure maximum compatibility.

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  (not used, deprecated in 0.4.0) 
  • UpdateProcName – The name of the proc which updates the appropriate tables in the configuration database.  (not used, deprecated in 0.4.0) 
  • StageTableName – Every module requires a staging table
  • MainTableName – The table used to store the gathered data
  • Active – Switch to enable or disable the module

ConfigModulesDefinition (since 0.4.0)

Contains the module definition code and location of online definition.

  • ModuleID – ID referring ConfigModules. 
  • Online– Use Online module definition, when enabled the Catalogue will attempt to use the online definition by default. When disabled, the definition stored in this table will be used. USER CONFIGURABLE
  • GetDefinition – Locally stored definition of the GET portion of the module, used if Online = 0 or the online repository isn’t available. USER CONFIGURABLE
  • UpdateDefinition – Locally stored definition of the UPDATE portion of the module, used if Online = 0 or the online repository isn’t available. USER CONFIGURABLE
  • GetURL – Online location of the GET portion of the module
  • UpdateURL – Online location of the UPDATE portion of the module

ConfigModulesInstances (since 0.4.0)

It is now possible to run different module configurations on different instances. The Active setting in this table will override any settings in ConfigModules.

  • ModuleID– Referencing ConfigModules
  • ServerName – SQL Server instance
  • Active – Active setting overrides any settings in ConfigModules for specified module and instance.

Config PoSH

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 used, deprecated in 0.4.0) 
  • AutoUpdate – Automatically update the Catalogue on servers found to be running an old version  USER CONFIGURABLE
  • InstallationScriptPath – Network path containing the installation script for use by AutoUpdate and AutoInstall  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 2008 compatible module, this will need to be enabled in ConfigModulesInstances

Create a website or blog at WordPress.com

Up ↑