Frozen SharePoint PSConfig Upgrade with SQL Server AlwaysOn
If you have a SharePoint farm that uses SQL Server AlwaysOn and your upgrade is just never finishing, this article might be for you.
Update: as someone pointed out, removing the service-application from the farm probably isn’t necessary. Just remove the DB from the AlwaysOn availability group and then the upgrade should just work.
When you run the upgrade wizard you’ll see this at some point:
It’s likely if you’re running AlwaysOn that it’ll never finish however because of an issue with upgrading the usage database while it’s in an AlwaysOn cluster.
Looking at the upgrade status (/_admin/UpgradeStatus.aspx) for the job running you’ll see:
Current object |
SPAdministrationWebApplication |
Current action |
SPWssWebApplicationSequence |
…and looking at the ULS logs, you might find this message:
Unknown SQL Exception 1468 occurred. Additional error information from SQL Server is included below. The operation cannot be performed on database "Usage_Service" 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.
This article is about how to fix this.
How to Fix Half-Upgraded SharePoint Farms on SQL Server AlwaysOn
In short you need to:
Remove the usage service application from the farm.- Remove the old usage database from the AlwaysOn availability group.
- Complete patch upgrade on all servers with psconfig/the wizard.
Re-add service-app with old DB in PowerShell to complete upgrade successfully.- Add again usage DB to AlwaysOn group.
First you’ll have to kill the process running the upgrade. If it’s the wizard, clicking “cancel” doesn’t work – it’ll just sit there eternally waiting for the “upgrade to finish”.
Find the app in task manager and kill it there from the “processes” tab as that doesn’t directly rip it out of memory (if I’m not mistaken), rather it sends the app a “you’re about to be killed; pack-up now” message which the config wizard will respond to, and exit.
Remove Usage Service Application (If Necessary)
This shouldn’t be necessary once the usage database isn’t in an availability group. But just in case, disconnect the usage application from the farm to try again. The short version is to run this PowerShell:
Remove-SPUsageApplication -Identity (Get-SPUsageApplication).Name -RemoveData:$false
Remove Usage DB from AlwaysOn Availability Group & Retry Upgrade
First remove the old usage database from the AlwaysOn group.
If you run the wizard/PSConfig again it should now work. Here’s the previous failed upgrade and the new successful one.
Next, finish the upgrade on each remaining server.
If the Upgrade Wizard Still Doesn’t Finish?
If after this upgrades are still infinitely not finishing, it may be a permissions issue. There are some things the upgrade does that need administrator access on the server itself. Temporarily add the farm account to the local admin group and restart the timer service so the upgrade can finish. It’s got nothing to do with AlwaysOn but if you run a minimum-permission configuration (recommended) you might hit this limitation too.
Re-Adding the Usage Database to the AlwaysOn Group
Once the database has been added to the farm with the above PowerShell we really need it back in the AlwaysOn group too so it’s highly-available again.
For some reason mounting the usage application database again puts the DB back into “simple” recovery model, which doesn’t work for AlwaysOn clustering. Change this back to “full” recovery 1st.
Then take a full backup of the database; that’s another requirement for AlwaysOn databases.
You’ll also need to delete the database from the other SQL AlwaysOn cluster nodes too as the mirroring will be broken.
Once ready, we should be able to re-add the database to the group again.
All being well it’ll work no problem.
That’s it, you’re done. Farm fully upgraded & highly-available again.
Wrap-up
This is an odd problem cause by having SharePoint on SQL Server AlwaysOn specifically. It should get resolved at some point but for now this is your workaround.
Cheers,
Sam Betts