Wednesday, December 29, 2010

Configuring System Center Operations Manager 2007 (SCOM) Monitoring of SQL Server Agent Job Status

As part of the migration from Operations Manager 2005 to System Center Operations Manager 2007, we are configuring centralized monitoring of SQL Server job status.  Previously SQL Server jobs reported their status using notification configured within each job.  Configuring the job notification is a multi-step process:

  1. In the Authoring section of the SCOM console, select Management Pack Objects—Object Discoveries then search for “agent jobs”.  There are separate discovery types for SQL 2000, 2005 and 2008, and each needs to have an override created to allow discovery to occur.  Right click on the object and choose “Overrides—Override the Object Discovery—For all objects of class…”  Check the box for the Enabled parameter and set the override value to True.  Repeat for each version of SQL.  You will need to wait for the discovery to run which can take up to one day by default.  To verify the jobs have been discovered use the Monitoring section and select Microsoft SQL Server—SQL Agent—SQL Agent Job State.
  2. In the Authoring section of the SCOM console select Management Pack Objects—Monitor then search for “Last Run Status”.  Again, there are separate monitors for each version of SQL server.  Right click on the Last Run Status object, choose “Overrides—Override the Monitor—For all objects of class…”.  Check Alert on State and set the Override Value to “The monitor is in a warning state”.  Check Generates Alert and set the Override Value to “True”.
  3. In the Administration section of the SCOM console select Notifications—Subscriptions, right click and choose New subscription.  Provide a subscription name, a criteria that includes the Windows server you are monitoring (for example an alert raised by an instance with a specific name or an instance in a group), a subscriber (generally resolving to an email address) and a channel (generally SMTP for email).
  4. Test the Subscription by running a job that alternately succeeds and fails.  Wait 15 minutes between each run for testing purposes.

Troubleshooting:

  1. Check that the SQL job is succeeding or failing by viewing the job history in the SQL Management console.
  2. Check that the state is changing by checking the SQL Agent Job State in the SCOM Monitoring console.  Right click the job and choose the Health Explorer.  Navigate to Availability—Last Run Status and choose the State Change Events tab.  You should see previous state changes for the job.
  3. Remember to refresh the Management console to get current information.
  4. Remember that the alerts are not sent immediately.  The monitors have an interval parameter that specifies the number of seconds between checks of the job status.
  5. Changing the Alert Severity to critical and the Alert of State to “The monitor is in a critical state” does not appear to generate alerts (as of SCOM 2007 R2).

Friday, December 10, 2010

SharePoint 2010 Content Databases -- Auto Update Statistics Setting

I noticed that our SharePoint 2010 content databases were being set to Auto Update Statistics Off, and Auto Create Statistics Off.  Since we normally run with Auto Update Statistics on, and that was standard in WSS 3.0 and SharePoint 2007, I did some checking.

It's the default for new content databases added and/or attached, and is part of the upgrade process for databases moved over from a WSS 3.0 or SharePoint 2007 farm.  This is only the case with content databases.  The application and config databases default to your model configuration if created by SharePoint, and are not changed if you use DBA created databases.


I could only find one official reference to this in Microsoft's documentation, which is curious to me since their explanation indicates that performance is negatively impacted if your forcibly re-set Auto Create Statistics to on (although no mention of Auto Update is made).  This Microsoft TechNet article contains the information as a passing reference.  I've copied the relevant text below.

Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server. SharePoint Server implements specific statistics, and no additional statistics are needed. Auto-create statistics can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, SharePoint Server provides coded hints for queries as needed to provide the best performance across all scenarios.

Thanks to rjgd80 for pointing out that my original post was not clear regarding the Microsoft article.

Thursday, December 2, 2010

SharePoint 2010 upgrade error -- failoverpartner not supported

Quick Description:  When patching or upgrading SharePoint 2010 on a farm configured with failover mirroring the upgrade fails with error
Failed to initiate the upgrade sequence
An exception of type System.ArgumentException was thrown.  Additional exception information:  Keyword not supported 'failoverpartner'.
To diagnose the problem, review the application event log and the configuration log located at:
PathToLogFile
Problem:  SharePoint patches are failing on configured database mirroring.

Solution:
You do not need to destroy database mirroring on the SQL Server.  Removing the configuration on the SharePoint server is enough, and can be easily re-configured with a PowerShell command once the upgrade is finished.

Content databases and application databases can be removed through the Central Admin console, but configuration databases have to be configured with PowerShell.  To remove the offending failoverpartner setting run the following script.
get-spserviceinstance -all | foreach-object {
if ($_.typeName -eq "Microsoft SharePoint Foundation Database")
{
foreach ($Database in $_.Databases){
if ($Database.FailoverServer) {
write-host "Found Mirrored Database, Removing"
$Database.AddFailoverServiceInstance($Null)
$Database.Update()
write-host "Successfully removed Failover Instance"

}
else {
write-host $Database.Name "none configured"}
}
}
}
To check that mirroring has been removed, run the following command
get-spdatabase | select name, failoverserver
Run the upgrade, once it's completed you can re-configure the failoverpartner with this script.