Wednesday, May 21, 2014

Using the SQL 2012 dashboard within Operations Manager 2012 R2

Within System Center Operations Manager (OpsMgr) 2012 R2 you can use the new great SQL 2012 dashboard. The dashboard can be used for SQL 2012 only however. Let's have a look.
 
The System Center Management Pack for SQL Server can be found here: Download Center. The Management Pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover and monitor SQL Server 2005, 2008, 2008 R2, 2012 and their components such as SQL instances, databases, and SQL Server agents.
 
When installing the Management pack from the online catalog, you will not found the SQL 2012 dashboard at all. That's why you need to download it from Download Center. The Management packs you need to import are:
-Microsoft SystemCenter Visualization Component Extensions Library
-Microsoft SQLServer Presentation
 
After this you will find the SQL 2012 dashboard in the Microsoft SQL server, Databases, SQL Server 2012 Databases Summary Dashboard. Remember that only SQL 2012 databases will be showed in the dashboard. One of the new features is the widget that will show the related health state color. With that you have a quick overview of the selected SQL 2012 database.

 
After installation I did not see any health state at all however. That's because the monitors, which are needed for that, are disabled by default. To override these monitors go to Authoring Pane, Management Pack, Monitors. Now paste the monitor “Transaction Log Free Space” into the search bar and select the monitor in the "SQL 2012 DB management pack". You can now enable this monitor when right click and select override “For all object of the class: SQL 2012 DB” and choose enable with the check box.
 
In the override column change false to true and save this setting in a "Overrides SQL server Management pack". Do this for all changes needed in OpsMgr by default! The following four Monitors which must be enabled for the dashboard are:
-Transaction Log Free Space (%)
-DB Total Space
-Disk Read Latency
-Disk Write Latency

After configuration I still did not see any health state at all. Bummer! That's because security is not in place by default too. When SQL 2012 is installed by default - we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access. Therefore a RunAs account is needed for communication now. This RunAs account will be granted Local Admin over the OS and SA (SysAdmin) rights to SQL. After that you will see the widget that will show the related health state color.

Just a beautiful dashboard isn't it!?

Sources:
System Center Dynamics by wwwally
Microsoft TechNet Forums
Kevin Holman's System Center Blog

Update: Albert Neef mentions MSDN Blogs to manage SQL 2008 databases within the dashboard as well. Very nice indeed!
Posted by Henk Hoogendoorn at 9:49 PM
Labels: , , , , , , , ,

7 comments:

  1. AnonymousMay 22, 2014 at 8:38 AM

    And again, a nice blog Henk. Keep up the good work! You said at the beginning of this blog that the dashboard is for SQL 2012 only. I have found a blog about how to customize the dashboard for SQL 2008 few weeks ago. http://blogs.msdn.com/b/wei_out_there_with_system_center/archive/2014/03/17/opsmgr-sample-customizable-sql-server-2008-databases-summary-dashboard.aspx With this customization you could use the dashboard for SQL 2008 also.

    ReplyDelete
    Replies
    1. Henk HoogendoornMay 22, 2014 at 10:51 AM

      Thanks Albert, didn't know that! Will use it in another implementation when applicable.

      Delete
  2. AnonymousAugust 28, 2014 at 8:30 AM

    Hi,

    We have an issue on this dashboard. we already drop the database but still present on the dashboard and still receiving notifications and alerts.

    Please help.

    please help

    ReplyDelete
    Replies
    1. Henk HoogendoornSeptember 15, 2014 at 12:11 PM

      Hi, can you describe the issue on this dashboard? Maybe it's a known error..

      Delete
  3. AnonymousSeptember 11, 2014 at 11:07 AM

    Hi, thanks for the post, I've just downloaded the MP install from the download centre link you provided but when I unpack it the two MP's you mention aren't there. There's 16 of 'em but not those, am I missing something, other than the obvious :-)

    DC

    ReplyDelete
    Replies
    1. Henk HoogendoornSeptember 15, 2014 at 12:12 PM

      Hi, that is mentioned in the blogpost :-)

      When installing the Management pack from the online catalog, you will not found the SQL 2012 dashboard at all. That's why you need to download it from Download Center. The Management packs you need to import are:
      -Microsoft SystemCenter Visualization Component Extensions Library
      -Microsoft SQLServer Presentation

      Delete
    2. AnonymousSeptember 17, 2014 at 7:44 AM

      Thanks Henk! Teach me to speed read :-)

      All good now and the dashboards are working for 2008! Although I can't get the Free Space tab to populate on the Database Summary Dash....still investigating (works for 2012!).

      Delete

Subscribe to: Post Comments (Atom)