First published on TECHNET on Mar 08, 2018
UPDATE: November 2018 the 9 th – For SCOM 2016 only this issue has been fixed in UR6
Bruno here. Today I will cover a topic that came to my attention several times, without being previously discussed broadly and deeply.
Have you ever asked yourself what happen when you create a Scheduled Maintenance mode entry in your System Center 2016 - Operations Manager and above with its database hosted on SQL Server Always ON ? Have you ever checked if a given Scheduled Maintenance Mode run after the Availability Group was moved over to a secondary replica? How can I make sure that it works as expected?
When using SQL Server Always On, all the changes occurring on databases which are part of the Availability Group are replicated to secondary replica(s). This is obviously not true for system databases such as (Master, Model, MSDB and TempDB) which can't be part of any Availability Group by design and hence cannot replicate.
To better explain the scenario, let's think about the new SQL Login creation. You know for sure that, as part of the Always On configuration, you need to replicate the Logins which are stored on the Master database, from the primary replica to all secondary replicas which belong to a given Availability Group (see Logins and Jobs for Availability Group Databases ).
Going back to our topic, when you create a Scheduled Maintenance Mode entry in System Center Operations Manager, the operation takes places into 2 different places: The OperationsManager database and the MSDB database. As I said before, the Operations Manager gets replicated over the secondary replicas, whilst the MSDB gets not.
Given that, as you can imagine, the Scheduled Maintenance Mode will not work anymore until you switch the Availability Group back to the original node.
The prove and repro:
For those of you which are not yet convinced, you can simple repro the issue doing the following:
FROM [msdb].dbo.sysjobs WHERE [name] =
The solution to this issue is simple, but because of the problem's nature, cannot be considered a one-time solution. In fact, you need to apply it each and every time you create a new Scheduled Maintenance Mode.
But let's get this done now :
Of course, this is just a quick and dirty workaround. I am pretty sure that working with your DBA you can find a way to make the Job definition aligned between replicas, automatically
Thank you all; I really hope that this post will save your time as well as you from headaches
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.