If you have installed SQL MP v6.06648.0 (read Kevin Holman’s post on why you need it) issued on 23rd July 2009 you may think that you are now monitoring SQL. Not quite. Here are some things to consider.
SQL Agent Jobs
According to the MP Guide the discovery for SQL Agent Jobs is not turned on but you can obviously override that. I had a customer complain that they found out a job had been failing but there was no alert. Luckily Jimmy Harper posted about this. Apparently the discoveries do not work if certain fields are blank with Description being the main culprit. The customer did not want to go through each job and put in a description but luckily Jimmy’s post contains new MPs to replace the discovery ones in the SQL MP. This worked a treat and the jobs started showing up in the console. The customer created a new SQL Agent Job with nothing in the description and that was picked up. A must have if you want to monitor SQL Agent Jobs. Nice one Jimmy. Hopefully that will be fixed in the next version.
The other thing that I had a long discussion about with the customer was SQL jobs failing. When they fail they are picked up but when the job is set to carry on with the rest of the jobs in sequence after a fail nothing is shown in the console. The customer appreciates why this is. After all if you do not think that one part of a sequence is important enough to stop the other jobs running you can’t call it a failure. They can see it if they look at SQL but in the OpsMgr console there is nothing. We tested it with a few jobs. They would like to see something – even if it is information. As they said if it fails once it is not important but if it keeps on failing it needs investigating. Something else for the next version of the MP.
SQL Replication
Replication is also not discovered by default. When turned on it discovers replication servers but there are no rules or monitors. So if you need to monitor SQL Replication you have to do it yourself.
SQL Mirroring
If you want to monitor SQL Mirroring pick up the Extended SQL Server MP by Matt Goedtel on OpsManJam.
Noisy DBCC Rule
DBCC Check comes up every now and then with an information alert that says no errors. I have always thought that bizarre but never had time to investigate. Jimmy Harper’s post on it explains why that is and how to fix it to give you useful warning and critical alerts when there are real problems with DBCC. Another good post from Jimmy.
SQL DB Properties
Jimmy also has a post on SQL DB properties not being discovered and how to fix it.
SQL Server Full Text Search Service Monitor
As Jimmy Harper says it is mentioned in the guide but his post goes into great detail on it. This is usually the case for servers running MSDE like WSUS. Also mention by Kerrie and Cameron in their OpsMgr R2 by Example which goes into a lot of information about the typical alerts that come up for SQL MP and some resolutions for those. Required reading after the MP Guide.
SQL 2008
If you have SQL 2008 in your environment then you will need to install the SQL DMO bits as they are no longer shipped with SQL but the SQL MP still needs them. This shows up as no space performance counters for SQL 2008 DBs and event 4000 in the OpsMgr log. Marnix Wolf (Thoughts on OpsMgr) has posted a good set of articles on this. First here which includes showing what to download and install and then problem with DMO Installation.
Conclusion. You may think that after you import the SQL MP that you are monitoring SQL. But unless you dig into the documentation and the MP you may find that critical bits are not being monitored.
