Agent Management

SQL Server Agent Management

The SQLServerAgentClient provides robust automation control over SQL Server Agent. It is effectively a programmatic mirror of the SQL Agent tree in SSMS.

Note: SQL Server Agent relies on extended stored procedures (Agent XPs). Ensure the Agent service is running on the target instance.

---

Basic Job Execution

let agent = SQLServerAgentClient(client: client)

// Check if the agent is healthy and running
let status = try await agent.preflightAgentEnvironment().get()
print("Agent Ready: \(status.isSqlAgentRunning)")

// List all jobs
let jobs = try await agent.listJobs()

// Start a job asynchronously
if let job = jobs.first {
    try await agent.startJob(named: job.name)
}

// Fetch execution history for a job
let history = try await agent.listJobHistory(jobName: "NightlyBackup", top: 10)

---

Creating Jobs from Scratch

You can build jobs, define multiple steps across different subsystems, and schedule them programmatically.

let jobName = "nio_sample_job"

// 1. Create the Job Container
try await agent.createJob(named: jobName, description: "Nightly cleanup", enabled: true)

// 2. Add a T-SQL Step
try await agent.addTSQLStep(
    jobName: jobName, 
    stepName: "DeleteOldLogs", 
    command: "DELETE FROM Logs WHERE created_at < GETDATE() - 30;", 
    database: "master"
)

// 3. Attach the job to the current server (Required before starting)
try await agent.addJobServer(jobName: jobName)

// 4. Start the job
try await agent.startJob(named: jobName)

---

Scheduling

Automate execution by linking Schedules to Jobs.

// Create a recurring daily schedule (frequency logic aligns with standard MSDB parameters)
try await agent.createSchedule(
    named: "nio_daily", 
    enabled: true, 
    freqType: 4,        // 4 = Daily
    freqInterval: 1,    // Every 1 day
    activeStartTime: 0  // 00:00:00 (Midnight)
)

// Attach it to your job
try await agent.attachSchedule(scheduleName: "nio_daily", toJob: "nio_sample_job")

// Check when it runs next
let nextRuns = try await agent.listJobNextRunTimes(jobName: "nio_sample_job")

---

Alerts and Operators

Configure the Agent to send alerts upon failure or specific conditions.

// 1. Create a person to notify
try await agent.createOperator(name: "DBA_Team", emailAddress: "dba@example.com")

// 2. Attach an email notification to the job on failure (Level 2)
try await agent.setJobEmailNotification(
    jobName: "nio_sample_job", 
    operatorName: "DBA_Team", 
    notifyLevel: 2 
)

// 3. Create a generic Alert based on severity
try await agent.createAlert(name: "Severity16_Errors", severity: 16)
try await agent.addNotification(alertName: "Severity16_Errors", operatorName: "DBA_Team", method: 1) // 1 = Email

---

Advanced Subsystems & Proxies

Jobs aren't limited to T-SQL. You can run Command Line scripts or PowerShell by defining proxies and credentials.

// Define the secure credential
try await agent.createCredential(name: "cmd_cred", identity: "local_svc", secret: "<your_password>")

// Create the proxy linking the credential to the subsystem
try await agent.createProxy(name: "cmd_proxy", credentialName: "cmd_cred", description: "For Batch files")
try await agent.grantProxyToSubsystem(proxyName: "cmd_proxy", subsystem: "CmdExec")

// Create a job step utilizing the proxy
try await agent.addStep(
    jobName: "nio_sample_job", 
    stepName: "RunBatch", 
    subsystem: "CmdExec", 
    command: "C:\\scripts\\cleanup.bat",
    proxyName: "cmd_proxy" // Executes under local_svc context
)