Monitoring SQL Server with PowerShell Core Object Setup

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


Problem

There are many things that we can monitor in SQL Server such as backups, SQL Agent Jobs, configuration changes, etc. There are also several different tools that can be used to track these things that come with SQL Server as well as third party tools. The one problem with most approaches is that several different techniques are used to monitor SQL Server, so in this tip series we are going to look at creating a PowerShell monitoring solution for SQL Server. This gives us a free monitoring option as well as an option we can customize as needed.

Each tip in this series will tackle a different aspect for data centralized data collection, so let's get started.

Solution

The very first module that we will be putting together creates the core components that will be used by other parts of the solution. As mentioned, we will use PowerShell to collect the data and to also create the database objects in a centralized database server.

This PowerShell module will create the base database objects needed for the rest of the modules. It creates the database to store the data, some tables and inserts the list of servers that we want to monitor.

Database Objects

Before we begin collecting data, core database components will be setup with this first PowerShell script.

The following will be created with the PowerShell script:

Database

  • DBA - this is the name of the centralized collection database, but it can be changed if needed (see settings.ini section)
    • note: the database will be created using the settings of the model database

Schemas

  • audit - this schema will hold all database objects that are used for auditing purposes
  • inventory - this schema will hold database objects that contain the list of SQL Server instances
  • monitoring - this schema will hold database objects used to collect the data

Tables

  • monitoring.ErrorLog - this captures any errors that are encountered
    • serverid - ties back to inventory.MasterServerList.serverid
    • script - this is the script that was running
    • message - this is the error message generated from the script
    • error_timestamp - this is the date and time of the error
  • inventory.MasterServerList - this stores a list of servers to monitor
    • serverid - system generated unique id
    • server_name - name of the server where SQL is installed
    • instance - name of the SQL Server instance
    • ip - ip address of the SQL Server instance
    • port - this is the port number for the SQL Server instance
    • is_active - 1 = active, 0 = inactive

Settings.ini

This file is used to configure the central server and database that will be used to store all of the collected data.

There are two parameters that you will need to update in this file:

  • centralServer - this is the server and instance name where you want to create the inventory database
  • inventoryDB - this is used to specify the name of the database that will get created and store the collected data. If the database already exists it will not create the database.

By default, when you run the PowerShell script it will try to connect to the server using a trusted connection, using the following information that is in the Settings.ini file.

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA

If you want to use a SQL login then you will need to configure the Settings.ini file as follows and specify the SQL login and password to use to connect to the central server.

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA
[Optional]
username=login
password=pwd

Instances.txt

The instances.txt file is used to load the data into the inventory.MasterServerList table. The format of the data in the text file is as follows:

  • ServerName, InstanceName, IPAddress, Port

Here is an example of two instances that we will monitor. Note that you need to use single quotes around each item and separate each item with commas.

  • The first row below is for server DESKTOP-QUDLLRR using the default instance of SQL Server, so we specify the default instance as MSSQLSERVERwith ip 10.0.0.35 and uses the default port 1433
  • The second row is also for server DESKTOP-QUDLLRR using a named instance of SQL2with ip 10.0.0.35, but uses port 50287
'DESKTOP-QUDLLRR','MSSQLSERVER','10.0.0.35',1433
'DESKTOP-QUDLLRR','SQL2','10.0.0.35',50287

This text file can be updated with new instance information and the PowerShell script can be run again or you can just add new rows directly to table inventory.MasterServerList with the necessary information.

PowerShell Script

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

  • Create-Master-Server-List.ps1

The script will use the Setting.ini file to determine what SQL Server instance to use for the central server and also the name of the database you want to use for the central data collection.

It will create the database, tables and load data from the Instances.txt file into inventoryMasterServerList.

How to Use

After you have entered records into the Instances.txt file for your SQL Server instances and configured the Settings.ini file you should put all three files in the same folder. In my setup, I put all of these files in the "C:\temp" folder.

  • Instances.txt
  • Settings.ini
  • Create-Master-Server-List.ps1

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

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

Option 1

  • Right click on Create-Master-Server-List.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\Create-Master-Server-List.ps1"

This doesn't need to be scheduled to run on a set basis, since this only creates core objects and inserts the instances to monitor which can be updated directly to table inventory.MasterServerList if there are changes or you want to add additional instances to monitor.

Check Creation of Database and Objects

After running the PowerShell script, we should two tables that are created. In this example, I used "DBA" as my central monitoring database and the two tables that get created are inventory.MasterServerList and monitoring.ErrorLog.

list of database objects

If we query the inventory.MasterServerList table, we should see the rows that have been inserted.  In my case, it inserted 2 rows as shown below.

list of instances to monitor

Checking for Errors

We can query the monitoring.ErrorLog table to find any errors with this module.

For example, if we run the PowerShell script again we should get an error because we are trying to load the same instances which should fail. We can see this below if we query the monitoring.ErrorLog table.

query monitoring errorlog table

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


Article Last Updated: 2020-06-24

Comments For This Article




Monday, March 20, 2023 - 12:36:37 PM - Alejandro Cobar Back To Top (91027)
Good approach Dennis!
Remember that this is a highly customizable solution, so I'm glad that you are tweaking it to fit your use case :)

Cheers!

Monday, March 20, 2023 - 8:17:22 AM - Dennis Back To Top (91023)
In most all of the scripts there is a function to execute the sql queries. One of the values that gets passed in is a 1 for trusted. This is assuming that all the instances are trusted and to use the windows credentials of the user. Shouldn't it be set to the value of the variable $trusted that gets set when it reads from the instances table?
In order to run a SQL agent job to execute the powershell scripts, I would have to create a credential in SQL Server or have the queries run as a sql user. I changed all the values for the column instances.trusted to 0 and modified the scripts to run this command instead:

Execute-Query $saInformationQuery "master" $sqlInstance $trusted
rather than this:
Execute-Query $saInformationQuery "master" $sqlInstance 1

Monday, March 15, 2021 - 7:10:05 PM - Alejandro Cobar Back To Top (88402)
Hi Dennis!
So the MasterServerList table already has a port field that is auto-filled with 1433, if no value is specified for it. Therefore, to establish a connection to your instances using any of the other modules, you would have to modify the $instancesLookup query string to add the port instead of adding it to your server_name value in the MasterServerList. At least that's the intention of it, but if you were able to make it work like that and you are fine with that solution, then by all means go ahead, as long as you can find value with the work I've published so far :)

Monday, March 15, 2021 - 10:20:36 AM - Dennis Back To Top (88397)
I have to add the port number to the servername in the inventory.MasterServerList table in order to get a connection for default instances.
Not a big deal but should be mentioned.

Friday, September 4, 2020 - 5:40:04 PM - Alejandro Cobar Back To Top (86419)
Hi Bubba,

I'm just sharing what has served me so well in the past few years so that others can benefit from that. I understand that the editing part of the files can be a tedious thing to do, but in my case, the benefit turns out to be very high. I guess that many things in life work that way, you work very hard at beginning and then enjoy the fruits of such hard work.

Friday, September 4, 2020 - 10:21:32 AM - BUbba Back To Top (86417)
Interesting start; but from a sysadmin perspective these scripts require a lot of surgery to make work properly in a real environment.
first, INI files are ... well, a thing of the past. would be much better to pass in params from command line. the less editing of files, the better. you want params for everything.

Monday, June 29, 2020 - 2:04:18 PM - Alejandro Cobar Back To Top (86070)

Perhaps I'm not getting the issue, but let me give it a shot...

Are you able to reach your target servers from the centralized server using at least the IP of the servers?

If no, then your issue is a networking one.

If yes, then I suggest using the option that uses credentials in the Settings.ini file. However, you should manually do the effort of creating local SQL accounts (with enough privileges) in each SQL Server instance that you are planning to cover. I know this sounds like a lot of job, but it would be a one time thing for each instance.

Let me know if it works for you.

Monday, June 29, 2020 - 4:25:06 AM - Gustav Mulder Back To Top (86065)

Thanks for the feedback Alejandro.  I ran into an issue with the way our servers are currently setup.  As you stated by default, a PowerShell script it will try to connect to the server using a trusted connection.  Our test and production servers, are in two seperate domains without trust (this is by design) and hence the centralized server, cannot gather data from both environments.  I have played around with the $server.ConnectionContext.set_LoginSecure($false) but could not get it to function correctly.  Any help would be greatly appreciated :)

I realise this is not a support channel, but I can see a lot of value with this way of monitoring, and are really looking forward to the next modules!

Keep up the good work,

Kind Regards,

Gustav

Friday, June 26, 2020 - 1:05:11 PM - Alejandro Cobar Back To Top (86055)

Greetings Gustav,

I'm glad you liked the article. The purpose of this initiative is to expand it with a ton of modules so that users can cherry-pick those that they find useful in a "plug and play" fashion.

Your feedback has given me some light to consider the development of a new module, so stay tuned ;)

Thursday, June 25, 2020 - 11:52:46 AM - Gustav Mulder Back To Top (86043)

Hi,

Thanks for a great article.  Will implement it on our servers.  Is it possible to log user activity on databases with this or another module in PS?  Would like to know the IP address, PC hostname and query ran with result.

Keep well,

Gustav














get free sql tips
agree to terms