In our organization, we have a lot of clustered SQL Server installations. SSIS is usually installed on each of the cluster nodes. The problem is that SSIS is not cluster aware like the rest of SQL Server is. Because of this, we ended up receiving multiple alerts from SCOM when there were SSIS job failures (this is just one of the monitors that is in the SCOM management pack for SQL Server). When an error occurred, we would receive alerts from the following:
- The node where the error occurred (SERVER1)
- The cluster group (MYCLUSTER)
- The SQL Server cluster group (MYSQLCLUSTER)
Needless to say, this was annoying for our SQL admins. With a helpful tip from Kevin Holman, I implemented the following simple solution:
- Create a group in SCOM that is populated with all cluster virtual server objects (I put this in a management pack that is authored in VSAE and is sealed for reuse throughout the environment)
- Apply overrides targeted at the cluster virtual servers group to disable SSIS discovery. Once the discovery is disabled on the clustered instances of SSIS are out of the environment, we will no longer get duplicate alerts. This ensures that we only receive the alert from the actual node where the error occurred.
The details are listed below.
Create a Dynamic Group
Here is the fragment that I implemented in my shared groups management pack. This management pack is authored in Visual Studio. The management pack name is “COMPANYNAME.Groups”, and the rest of the items build on the namespace from there. The result will be a group called “All Cluster Virtual Servers Group”.
<ManagementPackFragment SchemaVersion=”2.0″ xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<ClassType ID=”CompanyXYZ.Groups.Group.AllClusterVirtualServers” Base=”SCIG!Microsoft.SystemCenter.InstanceGroup” Accessibility=”Internal” Abstract=”false” Hosted=”false” Singleton=”true”>
<Discovery ID=”CompanyXYZ.Groups.Discovery.AllClusterVirtualServers.Group” Target=”CompanyXYZ.Groups.Group.AllClusterVirtualServers” Enabled=”true” ConfirmDelivery=”false” Remotable=”true” Priority=”Normal”>
<DiscoveryClass TypeID=”MWCL!Microsoft.Windows.Cluster.VirtualServer” />
<DiscoveryRelationship TypeID=”SCIG!Microsoft.SystemCenter.InstanceGroupContainsEntities” />
<DataSource ID=”DS” TypeID=”SC!Microsoft.SystemCenter.GroupPopulator”>
<!– GUID of the discovery. We will use $MPElement$ variable which specifies the current discovery. –>
<LanguagePack ID=”ENU” IsDefault=”true”>
<!– The name of the group that will appear to users in the console. –>
<Name>All Cluster Virtual Servers Group</Name>
<Description>This group contains all cluster virtual server instances</Description>
<Description>Discovery for the ‘CompanyXYZ.Groups.Group.AllClusterVirtualServers’ group.</Description>
Once you have the management pack created, seal it up and deploy it to your SCOM management group. I recommend always testing this in a DEV environment prior to putting in your production SCOM. After the management server has had time to do group population (tip on forcing group population), ensure that the group contains all of your cluster group objects.
Click here to download the code sample above
Apply the Overrides
The next step is to apply the overrides to disable the discovery for SSIS on all cluster virtual servers. This is done by targeting the override at the group we created above.
- Select the “Management Pack Objects” node in the Authoring tab navigation tree
- Limit the scope to the 3 versions of integration services
Next we need to disable all of the discovery for SSIS for any object that is a cluster virtual server. We use our new ‘All Cluster Virtual Servers Group’ group to apply the override. Click on the Object Discoveries node in the navigation tree to view all of the discoveries targeted at SSIS.
- Right click the discovery, then select Overrides -> Override the object discovery -> For a Group…
- Find the ‘ All Cluster Virtual Servers Group’ group
- Override the ‘Enabled’ property to False, put in a description of the override, save into a unsealed customizations MP that is specific to the MP that the disocvery is defined in (e.g. “SQL Server 2005 (Discovery) – CompanyXYZ Customizations”)
- Repeat for each SQL version
- Lastly, if SSIS has already been discovered on cluster virtual servers in your environment (which I am assuming it has since you are still reading this), then you need to clean up the discovered class instances. This can be done in SCOM 2012 R2 using the Remove-SCOMDisabledClassInstance cmdlet. It does not need any parameters. This command will likely take a few minutes to run. Once it has completed, you can verify that the discovered instances on virtual servers are now gone by using the Discovered Inventory view