In my environment the DBA team receive all SQL alerts from SCOM, but there are some SQL installations that the team doesn’t manage. They don’t want to receive alerts for the ‘non-managed’ SQL installations, which are mostly SQL express. However, we still need to monitor everything else on those servers. In addition, any new SQL installations should be automatically discovered by SCOM.
Disabling the SQL discoveries for all SQL servers, then enabling them for the servers that should be managed would be cleaner. This way, we wouldn’t receive alerts about missing permissions from new SQL installations. BUT – the SQL team want to have new SQL installations discovered by SCOM, without having to place the servers into a group first. So, my approach was to create a group for the non-managed SQL servers, then override and disable the SQL seed discoveries for that group. If you do want to disable SQL discoveries for all servers, check out Marnix Wolf’s blog on the subject.
First create a group for the non-managed SQL servers:
In the Authoring Pane, create a new group. Give the group a name and description, and select the management pack to store the group in. Remember that the group and override will be placed in the same management pack.
Add the servers with non-managed SQL installations to the group. Use the Windows Computer object (the SQL seed discoveries are targeted at Windows Server, which is a child class of Windows Computer).
After adding the servers, click next a few times to complete the wizard.
Next, override the seed discovery for each version of SQL, using the group created in the previous step:
Discoveries to disable:
SQL Server 2008 DB Installation Discovery Source
SQL Server 2005 DB Installation Discovery Source
SQL Server 2012 DB Installation Discovery Source
MSSQL 2014: Discover SQL Server 2014 DB Installation Source (seed)
MSSQL 2016: Discover SQL Server 2016 DB Installation Source (seed)
In the Authoring Pane, select Object Discoveries then click the scope button.
Scope the view to SQL Server Installation Seed for each version of SQL:
Right click each discovery and override for a group:
Select the group you created earlier, and change Enabled to False:
Complete these steps for each discovery.
Finally, run the powershell command to remove the previously discovered SQL objects from the SCOM database:
This command will remove all discovered sql components for the group, which will also result in the servers being removed from the built in SQL groups (eg. “SQL group name computers”).
The following SQL query lists discovered SQL objects for a SQL server (replace servername.domain with the FQDN of the SQL server):
FROM BaseManagedEntity BME LEFT JOIN ManagedTypeView MTV ON BME.BaseManagedTypeId=MTV.Id
WHERE MTV.Name LIKE '%sqlserver%' AND (BME.FullName LIKE '%Servername.domain%' OR BME.Path LIKE '%Servername.domain%')
ORDER BY BME.TimeAdded DESC
Running this query against a server before Remove-SCOMDisabledClassInstance shows the following objects are discovered:
After running Remove-SCOMDisabledClassInstance we can see the IsDeleted flag has changed to 1. These objects will be groomed out of the database in a few days.
Reporting and integration services have separate discoveries, which is why the IsDeleted flag isn’t set for them in the above screenshot. If needed, these discoveries can also be disabled for the group.