I have had several blog posts in the past discussing how to write rules and monitors against multi-instance objects. Special care must always be taken when writing workflows against classes where an agent can host more than one instance of the same class type. Examples would be Logical Disk, SQL DB Engine, SQL Database, etc.
Some of the previous articles:
So – the rest of this post will be an example on how to collect the database size.
I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005 database objects, and one to collect for SQL 2008 database objects. The reason I am doing this – is because if I targeted generically “SQL Database” – the next version of SQL would be included in this parent class, but might use a different object/counter down the road. So I will stick to known versions and perf counters. So I will create my rule targeting “SQL Server 2005 DB ” and “SQL 2008 DB”
The first step in creating a Management Pack – is to open your existing custom SQL workflow MP into the Authoring Console, or create a new empty Management Pack.
I will create a new empty MP and give it the ID of “Microsoft.SQLServer.2008.Monitoring.Addendum” and Display Name of “SQL Server 2008 (Monitoring) Addendum”. Once you save it – go to File – Management Pack Properties. We need to version our MP (increment by 1) since we will be changing it, or assign a new version number.
We also need to add a reference here if we don’t already have it – to ensure this MP has a reference for the SQL 2008 Discovery MP. This will allow us to choose SQL Classes later on when targeting our collection rules. Click the references tab, and add the SQL 2008 discovery MP if it isn't already present:
Then go to Health Model, Rules, New, Collection, Performance Based, Windows Performance Collection.
The first step is give my rule an ID. This will be the ID of the management pack, plus some additional text. It defaults to “NewElement” and we need to change that:
I will call mine “Microsoft.SQLServer.2008.Monitoring.Addendum.CollectSQL2008DBSize”
Give the rule a display name that is in accordance with your custom rule naming standard
Under “Target” – Browse all classes and find the “Microsoft.SQLServer.2008.Database” class.
Under Category – change to Performance Collection.
When completed – here is how mine appears:
Click Next.
On this screen – we have the option to type in the performance counter, object, and instance we want to collect.
Great care should be taken here. This is because the SQL DB Engine is a multi-instance object, and each instance appears differently in Perfmon. If we don’t choose the correct object here – then we wont collect the data from all of our instances. Let me explain.
In a “default instance” of SQL – the perf counter looks like this:
In a Named instance – it appears like the following:
If we typed in “SQLServer:Databases” we would only collect from the default instances of SQL in the environment. If we typed in “MSSQL$I01:Databases” we would only collect the data from identically named instances in the environment. However – we want to collect this from ALL instances. In that case – we need to use a VARIABLE in the performance counter object – since the actual object names vary in Perfmon. We can cheat by looking at a some other perf collection rules in the SQL MP and see how they handled this…. or we can look in discovered inventory and see if there is a good class property of our chosen class to handle this.
It just so happens that the SQL DB Engine class – has a property called “Performance Counter Object Name” that was created specifically for this purpose! If you look at this value in discovered inventory, you can see these correspond perfectly with what we need:
Sweet! And if you spot check a few Perf Collection rules in the SQL MP using our same target class, you’d find they also use this.
So – back to the authoring console – we need to use this object, as a variable, for our Perfmon Object. Here is how: There is a fly-out on the right – this will show all the class properties based on our target. In this specific case – our class target is “database”. The Database is hosted by a SQL DB Engine – so in the flyout – select (Host=SQL DB Engine), and this will expose class properties from the host class. From here we can choose the “Performance Counter Object Name”
That will drop the entire variable into the object. We only need to add the actual perfmon object at the end (:Databases)
For the counter – that’s simple – just type in the counter exactly as it is by name in perfmon: Data File(s) Size (KB)
Now – for the instance – this is another tricky part. We don’t want to collect “all instances” when targeting all databases – that could potentially collect a TON of duplicate data depending on the datasource configuration. It is best to use a variable again here – to match the database perfmon instance to the database name. This will allow each instance of the rule – targeting each database, to collect performance data only about itself. Here is an example:
Under “Instance” – again using the flyout on the right – choose a property of the targeted class which matches up in Perfmon. In this class “Database Name” is perfect!
For the Interval – we don’t expect this to change often, so once an hour is fine. (You could even do once or twice a day, but then our hourly reports would not be populated).
Here is our final configuration:
Click Next. For the Optimization tab – this counter might be a good candidate for optimization – not to even collect the data unless there is significant change, but since I chose once per hour – I will not use optimization and get an actual perf record per hour, for each database. If you wanted to collect this counter more frequently – you might consider optimization.
Done! Now import this into your management group.
To test if our new rule is working – go to My Workspace, create a new performance view, and scope it to “Collected by specific rules”. Choose your rule from the list….
Once your SQL 2008 Servers have downloaded your new MP, applied the new config, and sent up their first performance data sample (takes up to the frequency of the collection rule), you will see this view populated:
You can also right click any database object in a state view – and choose “Open > Performance View” and see all the counters available for a given database.
Now – you can also run a “Performance Top Objects” report – and create a new one for “My Largest Databases” (required daily aggregation – so wait 24 hours for data to show up)
Now – you can repeat this process for the SQL Server 2005 DB objects, to ensure you are collecting DB size for SQL 2005 hosted databases as well.
I am attaching my sample MP below:
Some of the previous articles:
How do I collect data from a multi-instance object – like a SQL DB instance-
Writing monitors to target Logical or Physical Disksand most recently:
Collecting SQL Database size as a performance counterOn the last article – I discussed how to collect database size – for all databases, and targeting the SQL (200x) DB Engine as the target class for the collection. This probably wasn’t the best idea. This is because, for a database specific counter, we probably want to collect that performance data at the database class level – not the instance. The reason for this, is to facilitate SQL performance views when scoping to the database objects, and for reporting down the road, when we add specific databases to a report.
So – the rest of this post will be an example on how to collect the database size.
I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005 database objects, and one to collect for SQL 2008 database objects. The reason I am doing this – is because if I targeted generically “SQL Database” – the next version of SQL would be included in this parent class, but might use a different object/counter down the road. So I will stick to known versions and perf counters. So I will create my rule targeting “SQL Server 2005 DB ” and “SQL 2008 DB”
The first step in creating a Management Pack – is to open your existing custom SQL workflow MP into the Authoring Console, or create a new empty Management Pack.
I will create a new empty MP and give it the ID of “Microsoft.SQLServer.2008.Monitoring.Addendum” and Display Name of “SQL Server 2008 (Monitoring) Addendum”. Once you save it – go to File – Management Pack Properties. We need to version our MP (increment by 1) since we will be changing it, or assign a new version number.
We also need to add a reference here if we don’t already have it – to ensure this MP has a reference for the SQL 2008 Discovery MP. This will allow us to choose SQL Classes later on when targeting our collection rules. Click the references tab, and add the SQL 2008 discovery MP if it isn't already present:
Then go to Health Model, Rules, New, Collection, Performance Based, Windows Performance Collection.
The first step is give my rule an ID. This will be the ID of the management pack, plus some additional text. It defaults to “NewElement” and we need to change that:
I will call mine “Microsoft.SQLServer.2008.Monitoring.Addendum.CollectSQL2008DBSize”
Give the rule a display name that is in accordance with your custom rule naming standard
Under “Target” – Browse all classes and find the “Microsoft.SQLServer.2008.Database” class.
Under Category – change to Performance Collection.
When completed – here is how mine appears:
Click Next.
On this screen – we have the option to type in the performance counter, object, and instance we want to collect.
Great care should be taken here. This is because the SQL DB Engine is a multi-instance object, and each instance appears differently in Perfmon. If we don’t choose the correct object here – then we wont collect the data from all of our instances. Let me explain.
In a “default instance” of SQL – the perf counter looks like this:
In a Named instance – it appears like the following:
If we typed in “SQLServer:Databases” we would only collect from the default instances of SQL in the environment. If we typed in “MSSQL$I01:Databases” we would only collect the data from identically named instances in the environment. However – we want to collect this from ALL instances. In that case – we need to use a VARIABLE in the performance counter object – since the actual object names vary in Perfmon. We can cheat by looking at a some other perf collection rules in the SQL MP and see how they handled this…. or we can look in discovered inventory and see if there is a good class property of our chosen class to handle this.
It just so happens that the SQL DB Engine class – has a property called “Performance Counter Object Name” that was created specifically for this purpose! If you look at this value in discovered inventory, you can see these correspond perfectly with what we need:
Sweet! And if you spot check a few Perf Collection rules in the SQL MP using our same target class, you’d find they also use this.
So – back to the authoring console – we need to use this object, as a variable, for our Perfmon Object. Here is how: There is a fly-out on the right – this will show all the class properties based on our target. In this specific case – our class target is “database”. The Database is hosted by a SQL DB Engine – so in the flyout – select (Host=SQL DB Engine), and this will expose class properties from the host class. From here we can choose the “Performance Counter Object Name”
That will drop the entire variable into the object. We only need to add the actual perfmon object at the end (:Databases)
For the counter – that’s simple – just type in the counter exactly as it is by name in perfmon: Data File(s) Size (KB)
Now – for the instance – this is another tricky part. We don’t want to collect “all instances” when targeting all databases – that could potentially collect a TON of duplicate data depending on the datasource configuration. It is best to use a variable again here – to match the database perfmon instance to the database name. This will allow each instance of the rule – targeting each database, to collect performance data only about itself. Here is an example:
Under “Instance” – again using the flyout on the right – choose a property of the targeted class which matches up in Perfmon. In this class “Database Name” is perfect!
For the Interval – we don’t expect this to change often, so once an hour is fine. (You could even do once or twice a day, but then our hourly reports would not be populated).
Here is our final configuration:
Click Next. For the Optimization tab – this counter might be a good candidate for optimization – not to even collect the data unless there is significant change, but since I chose once per hour – I will not use optimization and get an actual perf record per hour, for each database. If you wanted to collect this counter more frequently – you might consider optimization.
Done! Now import this into your management group.
To test if our new rule is working – go to My Workspace, create a new performance view, and scope it to “Collected by specific rules”. Choose your rule from the list….
Once your SQL 2008 Servers have downloaded your new MP, applied the new config, and sent up their first performance data sample (takes up to the frequency of the collection rule), you will see this view populated:
You can also right click any database object in a state view – and choose “Open > Performance View” and see all the counters available for a given database.
Now – you can also run a “Performance Top Objects” report – and create a new one for “My Largest Databases” (required daily aggregation – so wait 24 hours for data to show up)
Now – you can repeat this process for the SQL Server 2005 DB objects, to ensure you are collecting DB size for SQL 2005 hosted databases as well.
I am attaching my sample MP below:
Kevin Holman
