Always On

New-sqmAlwaysOnRepairJob

Creates a SQL Server Agent job that runs Repair-Job.ps1 via a CmdExec job step on an hourly schedule. Designed to automatically repair AG synchronization issues. Sets up the output directory with appropriate NTFS permissions for SQL Agent service accounts.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject

Execution Flow

START dbatools available? No throw error Yes New-Item -ItemType Directory (OutputPath) + icacls for SQL Agent accounts ShouldProcess? WhatIf Skip -Force & job exists? Yes Remove-DbaAgentJob (-Force) New-DbaAgentJob (-JobName, -Description, -Category) New-DbaAgentJobStep (SubsystemCmdExec: powershell.exe -File Repair-Job.ps1 -OutputPath ...) sp_add_schedule (hourly, freq_interval=1, -ScheduleName) sp_attach_schedule (link schedule to job) Verify: Get-DbaAgentJob → status Job found on instance? No Status=CreateFailed Return PSCustomObject (Status=Created) DONE
The job step runs powershell.exe -File Repair-Job.ps1 via a CmdExec subsystem step. The output directory is created first and its NTFS ACL is updated to grant the SQL Server Agent service accounts write access, ensuring job output files can be written even under least-privilege configurations.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credentials.
-JobNameStringOptionalAgent job name. Default: sqmTool_AlwaysOn_AutoRepair.
-ScheduleNameStringOptionalSchedule name. Default: sqmTool_AlwaysOn_AutoRepair_Hourly.
-RepairScriptPathStringRequiredFull path to Repair-Job.ps1 on the SQL Server host.
-OutputPathStringOptionalDirectory for job output files and logs. Created if absent; ACL set for SQL Agent accounts.
-JobCategoryStringOptionalAgent job category. Default: Database Maintenance.
-ForceSwitchOptionalRemove and recreate the job if it already exists.
-EnableExceptionSwitchOptionalThrow exceptions immediately on error.
-WhatIfSwitchOptionalPreview actions without creating the job or directory.
-ConfirmSwitchOptionalPrompt before creating objects.

Return Value

Returns a PSCustomObject with: SqlInstance, JobName, ScheduleName, RepairScriptPath, OutputPath, Status (Created / Replaced / CreateFailed / WhatIfSkipped), Message.

Examples

Example 1 — Create repair job with defaults

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1"

Example 2 — Custom job name, output path, and force replace

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1" `
    -JobName "AG_AutoRepair_Finance" `
    -OutputPath "D:\AgentOutput\AGRepair" `
    -Force

Example 3 — WhatIf preview

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1" -WhatIf