How to Use PowerShell to Discover and Document SQL Server Instances

Want to learn how to get MSSQL server instances with PowerShell? In the IT world, managing and maintaining SQL Server instances is a critical task for IT professionals and Managed Service Providers (MSPs). Knowing how to efficiently gather information about SQL Server instances can significantly enhance administrative tasks and streamline processes. This blog post delves into a powerful PowerShell script designed to fetch a list of MSSQL server instances, providing detailed insights and practical applications.

Background

SQL Server instances are the backbone of many business applications, storing and managing vast amounts of data. IT professionals often need to identify and manage these instances to ensure optimal performance and security. The provided PowerShell script is an invaluable tool for quickly retrieving information about SQL Server instances on a system. This capability is crucial for tasks such as audits, troubleshooting, and system monitoring.

The Script:

#Requires -Version 5.1

<#
.SYNOPSIS
    Gets a list of MSSQL server instances and optionally save the results to a custom field.
.DESCRIPTION
    Gets a list of MSSQL server instances and optionally save the results to a custom field.
    The custom field can be either/both a multi-line or WYSIWYG custom field.

    SQL Server, SQL Server Developer and SQL Express are supported.

    SQL "Local" that are built into an application are not supported as they aren't an SQL Server instance.

    SQL service name that don't start with "MSSQL$" will not get detected.

    PS > Get-Service -Name "MSSQL`$*"
    Status   Name               DisplayName
    ------   ----               -----------
    Running  MSSQL$DB           SQL Server (DB)
    Running  MSSQL$DB01         SQL Server (DB01)
    Running  MSSQL$DB02         SQL Server (DB02)

.EXAMPLE
    (No Parameters)
    ## EXAMPLE OUTPUT WITHOUT PARAMS ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL

PARAMETER: -CustomFieldName "ReplaceMeWithAnyMultilineCustomField"
    Saves an text table to a multi-line Custom Field with a list of SQL instances.
.EXAMPLE
    -CustomFieldName "ReplaceMeWithAnyMultilineCustomField"
    ## EXAMPLE OUTPUT WITH CustomFieldName ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL

PARAMETER: -CustomFieldParam "ReplaceMeWithAnyWysiwygCustomField"
    Saves an html table to a Wysiwyg Custom Field with a list of SQL instances.
.EXAMPLE
    -WysiwygCustomFieldName "ReplaceMeWithAnyWysiwygCustomField"
    ## EXAMPLE OUTPUT WITH WysiwygCustomFieldName ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL
.OUTPUTS
    None
.NOTES
    Release Notes: Initial Release
By using this script, you indicate your acceptance of the following legal terms as well as our Terms of Use at https://www.ninjaone.com/terms-of-use.
    Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms. 
    Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party. 
    Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library or website belonging to or under the control of any other software provider. 
    Warranty Disclaimer: The script is provided “as is” and “as available”, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations. 
    Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks. 
    Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script. 
    EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).
#>

[CmdletBinding()]
param (
    [String]$CustomFieldName,
    [String]$WysiwygCustomFieldName
)

begin {
    function Test-IsElevated {
        $id = [System.Security.Principal.WindowsIdentity]::GetCurrent()
        $p = New-Object System.Security.Principal.WindowsPrincipal($id)
        $p.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
    }
    function Set-NinjaProperty {
        [CmdletBinding()]
        Param(
            [Parameter(Mandatory = $True)]
            [String]$Name,
            [Parameter()]
            [String]$Type,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            $Value,
            [Parameter()]
            [String]$DocumentName
        )
    
        $Characters = $Value | Measure-Object -Character | Select-Object -ExpandProperty Characters
        if ($Characters -ge 10000) {
            throw [System.ArgumentOutOfRangeException]::New("Character limit exceeded, value is greater than 10,000 characters.")
        }
        
        # If we're requested to set the field value for a Ninja document we'll specify it here.
        $DocumentationParams = @{}
        if ($DocumentName) { $DocumentationParams["DocumentName"] = $DocumentName }
        
        # This is a list of valid fields that can be set. If no type is given, it will be assumed that the input doesn't need to be changed.
        $ValidFields = "Attachment", "Checkbox", "Date", "Date or Date Time", "Decimal", "Dropdown", "Email", "Integer", "IP Address", "MultiLine", "MultiSelect", "Phone", "Secure", "Text", "Time", "URL", "WYSIWYG"
        if ($Type -and $ValidFields -notcontains $Type) { Write-Warning "$Type is an invalid type! Please check here for valid types. https://ninjarmm.zendesk.com/hc/en-us/articles/16973443979789-Command-Line-Interface-CLI-Supported-Fields-and-Functionality" }
        
        # The field below requires additional information to be set
        $NeedsOptions = "Dropdown"
        if ($DocumentName) {
            if ($NeedsOptions -contains $Type) {
                # We'll redirect the error output to the success stream to make it easier to error out if nothing was found or something else went wrong.
                $NinjaPropertyOptions = Ninja-Property-Docs-Options -AttributeName $Name @DocumentationParams 2>&1
            }
        }
        else {
            if ($NeedsOptions -contains $Type) {
                $NinjaPropertyOptions = Ninja-Property-Options -Name $Name 2>&1
            }
        }
        
        # If an error is received it will have an exception property, the function will exit with that error information.
        if ($NinjaPropertyOptions.Exception) { throw $NinjaPropertyOptions }
        
        # The below type's require values not typically given in order to be set. The below code will convert whatever we're given into a format ninjarmm-cli supports.
        switch ($Type) {
            "Checkbox" {
                # While it's highly likely we were given a value like "True" or a boolean datatype it's better to be safe than sorry.
                $NinjaValue = [System.Convert]::ToBoolean($Value)
            }
            "Date or Date Time" {
                # Ninjarmm-cli expects the GUID of the option to be selected. Therefore, the given value will be matched with a GUID.
                $Date = (Get-Date $Value).ToUniversalTime()
                $TimeSpan = New-TimeSpan (Get-Date "1970-01-01 00:00:00") $Date
                $NinjaValue = $TimeSpan.TotalSeconds
            }
            "Dropdown" {
                # Ninjarmm-cli is expecting the guid of the option we're trying to select. So we'll match up the value we were given with a guid.
                $Options = $NinjaPropertyOptions -replace '=', ',' | ConvertFrom-Csv -Header "GUID", "Name"
                $Selection = $Options | Where-Object { $_.Name -eq $Value } | Select-Object -ExpandProperty GUID
        
                if (-not $Selection) {
                    throw [System.ArgumentOutOfRangeException]::New("Value is not present in dropdown")
                }
        
                $NinjaValue = $Selection
            }
            default {
                # All the other types shouldn't require additional work on the input.
                $NinjaValue = $Value
            }
        }
        
        # We'll need to set the field differently depending on if its a field in a Ninja Document or not.
        if ($DocumentName) {
            $CustomField = Ninja-Property-Docs-Set -AttributeName $Name -AttributeValue $NinjaValue @DocumentationParams 2>&1
        }
        else {
            $CustomField = Ninja-Property-Set -Name $Name -Value $NinjaValue 2>&1
        }
        
        if ($CustomField.Exception) {
            throw $CustomField
        }
    }
    if ($env:multilineCustomFieldName -and $env:multilineCustomFieldName -notlike "null") {
        $CustomFieldName = $env:multilineCustomFieldName
    }
    if ($env:WysiwygCustomFieldName -and $env:WysiwygCustomFieldName -notlike "null") {
        $WysiwygCustomFieldName = $env:WysiwygCustomFieldName
    }
}
process {
    if (-not (Test-IsElevated)) {
        Write-Error -Message "Access Denied. Please run with Administrator privileges."
        exit 1
    }

    try {
        $InstanceNames = $(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" -ErrorAction Stop).InstalledInstances
        $SqlInstances = $InstanceNames | ForEach-Object {
            $SqlPath = $(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$_\Setup" -ErrorAction Stop).SQLPath
            $SqlServices = Get-Service -Name "MSSQL`$$_" -ErrorAction Stop
            $SqlService = $SqlServices | Where-Object { $_.Name -notlike $SqlServices.DependentServices.Name -and $_.Name -notlike "SQLTelemetry*" }
            [PSCustomObject]@{
                Status   = $SqlService.Status
                Service  = $SqlService.DisplayName
                Instance = $_
                Path     = $SqlPath
            }
        }
    }
    catch {
        Write-Host "[Error] $($_.Message)"
        Write-Host "[Info] Likely no MSSQL instance found."
        exit 1
    }

    $SqlInstances | Out-String | Write-Host

    if ($CustomFieldName) {
        Write-Host "Attempting to set Custom Field '$CustomFieldName'."
        Set-NinjaProperty -Name $CustomFieldName -Value ($SqlInstances | Out-String)
        Write-Host "Successfully set Custom Field '$CustomFieldName'!"
    }

    if ($WysiwygCustomFieldName) {
        try {
            Write-Host "Attempting to set Custom Field '$WysiwygCustomFieldName'."
            $htmlReport = New-Object System.Collections.Generic.List[String]
            $htmlReport.Add("<h1>SQL Server Instances</h1>")
            $htmlTable = $SqlInstances | ConvertTo-Html -Fragment 
            $htmlTable = $htmlTable -replace "<tr><td>Running</td>", '<tr class="success"><td>Running</td>'
            $htmlTable = $htmlTable -replace "<tr><td>StartPending</td>", '<tr class="other"><td>StartPending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>ContinuePending</td>", '<tr class="other"><td>ContinuePending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>Paused</td>", '<tr class="other"><td>Paused</td>'
            $htmlTable = $htmlTable -replace "<tr><td>PausePending</td>", '<tr class="other"><td>PausePending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>Stopped</td>", '<tr class="danger"><td>Stopped</td>'
            $htmlTable = $htmlTable -replace "<tr><td>StopPending</td>", '<tr class="danger"><td>StopPending</td>'
            $htmlTable | ForEach-Object { $htmlReport.Add($_) }
            Set-NinjaProperty -Name $WysiwygCustomFieldName -Value ($htmlReport | Out-String)
            Write-Host "Successfully set Custom Field '$WysiwygCustomFieldName'!"
        }
        catch {
            Write-Error $_
            Write-Host "[Error] $($_.Message)"
            exit 1
        }
    }
    exit 0
}
end {
    
    
    
}

 

Detailed Breakdown

The script starts by checking if it runs with administrator privileges, which is necessary for accessing certain system properties. It defines a function, Test-IsElevated, to verify this. If the script is not run as an administrator, it exits with an error.

Next, it attempts to retrieve the installed SQL Server instances from the Windows Registry. This is achieved through the Get-ItemProperty cmdlet, which accesses the relevant registry path. The script then iterates through each instance, fetching details such as the instance name, service status, and installation path.

The gathered information is structured into a custom object and displayed. If specified, the results can also be saved to custom fields, either as plain text or HTML, using the Set-NinjaProperty function. This function handles various data types and ensures that values conform to character limits and other constraints.

Potential Use Cases

Consider an IT professional managing multiple servers across an organization. By using this script, they can quickly gather and document all SQL Server instances running on each server. This information is vital for tasks such as updating software, performing security audits, and planning capacity. For example, during an upgrade cycle, knowing all active instances helps in scheduling and minimizing downtime.

Comparisons

This PowerShell script offers a streamlined and automated way to gather SQL Server instance information compared to manual methods. Traditionally, administrators might use SQL Server Management Studio (SSMS) or manually check services via the Windows Services console. While effective, these methods are time-consuming and prone to human error. In contrast, the script provides a consistent, repeatable process that can be easily integrated into broader IT management workflows.

FAQs

Q: Can this script detect SQL Server instances installed as part of an application?

A: No, the script is designed to detect standard SQL Server instances. Instances embedded within applications are not supported.

Q: What happens if a custom field name exceeds the character limit?

A: The script includes a check to ensure that values do not exceed 10,000 characters. If this limit is exceeded, an error is thrown.

Q: Can this script be run on any version of PowerShell?

A: The script requires PowerShell version 5.1 or higher.

Implications

Running this script provides IT professionals with detailed insights into SQL Server deployments across their infrastructure. This information is crucial for ensuring compliance, optimizing performance, and enhancing security. By knowing exactly where SQL Server instances are running, administrators can better protect sensitive data and ensure that systems are properly configured and maintained.

Recommendations

• Run as Administrator: Ensure that the script is executed with the necessary privileges to access system information.

• Regular Audits: Schedule regular runs of the script to keep an up-to-date inventory of SQL Server instances.

• Integrate with Monitoring Tools: Consider integrating the script’s output with monitoring and documentation tools to streamline system management.

Final Thoughts

The PowerShell script discussed in this post is a powerful tool for IT professionals tasked with managing SQL Server instances. By automating the discovery process, it saves time and reduces the risk of errors, making it an essential addition to any IT toolkit. Tools like NinjaOne can further enhance this process by providing robust management and monitoring capabilities, ensuring that your SQL Server instances are always under control and secure.