Build Your Own SQL Server Central Monitoring Solution with PowerShell

By: Alejandro Cobar   |   Updated: 2022-10-31   |   Comments (1)   |   Related: More > Database Administration

Problem

As a production DBA, I manage hundreds of SQL Server instances to meet business requirements. It is difficult to monitor these instances efficiently since there is limited support, and my company currently doesn't have the budget to invest in third-party monitoring software. Is there anything that can help me easily manage all of the SQL Server instances in my environment?

Solution

Automation with PowerShell may be the answer to your problem. Using what we've learned so far through this PowerShell series, it's possible to build scripts to help manage multiple SQL Server instances simultaneously. This approach will make your life easier by building custom automation for your specific needs.

Assumptions

There are a few assumptions to consider before we begin:

1. Proficient with PowerShell
2. SQL Server PowerShell modules install in your environment. If not, please refer to this tip: PowerShell for the DBA - CMDLETs and Advanced Functions
3. Access to SQL Server Developer Edition test instances (2017 and 2019), as this is what the tip examples will be run against.

Setting Up the Initial Script

This initial script will act as the core script in which all the subsequent scripts can revolve. Of course, this is just one approach, feel free to build it in any way you think will work best for your situation. The main idea is that this initial script will return the list of SQL Server instances to connect to for a specific action.

For this article, let's keep it simple, the list of instances will be in a .txt file that the script will read and then return. As a best practice, keeping an updated list of SQL Server instances under your umbrella is highly recommended, a master server list, if you will.

The image below shows the sample instances .txt file:

As mentioned before, keeping it simple. Each row of elements, separated by a comma, depicts the following:

• Host name
• Instance name (MSSQLSERVER for the default instance)
• IP address currently assigned to the host
• Specific port used by the instance

Now, here is the code for the core script:

param(
    [Parameter(Position=0,mandatory=$true)]
    $instanceFile
)
 
if(Test-Path -Path $instanceFile){
    foreach($line in Get-Content $instanceFile) {
        $line
    }
}else{
    Write-Host "File doesn't exist, please provide a valid path"
}

In a nutshell, a mandatory parameter indicating the location of the instances file must be provided by the user. The file path is evaluated to determine if the file indeed exists. If it doesn't, the user will be notified, and the script will end its execution. If it exists, all the lines in the file will be returned.

Here's what a few executions look like:

Creation of Scripts for Specific Tasks

Now that a basic code script has been established, let's create a few specific sample scripts to help your particular needs.

Script to Connect to Each SQL Server Instance

Let's create a simple script to connect to all SQL Server instances to determine if there's any connectivity issue:

$coreScript = “C:\temp\Core.ps1 instances.txt"
$instances = $(Invoke-Expression $coreScript)
 
foreach($instanceRow in $instances){
    $instanceInfo = $instanceRow.Split(',')
    
    $sqlHost  = $instanceInfo[0]
    $instance = switch ($instanceInfo[1]){ 'MSSQLSERVER'{$sqlHost} Default{$instanceInfo[0]+'\'+$instanceInfo[1]} }
    $ip       = $instanceInfo[2]
    $port     = $instanceInfo[3]
 
    try{
        $result = $(Invoke-Sqlcmd -Query "SELECT 1" -ServerInstance $instance -ErrorAction:SilentlyContinue)
        
        Write-Host $instance": Success!" -BackgroundColor Green -ForegroundColor White
    }catch{
        Write-Host $instance": Fail!" -BackgroundColor Red -ForegroundColor White
    }
}

The first two lines pull the content from the core script and store the result in the $instances variable. From this point forward, I'll leave you to examine the code to flex those PowerShell skills a bit ;)

Below are screenshots of some execution samples made in my test environment:

Successful execution:

Failed attempt:

Script to Retrieve a List of Databases from Each SQL Server Instance

Here's another sample script to perform another basic task. This script will retrieve the list of databases that are housed within each SQL Server instance.

$coreScript = “C:\temp\Core.ps1 instances.txt"
$instances = $(Invoke-Expression $coreScript)
 
foreach($instanceRow in $instances){
    $instanceInfo = $instanceRow.Split(',')
    
    $sqlHost  = $instanceInfo[0]
    $instance = switch ($instanceInfo[1]){ 'MSSQLSERVER'{$sqlHost} Default{$instanceInfo[0]+'\'+$instanceInfo[1]} }
    $ip       = $instanceInfo[2]
    $port     = $instanceInfo[3]
 
    try{
        $databases = $(Invoke-Sqlcmd -Query "SELECT * FROM sys.databases" -ServerInstance $instance -ErrorAction:SilentlyContinue)
        
        Write-Host "Instance:"$instance
        foreach($database in $databases){
            $database.name + ' -> ' + $database.state_desc
        }
 
        Write-Host "----------------------------------------"
    }catch{
        Write-Host $_
    }
}

Again, the first two lines pull the list of instances from the core script. Next, it will attempt to connect to each SQL Server instance, retrieve the database list, and display each database's current status.

Note: The $databases object contains even more data, allowing you to experiment.

Here's a sample screenshot from my environment:

These sample scripts seem very basic, but by using the same principles discussed in this tip, you can begin to build custom automation for your specific needs.

As a next step, try to:

• Back up all the databases in each instance using a single script.
• Retrieve a list of disabled agent jobs in each instance.
• Retrieve the top three largest databases from each instance.
• Retrieve a list of agent jobs that have failed in the past 24 hours.
• Retrieve a list of logins, per instance, that have sysadmin privileges assigned.

Using these basic sample scripts as a starting point, you can continue to build scripts to help manage SQL Server instances and ultimately make your life easier.

Next Steps

• So you might be wondering, now that I have my fancy scripts, how can I schedule them to run automatically? In my next article, we're going to explore exactly that, so stay tuned!
• Automation is something really powerful that can make our day-to-day smoother, but it requires a time investment from our end to make it work; in the end, it is totally worth it.
• Remember that the approach used to demonstrate the idea is just one of hundreds. I'm sure you will come up with more clever implementations, but I sincerely hope you can use this as your stepping stone.
• You can download the scripts presented in this article here.

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