Tuesday, April 23, 2013

SharePoint Server 2013 MP: How About Monitoring The SharePoint Databases?

Out of the box the SharePoint Server 2013 MP won’t monitor the SharePoint databases, listed under the Views Microsoft SharePoint > Configuration Databases and Microsoft SharePoint > Content Databases.
image

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.

Let’s Monitor!
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…):
image

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.
image

Soon the discovered SharePoint databases will get a healthy status:
image

12 comments:

GoVikes01 said...

I followed your steps and created an override for a SharePoint database to test but the monitor is in an unhealthy state because SCOM is connecting to the database using the default Agent Action Account instead of the SharePoint Run As Account. How were you able to tell SCOM to use the SharePoint account instead? Also, did you have a problem importing the SharePoint 2013 reports? I am missing those as well. Thanks!

Marnix Wolf said...

Hi GoVikes01.

The SCOM Action account requires access to the SharePoint databases and requires Farm Admin permissions on the SharePoint Farm as well in order to make it work.

Can you tell me what problems you have with the SharePoint Reports, or better, the import issue.

Cheers,
Marnix

Marnix Wolf said...

Hi GoVikes01.

I see the same behavior with the Reports of this MP. Only two of them are uploaded to the SSRs instance and the remaining lot fails. I have blogged about it (http://thoughtsonopsmgr.blogspot.nl/2013/05/bug-alert-sharepoint-server-2013-mp.html) and contacted Microsoft about this issue.

Cheers,
Marnix

Brent Jackson said...

We are having a similar issue in the database connection fails with the default action account. Its not using the SharePoint Run As account. We have assigned the distribution to the SQL Cluster and we have changed the default action account for the cluster agents but the database query is still using the SCOM default action account.

GoVikes01 did you manage to resolve this issue? If so how?

Blake Wilson said...

Also seeing this issue. I was also under the impression that the workflow will use the account designated in the RunAs profile to handle all of the SP workflows. We've got a Farm Admin account for this, but this "lovely" management pack seems to be using the Default Action Account, NOT the RunAs account, to perform this connection.

Workaround anyone?

Blake Wilson said...

Also seeing this issue. I was also under the impression that the workflow will use the account designated in the RunAs profile to handle all of the SP workflows. We've got a Farm Admin account for this, but this "lovely" management pack seems to be using the Default Action Account, NOT the RunAs account, to perform this connection.

Workaround anyone?

Hare Krishna Agrawal said...

Even in my environment the database is using default action account and not SharePoint Run as Account. Do we have a solution or suggestion ?

Greenhelmet said...

Came across this blogpost yesterday and wanted to offer my to cents.

One possible workaround, is to give the Management Server Action Account public rights on every content or configuration database you want to monitor.
This is how I get past the issue, when the connection monitors are requested. Cheers!

Blake Wilson said...

@Greenhelmet are you saying that once you grant that level of permissions, that you do not have to edit the ConnectionString?

Greenhelmet said...

Truthfully I can't remember in which order I applied the steps when troubleshooting the issue. Currently the connection strings are changed according to http://support.microsoft.com/kb/2690744

For SPS 2013 the original connections strings will Work.

I’ll revisit the SPS2010 system next week, and will test the original connections strings and post back. Cheers.

Greenhelmet said...

Had the opportunity to have a look at the SPS2010 management pack connection strings today.

The connections will not work with the original connection strings.
The monitor returns “Data Source could not be initialized”.

So to summarize:

Changing the connection strings and granting public rights to the management server action account on the configuration and content databases should get you going.
/GH

jemeljanov said...

I Can confirm that SP2013 mp doesn't need to override connection string, only correct rights need to be granted to action account on SP databases