Posts Tagged ‘Microsoft’

Enabling vCenter Server 5.0 Database Monitoring

September 27th, 2011

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.

The 9/11 Post

September 10th, 2011

Snagit CaptureMy memory isn’t what it used to be but there are a few experiences in the past which remain clear in my mind.  The first time I met the person who would eventually become my wife. The birth of my daughter. The first time I saw VMware ESXi at a Minneapolis VMUG meeting.  September 10th, 2001 and of course what followed the next morning.  A lot of people have a 9/11 story.  I have mine.  Tomorrow being the 10 year anniversary, I’ll share it here as requested.

In 2001 I worked as a Systems Engineer for a large bank based out of Minneapolis, MN.  One of our datacenters was located in Columbia, MD which is situated between Baltimore, MD and Washington D.C.  We were re-IP’ing the datacenter the weekend before 9/11 so I had spent the prior week on site making the final preparations for the long weekend ahead which I would also be involved in.

As I recall, it was a pretty long weekend working around the clock.  Par for the course when we had to deal with the finicky attitude of Microsoft SQL Server clusters.  We got through it and Monday morning arrived.  I would usually stick around through mid day Monday for this type of activity to make sure we were out of the woods, then fly out in the afternoon or early evening.  There were no issues to speak of but I hung out with my Maryland co-workers until the last possible minute I had to leave for the airport.  This was my normal routine.  At this point in time there was no reason to arrive at the airport two hours early.  There weren’t security checkpoints & the associated lines to deal with.  However, I had cut it way too close this time and was going to miss my flight.  I didn’t know it at the time but arriving late and missing my flight would allow me a brush with fame opportunity.

Snagit CaptureAt this time it’s probably 5:30pm EDT.  I worked with the ticketing agent to find a later flight out.  Fortunately there was a later flight, I believe it was the last out of BWI.  I checked my bag and made my way to the gate for the long wait.  The portion of the airport where my gate existed was fairly empty.  I was doing the long walk thing to one of the last gates.  As I’m walking, a young guy who had gotten off a plane is walking towards me from the opposite direction.  From a distance he’s tall and has a baseball cap on.  As we passed each other, I got a closer look.  One thought immediately entered my mind as he was walking away “He looks like Travis Pastrana (a pro motocross Suzuki rider).”  The DUH moment followed “Travis and his parents live in Maryland.”  When you eat, sleep, and breathe motocross, you follow motocross and pro riders closely. You know these things.  I spun around and called out his first name “Travis!” from 20 feet away.  We talked for about a minute.  He was a nice guy and autographed a full spread poster of him performing a lazy boy contained in a motocross magazine I happened to be carrying in my laptop bag.  Then we went on our respective ways.  I called my wife (girlfriend at the time) and told her she’d never guess who I just met.  I spent the rest of the evening smiling.  I would always remember that particular day (but not necessarily the date September 10th itself), as one of the best days in my life.  The following day would be one of the worst.

Tuesday morning I woke up and drove to work in downtown Minneapolis.  As I waited at the intersection of 11 Street to turn right onto Hennepin Avenue, the frantic reports started coming in over the radio.  For me, that’s where I was when 9/11 happened – at that intersection making a right turn, trying not to believe what I’m hearing on the radio.  The details of that event are known by all and don’t need repeating here.  I had a difficult time grasping what had happened, how they could have happened.  I thought about how close I had been to one of the sites the night before.

The following weekend I loaded up the truck to race at Mazeppa.  There was such a poor turnout due to the week’s events that the races were cancelled and for those that stuck around, we just practiced the entire day.  A small and inconsequential example of how the events 9/11 would impact the future.  By the grace of God, I didn’t lose any of my own loved ones, friends, or co-workers but when I see the faces of the innocent people who lost their lives, I can’t help but feel a connection to each of them.  I’ve watched interviews of so many who suffered the loss of family members and I absolutely cannot comprehend how they dealt with it.  I pray that those who passed on and their family members who remain receive love and comfort from God.  It will be an emotional morning at church tomorrow as we join together in a special service of remembrance.

My daughter started kindergarten last week.  I also have a three year old boy.  In time they will learn about 9/11 and will inevitably talk about it and/or ask my wife and I questions about it.  I hope that it is the type of event they will only have to learn about through history books.  Tomorrow I’ll pray for peace and hope that my children and my children’s children can grow up in a better world.

On a creepy side note, for the first part of my tenure at the bank, each time I traveled to the Columbia site, a national level tragedy occurred:

  • Columbine High School tragedy – 4/20/99
  • 9/11 tragedy – 9/11/01
  • Space Shuttle Columbia tragedy – 2/1/03

I no longer work for the bank and as such, no longer travel to this site.

vCenter Server 5.0 and MS SQL Database Permissions

August 20th, 2011

It’s that time again (to bring up the age old topic of Microsoft SQL database permission requirements in order to install VMware vCenter Server).  This brief article focuses on vCenter 5.0.  Permissions on the SQL side haven’t changed at all based on what was required in vSphere 4.  However, the error displayed for lacking required permissions to the MSDB System database has.  In fact, in my opinion it’s a tad misleading.

To review, the vCenter database account being used to make the ODBC connection requires the db_owner role on the MSDB System database during the installation of vCenter Server.  This facilitates the installation of SQL Agent jobs for vCenter statistic rollups.

In the example below, I’m using SQL authentication with an account named vcenter.  I purposely left out its required role on MSDB and you can see below the resulting error:

The DB user entered does not have the required permissions needed to install and configure vCenter Server with the selected DB.  Please correct the following error(s):  The database user ‘vcenter’ does not have the following privileges on the ‘vc50’ database:

EXECUTE sp_add_category

EXECUTE sp_add_job

EXECUTE sp_add_jobschedule

EXECUTE sp_add_jobserver

EXECUTE sp_add_jobstep

EXECUTE sp_delete_job

EXECUTE sp_update_job

SELECT syscategories

SELECT sysjobs

SELECT sysjobsteps

Snagit Capture

Now what I think is misleading about the error thrown is that it’s pointing the finger at missing permissions on the vc50 database.  This is incorrect.  My vcenter SQL account has db_owner permissions on the vc50 vCenter database.  The problem is actually lacking the temporary db_owner permissions on the MSDB System database at vCenter installation time as described earlier.

The steps to rectify this situation are the same as before.  Grant the vcenter account the db_owner role for the MSDB System database, install vCenter, then revoke that role when vCenter installation is complete. While we’re on the subject, the installation of vCenter Update Manager 5.0 with a Microsoft SQL back end database also requires the ODBC connection account to temporarily have db_owner permissions on the MSDB System database.  I do believe this is a new requirement in vSphere 5.0.  If you’re going to install VUM, you might as well do that first before going through the process of revoking the db_owner role.

An example of where that role is added in SQL Server 2008 R2 Management Studio is shown below:

Snagit Capture

USB Thumb Drive Not Recognized – 3 Fast Beeps

July 27th, 2011

No Earth-shattering material tonight.  In fact this tip isn’t even VMware/virtualization related other than the fact that the problem came up while working in the lab.  It has been several months since the last article I wrote under the “General” category which contains no VMware/virtualization content.

Anyway, I was working in the lab when…

My Windows 7 OS would no longer recognize my USB thumb drive.  Inserting the thumb drive into any of the USB  ports produced three quick USB-style beeps.  Having cut my x86 teeth in the days when A+ certification amounted to quite a bit, the three beeps told me something wasn’t right from a hardware standpoint but with a hint of driver hence the USB audio indicator.  I was mildly concerned because I sometimes carry data around on this drive which hasn’t been backed up or cannot be quickly reproduced.  A warm reboot of the OS produced no joy.  Neither did a power off.

Back in Windows Device Manager, the device was shown as disabled with an option to re-enable.  This did not work however.

Snagit Capture

This being a USB device which can easily be reinstalled, the next step was to uninstall the driver by right clicking on the device and choosing Uninstall (notice the “down arrow” depicted on the device indicating it is disabled):

Snagit Capture

After the uninstall of the driver, I unplugged the USB thumb drive, waited a few seconds, plugged it back in, and immediately heard the friendly USB sound I had been wanting all along.  Windows 7 went through a device discovery process, installed drivers, and I was on my way.

New Diskeeper White Paper: Optimization of VMware Systems

June 28th, 2011

diskeeperDiskeeper Corporation reached out to me via email last week letting me know that they’ve released a new white paper on optimizing VMs.  I’m making the three page document available for download via the following link:

Best Practice Protocols: Optimization of VMware Systems (416KB)

He is serious, and don’t call him Scott

May 20th, 2011

5-20-2011 10-47-54 AMHappy Friday!  Today’s treat is the announcement of a new tech blog by my friend in VMware virtualization, Microsoft SQL,  and the occassional fine cigar, Todd Scalzott (@tscalzott).  I love the title of his blog: Don’t Call Me Scott.  Content focus will be Tech ramblings from a guy named Todd, too often called Scott.  I’m looking forward to what you have to share Todd!

Performance Overview charts fail with STATs Report Service internal error

May 11th, 2011

A few months ago I was troubleshooting a problem with the Overview charts in the Performance tab of the vSphere Client.  This was a vSphere 4.0 Update 1 environment but I believe the root cause will impact other vSphere versions as well.

Instead of displaying the dashboard of charts in the Overview display, an error was displayed:

STATs Report service internal error
or
STATs Report application initialization is not completed successfully

One unique aspect of this environment was that the vCenter database was hosted on a Microsoft SQL Server which used a port other than the default of TCP 1433.  VMware KB Article 1012812 identified this as the root cause of the issue.

To resolve the issue, I was required to stop the vCenter Server service and modify the statsreport.xml file located on the vCenter Server in the \Program Files\VMware\Infrastructure\tomcat\conf\Catalina\localhost\ directory by inserting the line in bold.  Note the italicized components will vary and are environment specific based on the SQL server name, database name, alternate TCP port in use, and authentication method (SQL/false or Windows integrated/true):

<Resource auth=”Container”
   name=”jdbc/StatsDS”
   type=”javax.sql.DataSource”
   factory=”org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory”
   initialSize=”3″
  maxActive=”10″
  maxIdle=”3″
  maxWait=”10000″
  defaultReadOnly=”true”
  defaultTransactionIsolation=”READ_COMMITTED”
  removeAbandoned=”true”
  removeAbandonedTimeout=”60″
  url=”jdbc:sqlserver://sqlservername:1601;instanceName=sqlservername;
     databaseName=sqldatabasename;integratedSecurity=false;”
/>

Don’t forget to restart the vCenter Server service after saving the statsreport.xml file.