Automate SQL Express Instance removal from SCOM with PowerShell – Ruben Zimmermann

Ruben is back again with another Powershell banger! This time he presents you a Powershell script that will automatically detect and remove the SQL Express Instances from SCOM monitoring and save you from unnecessary overhead of removing them manually!

Introduction

SQL Express Databases are a widely used storage for settings in applications or as data storage for small amount of data. Except of backups in cases it is not required to manage those databases.

The MS SQL Server Management Pack for SCOM discovers any edition. Thus, we can spot Express databases from the SCOM Console.

Unfortunately, the Management Pack can’t monitor Express databases and lots of unfixable alerts are thrown.

Manual solution

As described by Tim McFadden here https://www.scom2k7.com/disabling-sql-express-instance-discoveries/

or by  Kevin Holman here

https://kevinholman.com/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database/

It is possible to either set filter strings to prevent the discovery for all Express instances by name.

This does not work if the Express is named as MSSQLSERVER.

MSSQLSERVER is also the default for SQL Standard and other editions.

Only choice then is to override object by object manually, or?

PowerShell solution

With a bit of PowerShell it is possible to override the discovery rules for Express editions no matter which name they have. – Put this script into your regular maintenance scripts to keep your SCOM free from Express instances:

# Defining Override Management Pack. - It needs to be created before starting.

$overrideMP = Get-SCOMManagementPack -DisplayName 'Custom.SQL.Server.Express.Removals'

# Get all Windows Servers Instances, needed as lookup to disable the specific computer object

$winSrvClass     = Get-SCOMClass -Name Microsoft.Windows.Server.Computer

$winSrvInstances = Get-SCOMClassInstance -Class $winSrvClass

# Get Express instances For SQL 2005 to SQL 2012

$classSQL2to8     = Get-SCOMClass -Name Microsoft.SQLServer.DBEngine

$instancesSQL2to8 = Get-SCOMClassInstance -Class $classSQL2to8

$expressSQL2to8   = $instancesSQL2to8 | Where-Object {$_.'[Microsoft.SQLServer.DBEngine].Edition'.Value  -eq 'Express Edition' }

$computersSQL2to8 = $expressSQL2to8.path

# Finding the computer objects which host SQL 2005 to SQL2012, store them in ArrayList

$targetComputers = New-Object -TypeName System.Collections.ArrayList

$computersSQL2to8 | ForEach-Object {

    $tmp   = ''

    $check = $false

    $check = $winSrvInstances.DisplayName.Contains($_)   

    if ($check) {

        $number = $winSrvInstances.DisplayName.IndexOf($_)

        $tmp    = $winSrvInstances[$number]

        if ($tmp -ne '') {       

            $targetComputers.Add($tmp)

        }   

    }

} #end $computersSQL2to8 | ForEach-Object { }

# Disabling the Dicovery Rules for those computers which host SQL 2005 to SQL 2012

$discoveryRuleList = @(

  'Discover SQL Server 2005 Database Engines (Windows Server)'

  'Discover SQL Server 2008 Database Engines (Windows Server)'

 'Discover SQL Server 2012 Database Engines (Windows Server)'

)

foreach ($discoveryRuleItem in $discoveryRuleList) {

$discoveryRule = Get-SCOMDiscovery -DisplayName $discoveryRuleItem

$targetComputers | ForEach-Object {

Disable-SCOMDiscovery -Instance $_ -Discovery $discoveryRule -ManagementPack $overrideMP

    }

}

#Removing the objects from SCOM. - This can take some Time!

Remove-SCOMDisabledClassInstance

# Get Express instances for SQL 2014

$classSQL2014     = Get-SCOMClass -Name 'Microsoft.SQLServer.2014.DBEngine'

$instancesSQL2014 = Get-SCOMClassInstance -Class $classSQL2014

$expressSQL2014   = $instancesSQL2014 | Where-Object {$_.'[Microsoft.SQLServer.2014.DBEngine].Edition'.Value  -eq 'Express Edition' }

$computersSQL2014 = $expressSQL2014.path

# Finding the computer objects which host SQL 2014, store them in ArrayList

$targetComputers   = New-Object -TypeName System.Collections.ArrayList

$computersSQL2014 | ForEach-Object {

    $tmp   = ''

    $check = $false

    $check = $winSrvInstances.DisplayName.Contains($_)

    if ($check) {

        $number = $winSrvInstances.DisplayName.IndexOf($_)

        $tmp    = $winSrvInstances[$number]

        if ($tmp -ne '') {       

            $targetComputers.Add($tmp)

        }   

    }

} #end $computersSQL2014 | ForEach-Object { }

# Disabling the Dicovery Rule for those computers which host SQL 2014

$discoveryRule = Get-SCOMDiscovery -DisplayName 'MSSQL 2014: Discover SQL Server 2014 Database Engines'

$targetComputers | ForEach-Object {

Disable-SCOMDiscovery -Instance $_ -Discovery $discoveryRule -ManagementPack $overrideMP

}

#Removing the objects from SCOM. - This can take some Time!

Remove-SCOMDisabledClassInstance

# Get Express instances for SQL 2016

$classSQL2016     = Get-SCOMClass -Name 'Microsoft.SQLServer.2016.DBEngine'

$instancesSQL2016 = Get-SCOMClassInstance -Class $classSQL2016

$expressSQL2016   = $instancesSQL2016 | Where-Object {$_.'[Microsoft.SQLServer.2016.DBEngine].Edition'.Value  -eq 'Express Edition' }

$computersSQL2016 = $expressSQL2016.Path

# Finding the computer objects which host SQL 2016, store them in ArrayList

$targetComputers   = New-Object -TypeName System.Collections.ArrayList

$computersSQL2016 | ForEach-Object {

    $tmp = ''

    $check = $false    

    $check = $winSrvInstances.DisplayName.Contains($_)

    if ($check) {

        $number = $winSrvInstances.DisplayName.IndexOf($_)

        $tmp = $winSrvInstances[$number]

        if ($tmp -ne '') {       

            $targetComputers.Add($tmp)

        }

    }

} #end $computersSQL2016 | ForEach-Object { }

# Disabling the Dicovery Rule for those computers which host SQL 2016

$discoveryRule = Get-SCOMDiscovery -DisplayName 'MSSQL 2016: Discover SQL Server 2016 Database Engines'

$targetComputers | ForEach-Object {

    Disable-SCOMDiscovery -Instance $_ -Discovery $discoveryRule -ManagementPack $overrideMP

}

#Removing the objects from SCOM. - This can take some Time!

Remove-SCOMDisabledClassInstance

Feedback is appreciated 😊

Warm regards

Ruben