I stumbled across this while rummaging through the vSphere 5.0 Installation and Setup document. Page 183 contains a small section (new in vSphere 5.0) which describes a process to enable database monitoring for Microsoft SQL Server (surrounding pages discuss enabling the same for other supported database platforms). The SQL script provided in the documentation contains an error on the first line but I was able to adjust that and run it on the SQL 2008 R2 server in the lab. Following is the script I ran:
use master
go
grant VIEW SERVER STATE to vcenter
go
Once access has been granted, vCenter will collect certain SQL Server health statistics and store them in the rotating vCenter profile log located by default at C:\ProgramData\VMware\VMware VirtualCenter\Logs\vpxd-profiler-xx.log. These metrics were taken from my vCenter Server log file and serve as an example of what is being collected from the SQL Server by the vCenter Server:
–> <dbMonitoring>
–> DbMonitoring/Counter/Storage: Manually extensible data files/Unit/count/Range Type/range/RangeMin/0/RangeMax/0/Timestamp/2011-09-27T18:00:01.79Z/Value/0
–> DbMonitoring/Counter/Memory:Database pages/Unit/timesIncrease/Range Type/range/RangeMin/0/RangeMax/3/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> DbMonitoring/Counter/Storage: Peak data file storage utilization/Unit/percent/Range Type/range/RangeMin/60559224/RangeMax/90/Timestamp/2011-09-27T18:00:01.802999Z/Value/0
–> DbMonitoring/Counter/Memory:Availaable/Unit/kiloBytes/Range Type/range/RangeMin/5120/RangeMax/60559416/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> DbMonitoring/Counter/Memory:Page Life Expectancy/Unit/seconds/Range Type/range/RangeMin/300/RangeMax/60559416/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> DbMonitoring/Counter/IO:Log growths/Unit/timesIncrease/Range Type/range/RangeMin/0/RangeMax/3/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> DbMonitoring/Counter/CPU:Usage/Unit/percent/Range Type/range/RangeMin/0/RangeMax/80/Timestamp/2011-09-27T18:00:01.75Z/Value/44
–> DbMonitoring/Counter/Memory:Buffer cache hit ratio/Unit/percent/Range Type/range/RangeMin/90/RangeMax/100/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> DbMonitoring/Counter/General:User Connections/Unit/count/Range Type/range/RangeMin/255/RangeMax/60559416/Timestamp/1970-01-01T00:00:00Z/Value/N/A
–> </dbMonitoring>
Per VMware’s documentation:
vCenter Server Database Monitoring captures metrics that enable the administrator to assess the status and health of the database server. Enabling Database Monitoring helps the administrator prevent vCenter downtime because of a lack of resources for the database server. Database Monitoring for vCenter Server enables administrators to monitor the database server CPU, memory, I/O, data storage, and other environment factors for stress conditions. Statistics are stored in the vCenter Server Profile Logs. You can enable Database Monitoring for a user before or after you install vCenter Server. You can also perform this procedure while vCenter Server is running.
One thing that I noticed is that these metrics were being collected in the vCenter log files prior to running the enabling script. I’m not sure if this is because vCenter already had the required permissions to the master database (I use SQL authentication and I didn’t explicitly grant this), or perhaps this is enabled by default in the vCenter installation routine when the database prepare script runs.
The instructions provide plenty of context but are are fairly brief and don’t identify next steps or how to harvest the collected metrics. Perhaps the vCenter Service Health agent monitors the profile log and will alarm through vCenter. If not, then I view this as a monitoring framework VMware provides which can tailored for specific environments. Thresholds could be defined which trigger alerts proactively before dangers or an outage occurs. Admittedly I’m not a DBA. With what’s provided, I’m not sure if this provides much value above and beyond native monitoring and alerting provided by SQL Server and Perfmon.
