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
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 0.9.750 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.
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.