Monitoring SQL Server with PowerShell Instance Data Collection

By: Alejandro Cobar   |   Updated: 2020-06-25   |   Comments (12)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring

Problem

To continue the series, this PowerShell monitoring script will collect instance level data for all of SQL Servers that have been registered for this monitoring solution.

Solution

This module will gather information about the SQL Server instance, such as the version, edition, memory settings and more. It will connect with each server in inventory.MasterServerListand capture the data for each of these instances. The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

Before we begin collecting data, we need to add additional components to the central database that was created with the core objects.

The following database objects will be created with the PowerShell script for this component:

Tables

• inventory.MSSQLEditions- a list of the different SQL Server editions. This gets built and populated by the script.
  • mssql_edition_id - key value for this table
  • edition - list of different editions of SQL Server
• inventory.MSSQLVersions- a list over the different SQL Server versions. This gets built and populated by the script.
  • mssql_version_id - key value for this table
  • version - list of different versions of SQL Server
• inventory.BuildNumbers - a list of the different SQL Server build numbers. This gets built and populated by the script.
  • mssql_build_number_id - key value for this table
  • sp - service pack number
  • cu - cumulative update number
  • extra - additional info about the update
  • build_number - SQL Server build number
  • release_date - release date for this build
• inventory.MSSQLInstanceValues- this table will stored the collected data for each SQL Server instance.
  • serverId - the Primary Key of the table, and also the reference to the instance id in the core inventory.MasterServerList table.
  • mssql_version_id - the id of the SQL Server version that points to the table inventory.MSSQLVersions.
  • mssql_edition_id - the id of the SQL Server edition that points to the table inventory.MSSQLEditions.
  • mssql_build_number_id - the id of the build number of the SQL Server instance that points to the table inventory.BuildNumbers.
  • min_server_memory - the current Min Server Memory value for the instance.
  • max_server_memory - the current Max Server Memory value for the instance. With this information populated, you can quickly know with a single query which instances don’t have a proper value set and can elaborate a plan to address it within your environment.
  • server_memory - the amount of memory currently installed in the server. This value helps you to quickly confirm if there are instances that don’t have a proper Max Server Memory value, in relation to the amount of memory in the server.
  • server_cores - the amount of CPUs currently found in the server where the SQL Server instance is hosted.
  • sql_cores - the amount of CPUs that the SQL Server instance is actually using. This is very useful because you might find a case (I hope not) where you have 32 cores in a server, but the SQL Server instance is a Standard Edition (which will use up to 24), so you quickly confirm if you are dealing with a bad architectural approach.
  • lpim_enabled - this value indicates if Lock Pages In Memory is enabled or not, for each SQL Server instance; a pretty useful value to know which instances currently lack this best practice.
  • ifi_enabled - this value indicates if Instant File Initialization is enabled or not, for each SQL Server instance; a pretty useful value to know which instances currently lack this best practice.
  • sql_service_account - the current account that the DB service is running with.
  • sql_agent_account - the current account that the DB Agent service is running with.
  • installed_date - the date/time when each particular SQL Server instance was installed. This value can give you a rough idea of what you’re currently dealing with, in terms of how new/old is the current infrastructure that you are providing support to.
  • startup_time - the date/time when the SQL Server instance was last restarted. This value can help you quickly check if somebody has been messing recently with an instance, or if simply the server hasn’t had a server reboot in a very long time (which could also mean that it hasn’t been patched in a very long time).
• audit.MSSQLInstanceValues
  • serverId- the id of the server instance
  • old_value- old value collected
  • new_value - new value collected
  • field- data item name
  • data_collection_timestamp - time of collection

Functions

• inventory.get_MSSQLBuildNumberId - returns the id of the matching build number
• inventory.get_InstanceId - returns id for the instance
• inventory.get_MSSQLEditionId - returns id for the edition
• inventory.get_MSSQLVersionId - returns id for the version

PowerShell Script

The PowerShell script that creates the above objects and inserts data into the inventory.MSSQLInstanceValues table is called:

• Get-MSSQL-Instance-Values.ps1

Before using this module, the core objects need to be created, see prerequisites section above.

The PowerShell script should be stored in the centralized location. For this series we have been using "C:\temp".

If you want to change the working directory and use something other then "C:\temp" you will need to edit the PowerShell script and modify the first line as shown below. This will be the case for all future modules too.

How to Use

Navigate to the folder were you created the files and you can run the PowerShell script as follows:

Option 1

• Right click on Get-MSSQL-Instance-Values.ps1 and select Run with PowerShell

Option 2

• Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-Values.ps1"

Option 3

• Schedule this as a SQL Agent job to run the PowerShell script on a predefined basis.

Option 4

• Schedule this as a Windows Task Scheduler job to run the PowerShell script on a predefined basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.MSSQLInstanceValues table, we can see the data that has been collected.

SELECT * FROM inventory.MSSQLInstanceValues

Checking for Data Differences

You can run the following query to see if any of the values have changed since the last run.

SELECT * FROM audit.MSSQLInstanceValues

Checking for Errors

To check for errors query the monitoring.ErrorLog table. 

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Values'

Query Collected Data

To join with the other tables to get lookup values you can use a query like below.

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS 'Instance',
   v.version AS 'Version',
   e.edition AS 'Edition',
   bn.build_number AS 'BuildNumber',
   iv.min_server_memory AS 'Min Server Memory',
   iv.max_server_memory AS 'Max Server Memory',
   iv.server_memory AS 'Server Memory',
   iv.server_cores AS 'Server Cores',
   iv.sql_cores AS 'SQL Cores',
   iv.lpim_enabled AS 'LPIM Enabled',
   iv.ifi_enabled AS 'IFI Enabled',
   iv.sql_service_account AS 'SQL Service Account',
   iv.sql_agent_service_account AS 'SQL Agent Service Account',
   iv.installed_date AS 'Instance Installation Date',
   iv.startup_time AS 'Instance Latest Startup Time'
FROM inventory.MSSQLInstanceValues iv
JOIN inventory.MasterServerList msl ON iv.serverId = msl.serverId
JOIN inventory.MSSQLBuildNumbers bn ON iv.mssql_build_number_id = bn.mssql_build_number_id
JOIN inventory.MSSQLEditions e ON iv.mssql_edition_id = e.mssql_edition_id
JOIN inventory.MSSQLVersions v ON iv.mssql_version_id = v.mssql_version_id

Download Scripts

• Download the scripts for this module.

Next Steps

• Check out these other related articles:
  • Monitoring SQL Server with PowerShell Core Object Setup

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips