New-sqmPreferredPrimaryJob
Always OnsqmSQLTool v1.9.130+ · New🔓 SQLAgentOperatorRole / sysadmin on the job instance
New-sqmAgentCommandJob (the generic CmdExec wrapper with typed .clixml parameters) and adds the recurring schedule that function does not offer: daily, repeating every -IntervalMinutes minutes. The policy parameters (-MaxRedoQueueMB, -MinRoleAgeMinutes, -MinTargetUptimeMinutes, -AllowedDay, -AllowedTimeStart/-AllowedTimeEnd, -CheckOnly, -FailOnBlocked) are handed straight to Invoke-sqmPreferredPrimaryCheck - see there for what each gate does. Only the parameters you actually pass are written into the job, so the check function's own defaults keep applying. Runs that find the preferred replica already primary do nothing and finish in a second, which is the normal case for nearly every run.Run this on the instance where the job is created: the wrapper and parameter files are written to the local module folder, exactly like the module's other job-generating functions. Deploy the job on the preferred node - its Agent keeps running while the node is secondary, and the check works from either role. Deploying it on several replicas is harmless (the runs are idempotent) but produces duplicate log entries.
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Instance where the job is created. Run the function on that machine. |
| -SqlCredential | PSCredential | Optional | , | Credential for the connection that creates the job. It is not stored in the job: the step runs as the SQL Agent service account (or a proxy), which needs VIEW SERVER STATE on all replicas and ALTER AVAILABILITY GROUP on the preferred replica. |
| -AvailabilityGroup | string | Required | , | Availability group to watch. |
| -PreferredReplica | string | Required | , | Replica that should hold the PRIMARY role, spelled as in sys.availability_replicas. |
| -JobName | string | Optional | sqmPreferredPrimary_<AvailabilityGroup> | Name of the Agent job. |
| -IntervalMinutes | int (1-1440) | Optional | 30 | Interval of the check in minutes. |
| -StartTime | string HH:mm | Optional | 00:00 | Start of the daily recurrence, i.e. around the clock by default. |
| -MaxRedoQueueMB | int | Optional | check default (50) | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -MinRoleAgeMinutes | int | Optional | check default (15) | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -MinTargetUptimeMinutes | int | Optional | check default (15) | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -AllowedDay | string[] | Optional | all days | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -AllowedTimeStart | string HH:mm | Optional | , | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -AllowedTimeEnd | string HH:mm | Optional | , | Passed through to Invoke-sqmPreferredPrimaryCheck. |
| -CheckOnly | switch | Switch | $false | The job only reports, never fails over. Recommended for the first weeks. |
| -FailOnBlocked | switch | Switch | $false | The job step fails when a switch back would be needed but a gate blocked it. |
| -Force | switch | Switch | $false | Replace an existing job of the same name. |
| -StartJob | switch | Switch | $false | Run the job once immediately after creating it. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of writing an error and returning a result object. |
| -WhatIf / -Confirm | switch | Switch | ConfirmImpact = Medium | Show or confirm the job creation before anything is written. |
Execution Flow
Examples
Watchdog every 30 minutes that only reports what it would do
New-sqmPreferredPrimaryJob -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -CheckOnly
Every 15 minutes, but only switch back on weekdays inside working hours
New-sqmPreferredPrimaryJob -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -IntervalMinutes 15 -AllowedDay Monday,Tuesday,Wednesday,Thursday,Friday -AllowedTimeStart '06:00' -AllowedTimeEnd '20:00' -Force
Create the job and run it once right away
New-sqmPreferredPrimaryJob -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -JobName 'AG_Prod back to SQL01' -StartJob
Make a blocked switch back visible as a failed job step
New-sqmPreferredPrimaryJob -AvailabilityGroup 'AG_Prod' -PreferredReplica 'SQL01' -FailOnBlocked -Force