Now Microsoft recently released their new management pack for SQL Server, which included new dashboard views for SQL administrators. Now this is nice and it gives a clear overview of the current configuration and what’s happening at the time. Now it supports most the SQL versions, except 2014 (yet…)
|
Version |
32-bit SQL Server on 32-bit OS |
32-bit SQL Server on 64-bit OS |
64-bit SQL Server on 64-bit OS |
|
SQL Server 2005 |
Supported |
Supported* |
Supported (for SQL Server 2005 SP2 or later only) |
|
SQL Server 2008 |
Supported |
Supported* |
Supported |
|
SQL Server 2008 R2 |
Supported |
Supported* |
Supported |
|
SQL Server 2012 |
Supported |
Supported* |
Supported |
Another heads up! is that by default Microsoft recommends that you monitor no more then 50 databases (per agent) to avoid spikes in the CPU usage. And also not all features are discovered and monitored by default
So in order to get monitoring on these features we would neeed to create en override on the different objects. This can be done by going into Authoring –> Management Pack –> Objects –> Object Discovery –> Scope –> (Choose SQL objects) right-click on the feature which is not enabled and choose Override –> choose a class for which you want this overide to work for. Then create a new management pack and choose Enabled == True
Now the installation is quite simple, head to this webpage and download the management packs –> http://www.microsoft.com/en-us/download/details.aspx?id=10631
Download and then go into the Operations Manager Console –> Administration –> Management Packs –> Import
(NOTE: it is also an best-practice to import the Windows Server management packs first in order to properly monitor aspects such as disk, processor, memory, network)
And after we have imported the management packs we get alot of tasks related to SQL in the dashboard
In order to use these we need to install the SQL management studio on the Operations Manager server. After you have imported the managmeent pack you also need to attach accounts to the SQL profiles in order to have the proper access to the SQL servers.
This post from Kevin Holman (which is a couple of years old, still explains in good detail how to configure the profiles and accounts –> http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx)
Now after you have properly configured the managment pack with permissions, its time to do some proper tuning. For instance by default, the mangement pack will not inform about database backups, which I find a bit disturbing since this is something I would really want to monitor.
In order to enable this monitor go into Monitoring pane –> Microsoft SQL Server –> SQL Server Database Engines –> Databases –> Databases view (Then choose a random SQL database) right-click choose open –> Health explorer (Click the filter monitors to remove showing of unhealth objects) Then under Database backup status it does not have any info.
Now right-click on that object and choose override –> choose enabled true and enter a date here where it should check how old the database is.
After you have then stored this in a mangement pack and choosen which object this rule should apply to, Operations Manager should start to report back with the status if you have taken backup or not.
I can see that after enabling the rule, the alarm appears.
Now there are of course loads of different monitors, which will cause overhead to the SQL server and the operations manager. Many monitors are by default added because Microsoft thinks its a good idea, but your database admin or yourself might not agree. Important to remember that Microsoft have to create a baseline which applies to most setups but if you don’t agree you can customize to your hearts desire! 
So I live by a rule with Operations Manager, if you don’t need to monitor it. Disable it!
Last but not least a preview of the new dashboards which comes with the new management pack
