The related MP guide doesn’t say anything about it and on the internet there isn’t much to be found either. Even worse, on the internet there are some blogs which make statements which aren’t true at all, like this one (freely translated): ‘…when the SharePoint MP ‘sees’ the SQL MP is in place, it won’t monitor the related SharePoint databases because these monitors are exactly the same…’.
And this isn’t true at all! It’s crap! Excuse my French but I really don’t like postings like these. Simply because they’re assumptions at their best, sold as statements. Therefore I have decided to write this posting in order to tell the whole story about monitoring the SharePoint databases with the SharePoint MP. There is much to tell so let’s start.
The Basics – The Classes
First and foremost, without a Discovery there won’t be any monitoring. The Classes related to the SharePoint databases (SharePoint Database, SharePoint Configuration Database and SharePoint Content Database) are defined in the Microsoft SharePoint Foundation Library MP.
The base class for the SharePoint Database is the System Database which is defined in the System Library MP, which is a core MP of SCOM. The SharePoint Configuration Database and SharePoint Content Database classes use the SharePoint Database as the base class.
As you can see, there isn’t any dependency with the SQL MP at all!
The Basics – The Discoveries
By default, the discoveries for the SharePoint databases (SharePoint Database, SharePoint Configuration Database and SharePoint Content Database) are turned on.
Basically meaning when the SharePoint Server 2013 MP is configured properly and the SharePoint Farm is discovered, the SharePoint databases will be discovered as well.
Okay, so now we have the SharePoint farm in a monitored state with the related SharePoint databases discovered. However, these SharePoint databases do have an unmonitored status.
And yes, those databases are covered by the SQL MP itself but wouldn’t it be nice to have the SharePoint databases covered by the SharePoint MP as well? If only to make the SharePoint admins happy by delivering them a scoped View within SCOM, covering their SharePoint Server 2013 farm(s).
The SharePoint MP runs only ONE Monitor for monitoring the SharePoint databases. Which is understandable. No need to create a second SQL MP within the SharePoint MP. So the SharePoint MP covers only the availability monitoring of the SharePoint databases, done by the SQL Database Connection Failed Monitor, which is turned off by default. Hence explaining why the discovered SharePoint databases do have an unmonitored status.
When taking a look at this Monitor, the Description of it explains why certain blogs on the internet share the earlier mentioned untrue statement (…when the SharePoint MP ‘sees’ the SQL MP is in place, it won’t monitor the related SharePoint databases because these monitors are exactly the same…):
Description: A critical state of this Monitor indicates that a SQL Database connection attempt failed for a specified connection string. Note: This Monitor is disabled by default, enable this Monitor if you want this Management Pack to monitor the SQL Database Connection for SharePoint 2010.
It also shows the SharePoint Server 2013 is almost a one on one copy of the SharePoint 2010 MP…
However, by simply placing an override on the Monitor to start monitoring the SharePoint databases won’t fit the bill here. Yes, monitoring will take place but within 1800 seconds (30 minutes), the interval of this Monitor, the SharePoint databases will get a critical state!
How to make the SharePoint databases healthy in SCOM
Reason why the monitored SharePoint databases enter a critical state is based on the way the related Monitor functions. It uses a connection string which requires additional configuration in order to make it work.
The default connection string simply doesn’t work for 9 times out of 10. (Personally I have never seen it work, but hey there are many SCOM environments I haven’t seen nor touched so who am I to say the connection string doesn’t work at all…)
The default ConnectionString as defined in the MP looks like this: Provider=SQLOLEDB;$Target/Property[Type="WSS!Microsoft.SharePoint.SPDatabase"]/LegacyConnectionString$. (Without the . (dot) at the end!)
In order to make it work it should be adjusted to something like this:
Provider=SQLOLEDB;Data Source=<SQL SERVER NAME>;Initial Catalog=<SHAREPOINT DATABASE NAME>;Integrated Security=SSPI;Enlist=False;Connect Timeout=15
Suppose the SQL server hosting the SharePoint databases is SQL01 and the SharePoint database is sp_content_search01. The new ConnectionString should look like this:
Provider=SQLOLEDB;Data Source=SQL01;Initial Catalog=sp_content_search01;Integrated Security=SSPI;Enlist=False;Connect Timeout=15
Create per SharePoint database an override, targeted at the same SharePoint database and save those overrides in a dedicated unsealed MP like Overrides SharePoint.
Also test this approach on ONE SharePoint database only and lower during the testing phase the interval (IntervalSeconds) of this Monitor to 5 minutes for instance (since I had a dedicated test environment I lowered the interval to 1 minute, allowing me to run the tests extra fast).
This way you’ll know for sure the modified ConnectionString works for your environment and puts those SharePoint databases in a monitored AND healthy state.
When you have found the correct ConnectionString all you need to do is to adjust them per SharePoint database, remove the override on the IntervalSeconds for the first modification so that Monitor runs once per 30 minutes as well.