Monitoring SQL Server with PowerShell Instance Data Collection


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


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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


Last Updated: 2020-06-25


get scripts

next tip button



About the author





Comments For This Article




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.



download





Recommended Reading

Monitoring SQL Server with PowerShell Core Object Setup

SQL Server Agent Job Monitoring with PowerShell

SQL Server Backup Monitoring with PowerShell

SQL Server Agent Job Monitoring for Last Execution with PowerShell

SQL Server Database and Database File Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools