There were some changes made in the most recent SQL MP I wrote about here:
SQL MP version 6.1.314.36 released–adds support for SQL 2008R2 and many other changes
Some of the noisy rules and monitors were disabled by default – and some workflows were deprecated, and replaced with new workflows. Also – the SQL database class property discovery was changed – and now Config Churn is no longer an issue, at ALL, from the SQL MP.
This is great news!
However, one of the common things collected by this MP is no longer collected out of the box – Database Size.
This is normal. A Management pack will (and should) focus on health. It is not meant to be a collection of every performance collection for every possible counter that every customer might ever want. It is totally expected that you as a customer, would examine what we collect out of the box – and disable anything you don’t care about. It is also expected you would write additional rules and monitors to add monitoring for any Microsoft application, if you have additional items you want to see monitored or reported on.
Database Size is an example of that.
In the previous MP – DB size was captured in two places:
1. The Database class property. Previous versions of this MP had 4 properties of the database class, which would cause config churn. These were Database Size (MB) (String), Database Size (MB) Numeric), Log Size (MB) (String), and Log Size (MB) (Numeric). These are now deprecated – the discovery will hard code these values to “0” (zero) for all databases. If you see that – this is NORMAL and by design. We should not ever design a property of a class that will change often, as this one did. Lesson learned. :-)
2. A performance collection rule. This previous perf collection rule used a shared datasource (script) which was performing the free space monitoring and collection calulations. This whole datasource was deprecated and replaced with a new model, and therefore this collection rule was deprecated as well.
Not to worry. Database size is a simple performance object in perfmon, and very simple to collect. Let’s create one if you think this is something you’d like to report on (largest DB’s report, etc)
The first step in creating a rule like this is to identify the data source. In this case – in perfmon – there is a Performance counter for:
SQLServer:Databases\Data File(s) Size (KB)\*(all instances)
Next – lets identify a proper target. Two obvious choices here would be the SQL Instance object, and the Database Object. Since this is a simple performance collection rule, where I want to collect and insert ALL instances of the counter, I will choose the SQL instance as my target for this collection rule. I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005, and one to collect for SQL 2008. The reason I am doing this – is because if I targeted generically “SQL DB Engine” – 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 2008 DB Engine” and “SQL Server 2005 DB Engine”
I prefer to create my rules using the authoring console – so I will be performing this activity there. You can do the same thing in the UI, using the wizards there as well. First thing I will open the management pack that contains my custom SQL rules for SQL 2005.
Go to File – Management Pack Properties. We need to version our MP (increment by 1) since we will be changing it.
We also need to add a reference here is we don’t already have it – to ensure this MP has a reference for the SQL 2005 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 2005 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.2005.Monitoring.Addendum.CollectSQL2005DBSize”
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.2005.DBEngine.
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 – simply choose the correct one:
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)
For the instance – check the box to include all instances.
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).
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.
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 2005 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:
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 – can repeat this process for the SQL 2008 Database Engines, to ensure you are collecting DB size for SQL 2008 hosted databases as well.
Kevin Holman
Hiç yorum yok:
Yorum Gönder