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!?
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!