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

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.


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:



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:


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:



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


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.


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:



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:


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.



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:



Soon Health Explorer may show as critical:



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:


Provide a credential:



Always choose More Secure:



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:





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:



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



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:



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:






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.

Kevin Holman