Monitoring SQL Server with PowerShell Instance Data Collection

By:   |   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.

powershell script parameters

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.

database objects

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

SELECT * FROM inventory.MSSQLInstanceValues
server info

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
server value changes

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
server info

Download Scripts

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar 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


Comments For This Article




Friday, March 8, 2024 - 2:06:29 AM - ANAND Back To Top (92050)
[ DBA].[inventory].[MSSQLBuildNumbers] table manually inserted but final query collected data not fetching records, Could you suggest me anything missing at my side. Thank you.

sp cu extra build_number release_date
RTM CU31 GDR 14.0.3465.1 2023-10-10
SP3 CU1 GDR 13.0.7029.3 2023-10-10

Thursday, August 3, 2023 - 7:29:50 AM - ANAND Back To Top (91459)
Hi.

Very nice script for capture mandatory SQL server info for monitoring.

SELECT * FROM inventory.MSSQLInstanceValues - Received/inserted records in table

SELECT * FROM audit.MSSQLInstanceValues - Received/Inserted records in table

But Build number and version not retrieve from final data collection query.

Thanks

Tuesday, April 27, 2021 - 9:36:25 AM - Dennis Back To Top (88615)
Do you have an example of how these scripts can be run as a sql server job? When I tried to set it up it runs as the SQL agent domain account which doesn't have login rights to the other servers. The settings.ini has the username and password fields defined but the ErrorLog table just shows "Login failed for user '<SQL Agent account>'"

Monday, November 23, 2020 - 2:27:36 AM - Alejandro Cobar Back To Top (87824)
Hi Tale,
I can think of a few things:
1) You don't have data in the inventory.MSSQLBuildNumbers table.
2) If you do have data in there, then probably you have instances with build numbers that are not in the inventory table (if they are 2005 and below that would be the case, if not then I find this strange).

If you're still stuck and can't find a way to fix that, then I can help you out with that... we could coordinate a call/meeting to go through this, so let me know.

Friday, November 20, 2020 - 7:17:54 AM - Tale Back To Top (87815)
Hi I need help. I do all as you wrote but.. in MSSQLInstanceValues tabel I have in columd [mssql_build_number_id] only NULL values.
I don't now why :(

Friday, August 28, 2020 - 11:24:55 AM - George T. Back To Top (86382)
Thank you, Greg for the quick reply. Very well appreciated!

Friday, August 28, 2020 - 11:13:44 AM - Greg Robidoux Back To Top (86381)
The download file has been updated to include the MSSQLBuildNumbers.sql build file. You will need to refresh the page so the link updates to the new file.

Friday, August 28, 2020 - 10:56:45 AM - George T. Back To Top (86380)
Hi Alejandro,
The "MSSQLBuildNumbers.sql" file is not in this module download.
Can you please advise where we can get it from?
Thank you for the good work.

Thursday, August 27, 2020 - 8:22:38 PM - Alejandro Cobar Back To Top (86376)
Hi Davin,

You are totally right, thank you for pointing that one out. I have made the fix and already sent the updated script so that it is visible in the article. For those SQL Server versions that don't have the sys.db_server_services available, xp_regread will be used instead to fetch the service accounts from the registry (so make sure that the account that's executing the script has enough permissions to use it).

Wednesday, August 26, 2020 - 12:24:48 PM - Davin Studer Back To Top (86365)
This file doesn't seem to work for SQL 2008 and below as sys.dm_server_services was not added till SQL 2008 R2.

Sunday, July 12, 2020 - 12:25:04 AM - Alejandro Cobar Back To Top (86125)

Hi Davin,

I totally get your point. However, the version of this module leaves that burden to the end user, so the first time the MSSQLBuildNumbers is loaded into the database it will contain the data only within the attached .sql file. Now, all the newer build numbers that are not in that .sql file must be entered manually by the end user directly in the database.

Regardless of that, I have in mind a solution (for another module) that addresses exactly that. It will automatically keep the MSSQLBuildNumbers up to date without the end user having to worry about it. So stay tuned!!!

Friday, July 10, 2020 - 1:15:09 PM - Davin Studer Back To Top (86117)

I like this script it's pretty slick. It would be handy if you added some logic to insert values into the [inventory].[MSSQLBuildNumbers] table each time the script is run if they do not exist. As new versions/CUs/SPs come out you will be missing data.














get free sql tips
agree to terms