Monitoring SQL Server with PowerShell Core Object Setup
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.
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.
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:
- 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
- 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
- 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
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
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
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.
The PowerShell script that creates the above objects and inserts data into the inventory.MasterServerList table is called:
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.
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.
Navigate to the folder where you created the files and you can run the PowerShell script as follows:
- Right click on Create-Master-Server-List.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
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.
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.
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.
- Check out Part 2 - Monitoring SQL Server with PowerShell Instance Data Collection
- Stay tuned for more modules.
About the author
View all my tips
Article Last Updated: 2020-06-24