Live 360 Conference Orlando November 18 - 22 Register now!
Microsoft Store Your new Windows 8 PC is here
Twitter
LinkedIn
RSS
Facebook
Live 360 Conference Orlando November 18 - 22 Register now!

How to monitor MySQL using System Center Essentials 2007

I have been running System Center Essentials (SCE) since July of 2007, after hearing about it at Tech Ed. I have pretty much been running SCE with little customization.

Then last week I was told by our web developers that our MySQL server was down. It turned out that the service had stopped running. Now, if only they had been using MS SQL, I would have been notified by my trusty SCE server (notice my gentle nudging). So I began to wonder how I could get System Center Essentials to monitor MySQL.

I was fortunate to find a forum post by a Microsoft MVP named John Joyner. Mr. Joyner made the following suggestions on how to monitor MySQL.

Go to the Authoring space in SCE. Expand Management Pack Templates.

Authoring Page

  1. Windows Service – Make a custom monitor that checks all the MySQL services are running OK.
  2. TCP Port – Watch for the particular ports used by MySQL to make sure they are live from outside the monitored box.
  3. OLEDB Data Source – Attach to the MySQL databases of interest using OLEDB, you can create a monitor for each one.

Now, steps 1 and 2 I found easy to follow. However, step 3 gave me a few problems. According to the MySQL.com web site, officially, OLE DB is not supported for MySQL. If you search hard enough, you will find an OLE DB driver for MySQL. It is officially not supported and carries a last modified date of Febraury 2001! Furthermore, although there is an official ODBC driver for MySQL, SCE does not allow ODBC drivers. So how can I monitor MySQL databases?

The answer is the Microsoft OLE DB Provider for ODBC. This way we can have OLE DB for MySQL using recent technology (No 2001 drivers, thank you).

To setup the Microsoft OLE DB provider for ODBC for this application, do the following:

  1. Install the MySQL ODBC driver
  2. From the SCE Authoring page, select OLE DB Data Source. Then click on Add Monitoring Wizard on the left side of the page.
  3. For Select the monitoring type, make sure that OLE DB Data Source is selected. Click Next.
  4. Give a name and an optional description to your monitor. Select or create a management pack for this monitor. Click Next.
  5. For the Connection String, make sure that Advanced Configuration is selected.

Advanced Configuration

You now have two choices for the type of connection string that you would like to enter depending on whether you want to use an ODBC Data Source Name (DSN) or not. My recommendation is not to use a DSN. It is just one more thing to troubleshoot. I have provided sample connection strings for both methods.

Connection Strings

Without a DSN (recommended):

Provider=MSDASQL;DRIVER=MySQL ODBC 3.51 Driver; SERVER=your-mysql-server; DATABASE=your-databasename; UID=mysql-user; PWD=password

(Note: If the name of the MySQL ODBC driver changes, the DRIVER parameter will have to be changed to match the new name. Currently, the name of the ODBC driver appears as MySQL ODBC 3.51 Driver.)

With a DSN

Provider=MSDASQL; DSN=name-of-you-dsn; DATABASE=your-databasename; UID=mysql-user; PWD=password

For further documentation on the Microsoft OLE DB provider for ODBC :

http://msdn2.microsoft.com/en-us/library/ms675326(vs.85).aspx


2 Responses to “How to monitor MySQL using System Center Essentials 2007”

  1. [...] just ran across a great post by Tom Swanson on how he monitors MySQL using Essentials 2007. Check it [...]

  2. Gwen Zierdt says:

    Thanks for sharing this great tip!

    Just FYI, Essentials 2007 SP1 was announced on 3/10/08 at Convergence 2008 in Orlando, Florida.

    More details at: http://technet.microsoft.com/en-us/sce/default.aspx

    Gwen
    The Real World is Messy: Making Sense with Essentials 2007
    http://myitforum.com/cs2/blogs/gzierdt/default.aspx