Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

OpsMgr: How to monitor non-Microsoft SQL databases in SCOM – an example using Postgre SQL

OpsMgr: How to monitor non-Microsoft SQL databases in SCOM – an example using Postgre SQL

  • Comments 2
  • Likes

OpsMgr has the capability to run a synthetic transaction, to query a remote database from a watcher node.  This can be used to simulate an application query to a back end database, and we have built in monitoring to set thresholds for:

  • Connection time
  • Query time
  • Fetch Time

We will also auto-create three performance rules – so that you can collect these as performance data for short term investigation, or long term trending.

 

In the console, on the Authoring pane, right click “Management Pack Templates” and choose the Add Monitoring Wizard.

image

 

Select the OLE DB Data Source, give it a name and create or select an existing management pack for your SQL synthetic transaction.

On the Connection String page – typically you would select “Build” and choose from one of our existing built-in providers:

 

image

 

This is very simple for running queries against Microsoft SQL servers.  However, what if you you need to query Oracle, or some open-source database?

 

There is a pretty good article on setting this up for an Oracle database here:

http://www.maartendamen.com/2010/09/monitor-an-oracle-database-with-a-scom-oledb-watcher/

 

My customer recently asked me about running a synthetic transaction against a Postgre Open Source SQL DB, so that will be the source of this article.  However – you can use this guidance for any database, as long as there is an OLE DB provider for Windows for that database.  The alternative to this – would be to write a custom script, that can query the DB via the scripting language providers, then use the output of the script to drive a SCOM monitor, like via a propertybag, or the event log.

 

Ok – lets get started.

 

The first step is to find a Windows OLE DB provider for your database.  Download it, and install it on your Watcher node (the agent that you want to run the queries)

For Postgre SQL – I used a trial provider from http://www.pgoledb.com however if you look around I am sure there are other open source providers out there.

Once you install the provider – you should test it to ensure your connections are a success.  Create an empty file with Notepad.exe on your Watcher node’s desktop, name it SQL.txt.  Then once it is on your desktop, rename it to sql.udl.  This UDL file can now launch the OLD DB data link tool, which will show you all your providers.  Notice my new provider for PostgreSQL:

image

 

Select your provider, and choose Next.

Input the servername, port, authentication account, and default database you wish to query, and test the connection.  You MUST get this working before even attempting the OpsMgr OLE DB Wizard, because it will simply call on this provider.  Here is my example below:

 

image

 

Once it is a success – you browse the “All” tab – and see all the parameters allowed by your provider in a connect string:

image

 

The next step is to configure the OpsMgr Synthetic transaction. 

 

In the “Build” Connection String setting for your OLE DB Datasource, it will not list our custom provider, unless it is installed on the same machine that you are running the console.  You could install your provider on your console machine, but I don’t recommend it.  The connect strings are very specific and the SCOM wizard does not provide the correct ones in all cases.  Therefore – just pick the “Microsoft OLE DB Provider for SQL Server”, provide a server and database name, and make sure you check the box to use Simple Authentication RunAs Profile. 

 

image

The reason we check the box for simple auth is so it will build the RunAs profile and input the username and password variables into the connect string.

 

Now – on the next screen, highlight everything in the Connection string, copy and past it into notepad

 

Provider=SQLOLEDB;Server=SRV02;Database=postgres;User Id=$RunAs[Name="OleDbCheck_37d53320a37b48dda11eed3a00caa91f.SimpleAuthenticationAccount"]/UserName$;Password=$RunAs[Name="OleDbCheck_37d53320a37b48dda11eed3a00caa91f.SimpleAuthenticationAccount"]/Password$

 

We need to modify this line to use the supported parameters of our SQL provider.  You should be able to get this information from the provider documentation, from the Data Link Properties tool we used above, or from examples on the web.  In my case – I will use the provider documentation

 

Provider=PGNP.1;Initial Catalog=postgres;Extended Properties="PORT=5432";User ID=$RunAs[Name="OleDbCheck_37d53320a37b48dda11eed3a00caa91f.SimpleAuthenticationAccount"]/UserName$;Password=$RunAs[Name="OleDbCheck_37d53320a37b48dda11eed3a00caa91f.SimpleAuthenticationAccount"]/Password$

 

In the example above – my provider uses a name of “PGNP.1”, the initial catalog is the database I want to query, and I specify the port.  I did not specify the server name, because my watcher node is the same computer that hosts the database, otherwise I would have a value for the server host name.

Once you have a well formatted connect string, the next step is to input your test query and give the workflow a timeout of when to quit and kill the query:

 

image

 

Running a “Test” will fail – because the test is not run from the watcher node – it is run from the RMS, which does not have these special providers installed, so skip that.

 

Configure alert thresholds for your expected query results:

image

 

Choose your watcher node and how often you want the query to run.  Don’t run these synthetic transactions too often, if you have a lot of them they can overflow the watcher node agent, or create a performance impacting load on it.

 

image

 

You can now finish and create your transaction.  The watcher node will get instructions to download this management pack, and it will begin running the transaction.  You can inspect the progress in the console under Synthetic Transaction, OLE DB Data Source State:

 

image

 

Soon Health Explorer may show as critical:

 

image

 

This is because we haven't configured the RunAs accounts, for simple authentication to gain access to the database. 

 

In the console, under Administration > Run As Configuration > Accounts.  Create a Run As Account.  Choose Simple authentication and supply a name:

image

 

Provide a credential:

 

image

 

Always choose More Secure:

 

image

 

Under Accounts, open the properties of the account you just created.  Go to the Distribution Tab – and you need to allow your watcher node to use this credential by distributing it to your watcher:

 

image

 

image

 

Now we need to associate this account we created, to the Profile that our Synthetic Transaction uses.  Select Profiles, and find the name of the Simple Authentication Profile that matches the name of our OLE DB Synthetic transaction:

 

image

 

Open the properties of this profile, and add our newly created account to it:

 

image

 

This will update the Secure Reference management pack, and this credential will flow down to our watcher node, and subsequent attempts to monitor our database will pass this credential, instead of trying to use the default agent action account to authenticate (local system).

After a few minutes, you should see Health explorer clear up and show a successful connection:

 

image

 

If you want to validate that you are collecting performance data – right click your OLE DB Synthetic transaction in the monitoring pane > Open > Performance View:

 

 

image

 

image

 

As you can see – as long as there is a provider for Windows for the agent to consume, we can synthetically query remote databases of any type, authenticate to them securely, and bring back good performance data to proactively show query or connect performance issues, and react to outages immediately.

Comments
  • Thanks for the great post

  • Little remark, if you want to monitor several oracle databases, just take the same connection string and just replace the name of the db if you use the same user name and password to log into the database.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs