mssqlserver.ps1 - powershell script by ActiveXperts Software

mssqlserver.ps1 checks whether MS SQL Server is running; it checks services, processes and performance counters.

Use mssqlserver.ps1 directly from ActiveXperts Network Monitor; in the Manager's 'Monitor' menu, select 'New Check (Script)' and select mssqlserver.ps1. Configure the required parameter, or press 'Load a working sample'.

In ActiveXperts Network Monitor, Administrators can use three different scripting languages: Powershell, VBScript and SSH.

---

mssqlserver.ps1 script code

#################################################################################
# ActiveXperts Network Monitor PowerShell script, © ActiveXperts Software B.V.
# For more information about ActiveXperts Network Monitor, visit the ActiveXperts 
# Network Monitor web site at http://www.activexperts.com
#################################################################################
# Script:
#     MsSqlServer.ps1
# Description: 
#     Checks if MS SQL Server is running; it checks the MS SQL services 
#     and MS SQL processes
# Declare Parameters:
#     1) strHost (string) - Hostname or IP address of the server you want to check
#     2) strVersion (string) - SQL Server version
#     3) strAltCredentials (string, optional) - Specify an empty string to use Network Monitor service credentials.
#         To use alternate credentials, enter a server that is defined in Server Credentials table.
#         (To define Server Credentials, choose Tools->Options->Server Credentials)
# Usage:
#     .\MsSqlServer.ps1 '<Hostname | IP>' '<Version>' '[alt-credentials]'
# Sample:
#     .\MsSqlServer.ps1 'localhost' '2008'
#################################################################################

# -- Declare Parameters
param ( [string]$strHost = '', [string]$strVersion = '', [string]$strAltCredentials = '' )

# -- Use _activexperts.ps1 with common functions
. 'C:\Program Files\ActiveXperts\Network Monitor\Scripts\Monitor (ps1)\_activexperts.ps1' 


#################################################################################
# // --- Private Functions section ---
#################################################################################

function getSoftwareInfo( $strVersion, [ref]$lstServices, [ref]$lstProcesses, [ref]$lstPerfCounters )
{
  # Retrieve services, processes and counters associated to the specific version of the software. Entries that start 
  # with '!' are optional services/processes/counters and are not checked by default. Remove '!' mark to enable monitoring those items.
  switch( $strVersion )
  {
    '2012'
    {
       $lstServices.value = @( @( 'MSSQLSERVER', 'SQL Server (MSSQLSERVER)' ), 
                               @( '!SQLSERVERAGENT', 'SQL Server Agent (MSSQLSERVER)' ), 
                               @( 'MSSQLServerOLAPService', 'SQL Server Analysis Services (MSSQLSERVER)' ), 
                               @( '!SQLBrowser', 'SQL Server Browser' ), 
                               @( '!SQL Server Distributed Replay Controller' )
                               @( 'SQL Server Integration Services 11.0' )
                               @( 'ReportServer', 'SQL Server Reporting Services (MSSQLSERVER)' ), 
                               @( 'SQLWriter', 'SQL Server VSS Writer' ),
                               @( '', '' ) )
       $lstProcesses.value = @( 'sqlservr.exe', '!sqlagent.exe', 'msmdsrv.exe', '!sqlbrowser.exe', '!DReplayController.exe', 'MsDtsSrvr.exe', 'ReportingServicesService.exe', 'sqlwriter.exe', '' )
       $lstPerfCounters.value = @( @( 'Memory', '', 'Available Bytes', '-gt 104857600' ), 
                                   @( 'Processor', '_TOTAL', '% Processor Time', '-lt 90' ),
                                   @( '', '', '', '' ) )
       return $AXSUCCESS
     }
     
    '2008'
    {
       $lstServices.value = @( @( 'MSSQLSERVER', 'SQL Server (MSSQLSERVER)' ),
                               @( '!SQLSERVERAGENT', 'SQL Server Agent (MSSQLSERVER)' ),
                               @( 'MSSQLServerOLAPService', 'SQL Server Analysis Services (MSSQLSERVER)' ),
                               @( '!SQLBrowser', 'SQL Server Browser' ),
                               @( 'ReportServer', 'SQL Server Reporting Services (MSSQLSERVER)' ),
                               @( 'SQLWriter', 'SQL Server VSS Writer' ),
                               @( '', '' ) )
       $lstProcesses.value = @( 'sqlservr.exe', '!sqlagent.exe', 'msmdsrv.exe', '!sqlbrowser.exe', 'ReportingServicesService.exe', 'sqlwriter.exe', '' )
       $lstPerfCounters.value = @( @( 'Memory', '', 'Available Bytes', '-gt 104857600' ), 
                                   @( 'Processor', '_TOTAL', '% Processor Time', '-lt 90' ),
                                   @( '', '', '', '' ) )
       return $AXSUCCESS
     }
     
    '2005'
    {
       $lstServices.value = @( @( 'MSSQLSERVER', 'SQL Server (MSSQLSERVER)' ),
                               @( '!SQLSERVERAGENT', 'SQL Server Agent (MSSQLSERVER)' ),
                               @( 'MSSQLServerOLAPService', 'SQL Server Analysis Services (MSSQLSERVER)' ),
                               @( '!SQLBrowser', 'SQL Server Browser' ),
                               @( 'msftesql', 'SQL Server FullText Search (MSSQLSERVER)' ),
                               @( 'ReportServer', 'SQL Server Reporting Services (MSSQLSERVER)' ),
                               @( '!SQLWriter', 'SQL Server VSS Writer' )
                               @( '', '' ) )
       $lstProcesses.value = ( 'sqlservr.exe', '!sqlagent.exe', 'msmdsrv.exe', '!sqlbrowser.exe', 'msftesql.exe', 'ReportingServicesService.exe', '!sqlwriter.exe', '' )
       $lstPerfCounters.value = @( , ( 'Memory', '', 'Available Bytes', '-gt 104857600' ), 
                                     ( 'Processor', '_TOTAL', '% Processor Time', '-lt 90' ) )
       return $AXSUCCESS
     }
     
     '2000'
     {
        $lstServices.value = @( @( 'MSSQLSERVER', 'Microsoft SQL Server' ),
                                @( '', '' ) )
        $lstProcesses.value = @( 'sqlservr.exe', 'sqlmangr.exe', '' )
        $lstPerfCounters.value = @( @( 'Memory', '', 'Available Bytes', '-gt 104857600' ), 
                                    @( 'Processor', '_TOTAL', '% Processor Time', '-lt 90' ),
                                    @( '', '', '', '' ) )
        return $AXSUCCESS
      }
      
     'Express'
     {
        $lstServices.value = @( @( 'MSSQL$SQLEXPRESS', 'SQL Server (SQLEXPRESS)' ),
                                @( '', '' ) )
        $lstProcesses.value = @( 'sqlservr.exe', 'sqlbrowser.exe', 'sqlwriter.exe', '' )
        $lstPerfCounters.value = @( @( 'Memory', '', 'Available Bytes', '-gt 104857600' ), 
                                    @( 'Processor', '_TOTAL', '% Processor Time', '-lt 90' ),
                                    @( '', '', '', '' ) )
        return $AXSUCCESS
      }
      
    default
    {
      $lstServices.value = @( , @( '', '' ) )
      $lstProcesses.value = @( , '' )
      $lstPerfCounters.value = @( , @( '', '', '', '' ) )
      return $AXERROR
    }
  }
}


#################################################################################
# // --- Main script ---
#################################################################################

# -- Clear screen and clear error
cls
$Error.Clear()

# -- Validate parameters, return on parameter mismatch
if( $strHost -eq '' -or $strVersion -eq '' )
{
  $res = 'UNCERTAIN: Parameter error - Usage: .\MsSqlServer.ps1 "<Hostname | IP>" "<Version>" "[alt-credentials]"'
  echo $res
  exit
}

# -- Declare local variables by assigning initial value
$lstServices = $null
$lstProcesses = $null
$lstPerfCounters = $null

$objAltCredentials = $null
$strExplanation = ''


# If alternate credentials are specified, retrieve the alternate login and password from the ActiveXperts global settings
if( $strAltCredentials -ne '' )
{
  # Get the Alternate Credentials object. Function "AxGetCredentials" is implemented in "activexperts.ps1"
  if( ( AxGetCredentials $strHost  $strAltCredentials ([ref]$objAltCredentials) ([ref]$strExplanation) ) -ne $AXSUCCESS )
  {
    echo $strExplanation
    exit
  }
}

# Retrieve product processes/services/performance-counters list
if( ( getSoftwareInfo $strVersion ([ref]$lstServices) ([ref]$lstProcesses) ([ref]$lstPerfCounters) ) -ne $AXSUCCESS )
{ 
  $res = 'ERROR: Version [' + $strVersion + '] is not supported.'
  echo $res
  exit
}

# -- Check services
if( ( AxCheckServices $strHost ([ref]$lstServices) $objAltCredentials ([ref]$strExplanation) ) -ne $AXSUCCESS )
{
  echo $strExplanation
  exit
}

# -- Check processes
if( ( AxCheckProcesses $strHost ([ref]$lstProcesses) $objAltCredentials ([ref]$strExplanation) ) -ne $AXSUCCESS )
{
  echo $strExplanation
  exit
}

# -- Check performance counters
if( ( AxCheckPerfCounters $strHost ([ref]$lstPerfCounters) $objAltCredentials ([ref]$strExplanation) ) -ne $AXSUCCESS )
{
  echo $strExplanation
  exit
}

# -- Print script result
$res = 'SUCCESS: All services, processes and counters successfully checked'
echo $res
exit


#################################################################################
# // --- Catch script exceptions ---
#################################################################################

trap [Exception]
{
  $res = 'UNCERTAIN: ' + $_.Exception.Message
  echo $res
  exit
}