SharePoint 2013: Upgrading usage DB in SharePoint farm with SQL Always-on availability?
With SQL server 2012 Always-On clustering feature, SharePoint 2013 Farms can now be configured with high-availability mode. Initially Microsoft had very limited support for databases that were provisioned in an Availability Group, since SharePoint 2013 SP1 most of the service application databases are now supported in both Async-Commit and Sync-Commit modes. Sync-Commit mode has the most support for SharePoint 2013 databases, although a problem occurs when executing the SharePoint Products Configuration Wizard after deploying CU’s or Security patches where an un-handled exception is thrown near the end of the wizard (Step 8 of 10 or Step 9 of 10). As a best practice, you should always monitor ULS logs and/or Upgrade logs while a patching process is executing. After reviewing ULS logs an error such as this might be found:
“… Timer job is exiting due to exception: System.Data.SqlClient.SqlException (0x80131904): The operation cannot be performed on database “WSS_Logging” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed. at ….”
Or something similar to this depending on the name of your Usage Logging database.
Why does this Happen
During a patching exercise a range of things occur when SharePoint Configuration wizard executes. The issue with databases that live in an Always-On Availability Group is that they can’t be “Altered”. Therefore, to use an ALTER DATABASE statement, the database in question needs to be removed from the Availability Group first. In this particular case the SharePoint Usage Logging database requires this each time the Configuration Wizard runs an upgrade process.
FIX It
So the solution for this particular problem scenario is to remove the Usage Logging database from the Availability Group while you are patching. You can do this safely, assuming that you have all your service applications configured to point to the availability group listener so that you can continue to access the databases while patching. The other important points are that you must remove the database from the primary replica and that you make a backup of the primary replica just in case something goes wrong. Once your patching has been successful across all nodes in the farm, you must then re-add the Usage Logging database back to the Availability Group.
References
· How to configure AlwaysOn availability group here
· Information on high availability option for SharePoint 2013 database support is available here.