Install-sqmStandardJob
Always OnsqmSQLTool v1.9.168+ · New🔓 SQLAgentOperatorRole / sysadmin on the job instance
New-sqmAgentCommandJob (generic CmdExec wrapper with typed .clixml parameters). -List shows the catalog.| Name | Runs | Defaults |
|---|---|---|
| AgLoginSync | Sync-sqmLoginsToAlwaysOn | -Force:$false, daily 02:00, job sqmAutoLoginSync |
| AgLoginCompare | Compare-sqmAlwaysOnLogins | -FailOnDrift -OnlyDifferences, daily 06:00, job sqmAutoLoginCompare |
Install the AG jobs on every replica. They are PrimaryOnly: the job step first checks the local AG role and, on a secondary, ends with success without calling the function. After a failover the new primary simply takes over and no job turns red.
Why the login sync runs without -Force.
-Force replaces existing logins by DROP and CREATE. If the CREATE fails afterwards (policy, transient error, AD latency) the login is gone on the secondary, which happened in unattended runs. The standard job therefore only adds missing logins and warns if you force Force = $true through -Parameters. Password or default database drift is reported by AgLoginCompare and fixed deliberately by hand.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -Name | string | Required | , | Catalog entry: AgLoginSync or AgLoginCompare. |
| -List | switch | Switch | $false | Show the catalog with all defaults instead of installing anything. |
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Instance where the job is created. Run the function on that machine, the wrapper files are written locally. |
| -SqlCredential | PSCredential | Optional | , | Only for the connection that creates the job. The step runs as the SQL Agent service account, which needs sysadmin on all replicas. |
| -AvailabilityGroupName | string | Optional | first AG | AG for the AG jobs. Also narrows the PrimaryOnly check to that AG. |
| -JobName | string | Optional | from catalog | Override the job name. |
| -Parameters | hashtable | Optional | , | Merged over the catalog parameters, your values win. Example: @{ AuditAdOrphans = $true }. |
| -ScheduleType | string | Optional | from catalog | None, Daily, Weekly or Monthly. |
| -ScheduleTime | string HH:mm | Optional | from catalog | Start time of the schedule. |
| -ScheduleDays | string[] | Optional | , | Weekdays for -ScheduleType Weekly. |
| -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 returning a result object with Status Failed. |
| -WhatIf / -Confirm | switch | Switch | ConfirmImpact = Medium | Show or confirm the installation before anything is written. |
Execution Flow
Return Value
PSCustomObject with Name, SqlInstance, JobName, FunctionName, Parameters, ScheduleName, PrimaryOnly, Started, Status (Success or Failed), Message, Timestamp. With -List: one object per catalog entry.
Examples
Show the catalog with all defaults
Install-sqmStandardJob -List
Login sync on every replica of the AG (run on each node)
Install-sqmStandardJob -Name AgLoginSync -AvailabilityGroupName 'AG_Prod'
Also report Windows logins without an AD account (detection only)
Install-sqmStandardJob -Name AgLoginSync -AvailabilityGroupName 'AG_Prod' -Parameters @{ AuditAdOrphans = $true } -Force
Drift alarm: the job fails when logins differ between replicas
Install-sqmStandardJob -Name AgLoginCompare -AvailabilityGroupName 'AG_Prod' -ScheduleTime '07:30'