If leveraging the new great features from SQL Server the last years – certainly the Availability Group have been a tempting way to increase availability for ConfigMgr (despite the fact that everyone prefers the local SQL Server running on the Primary Site server). Now, as ConfigMgr TechPreview 1706 announced Site Server Role High Availability – separating the site server and the SQL database might be a new preferred method going forward in the future.
Microsoft provided a great step-by-step (2016-05-14) approach on all the steps required to migrate an existing database to a SQL Server Availability Group. As part of this a few specific SQL configurations are stated as a way to ensure an operational database;
We need to Set Trustworthy and enable CLR Integration. Run this against Primary Replica (You can confirm from the AlwaysOn dashboard)
Reviewing the official documentation (2017-05-26) there are however a few additional configurations detailed;
- CLR Integration must be enabled
- Max text repl size must be 2147483647
- The database owner must be the SA account
- TRUSTWORTY must be ON
- Service Broker must be enabled
As a great benefit to that article there is a check for all the configuration options that clearly states wether they are correct or not. All of the above, but one, are very much a requirement for basic functionality. The Max text repl size was not stated in the step-by-step guide and is not a requirement for a functional database for normal operations, however during an upgrade it will be immediately detected as a deviation. What does ConfigMgr do with deviations? Well, it tries to fix it of course (don’t mention inbox backlogs or… or…).
An upgrade with settings configured according to the above requirements
An upgrade where on setting deviates from the requirements and the SQL database is part of an availability group;
The operation cannot be performed on database [CM_P01] because it is invovled in a database mirroring…
ALTER DATABASE [CM_P01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Gives the response: Failed to set database [CM_P01] to SINGLE_USER mode
As ConfigMgr tries to fix the deviation it also tries to set the ConfigMgr database in SINGLE_USER mode, unfortunately that is not possible as long as the database is part of the Availability Group and mirrored. Only way is to configure according to the Microsoft recommend settings outside of ConfigMgr and then retry the upgrade.