Invoke-sqmPreferredPrimaryCheck
Always OnsqmSQLTool v1.9.130+ · Invoke🔓 VIEW SERVER STATE + ALTER AVAILABILITY GROUP
-PreferredReplica and, if they differ, performs a controlled failover back, but only when every safety check passes. If any check fails, nothing happens and the result object says exactly why.The gates: the preferred replica is a member of the AG and currently SECONDARY; it runs in
SYNCHRONOUS_COMMIT (a manual failover to an async replica would lose data, so such a replica is never failed over to automatically); all AG databases are joined on it, SYNCHRONIZED, ONLINE and not suspended; its redo queue is at or below -MaxRedoQueueMB; SQL Server on it has been up for at least -MinTargetUptimeMinutes (a node fresh from a reboot may still be mid-patch); the current primary has held its role for at least -MinRoleAgeMinutes (anti-flapping, keeps the watchdog out of an ongoing failover sequence); and the current time is inside -AllowedDay and -AllowedTimeStart..-AllowedTimeEnd if given.The failover itself is delegated to
Invoke-sqmFailover, so pre- and post-checks and the plain ALTER AVAILABILITY GROUP ... FAILOVER (manual, never forced) behave exactly like a hand-triggered failover. Nothing changes when the preferred replica is already primary, which is the normal case for nearly every run. Deploy it as a recurring job with New-sqmPreferredPrimaryJob.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Any replica of the AG, used to read the current state. The function resolves the current primary itself, so this instance may be primary or secondary. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. Without it: Windows authentication. |
| -AvailabilityGroup | string | Required | , | Name of the availability group to watch. |
| -PreferredReplica | string | Required | , | Instance that should hold the PRIMARY role, as it appears in sys.availability_replicas.replica_server_name. A host name with a DNS suffix is matched against the catalog name as well. |
| -MaxRedoQueueMB | int | Optional | 50 | Maximum redo queue on the preferred replica in MB. Above that, no failover is performed. |
| -MinRoleAgeMinutes | int | Optional | 15 | Minimum time the current primary must already have held its role. 0 disables the check. Read from current_configuration_commit_start_time_utc, an approximation that can only delay a switch back, never trigger a wrong one. |
| -MinTargetUptimeMinutes | int | Optional | 15 | Minimum uptime of the SQL Server service on the preferred replica. 0 disables the check. |
| -AllowedDay | string[] | Optional | all days | Weekdays on which a switch back is allowed, e.g. Monday..Friday. |
| -AllowedTimeStart | string HH:mm | Optional | , | Start of the daily window in which a switch back is allowed. Only together with -AllowedTimeEnd. The window may cross midnight. |
| -AllowedTimeEnd | string HH:mm | Optional | , | End of the daily window. Only together with -AllowedTimeStart. |
| -WaitAfterFailoverSeconds | int (5-300) | Optional | 30 | Wait before the post-check. Passed to Invoke-sqmFailover. |
| -CheckOnly | switch | Switch | $false | Only check and report, never fail over. Use it to watch the watchdog for a few weeks before letting it act. |
| -FailOnBlocked | switch | Switch | $false | Return an error (failed Agent job step) when a failover would be needed but a gate blocked it. By default that is a WARNING in the log and the result object only, so a patch weekend does not turn every run red. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of writing an error and returning a result object. |
| -WhatIf / -Confirm | switch | Switch | ConfirmImpact = High | -WhatIf shows whether a switch back would happen right now, without touching anything. |
Execution Flow
Examples
Would a switch back happen right now?
Invoke-sqmPreferredPrimaryCheck -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -WhatIf
Full check including every safety gate, reported only
Invoke-sqmPreferredPrimaryCheck -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -CheckOnly
Switch back only during the week and inside working hours
Invoke-sqmPreferredPrimaryCheck -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -AllowedDay Monday,Tuesday,Wednesday,Thursday,Friday -AllowedTimeStart '06:00' -AllowedTimeEnd '20:00'
Tighter redo-queue limit, longer anti-flapping window
Invoke-sqmPreferredPrimaryCheck -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -MaxRedoQueueMB 10 -MinRoleAgeMinutes 60