SQL AG Auto-Failback Script

A PowerShell script for automatically failing SQL Server Availability Groups back to a preferred primary node after a reboot or failover event — designed to run via Task Scheduler at startup, with HTML email reporting via MailKit/MimeKit.

What it does

1. Waits a configurable delay after startup (so SQL/cluster services have time to come online).
2. For each SQL instance across a set of named instances, checks every Availability Group's current primary replica.
3. If the primary has moved to the non-preferred node, triggers a manual failover back to the preferred node.
4. Verifies the failover succeeded and that all databases in the AG report SYNCHRONIZED / healthy afterward — not just that the primary role moved.
5. Builds an HTML summary email (color-coded per-AG results, with a highlighted "manual verification required" section for anything that failed verification) and sends it via MailKit/MimeKit.

Requirements

• PowerShell 5.1+
• sqlcmd available on the machine running the script
• Windows-authenticated access to the SQL instances (sqlcmd -E)
• MailKit + MimeKit DLLs (and their dependencies) — the script expects these at a local path; a companion Get-MailKitDlls.ps1 script (not included here) is referenced for fetching them from NuGet
• An SMTP relay that supports STARTTLS + authentication
• A Config.json file containing DPAPI-encrypted SMTP credentials (see below) — DPAPI encryption is user- and machine-scoped, so the encrypted strings must be generated on the target server under the same account the script will run as

Setup — things you MUST change before using this

What	Where	Change to
Preferred/secondary node names	-Node1 / -Node2 parameters	Your actual SQL Server hostnames
Named instances to check	-Instances parameter	Your instance names (e.g. S0, S1) or remove if using default instances
Alert recipient	-MailTo parameter	Your team's email address
Documentation link	-DocLink parameter	A link to your own internal AG runbook/doc, or remove the line in the email body template if you don't have one
SMTP sender address	$MailFrom in Send-FailoverEmail	Your relay's authenticated sender address
SMTP relay host/port	$SmtpServer / $SmtpPort	Your organization's SMTP relay
MailKit/MimeKit DLL path	$LibPath	Wherever you've placed the DLLs
Config.json path	$ConfigPath	Wherever you store the encrypted credential file

Config.json format

{
    "Server":            "your-smtp-relay.yourdomain.com",
    "Port":              587,
    "EncryptedUsername": "<output of: 'you@yourdomain.com' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString>",
    "EncryptedPassword": "<output of: 'YourPassword' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString>"
}

Generate the encrypted strings interactively, on the target server, under the account the scheduled task will run as — DPAPI ties the encryption to that user+machine combination, so credentials generated elsewhere won't decrypt at runtime.

Usage

Typically run via Task Scheduler, triggered on system startup:

.\Check-AGPrimaryReplicaAutoFailback.ps1 -Node1 "SQLNODE01" -Node2 "SQLNODE02" -Instances "S0","S1","S2"

Or with defaults edited directly in the script (common for a dedicated scheduled task per environment).

Result states

Result	Meaning
No Action	AG was already primary on the preferred node
Failed Back	Failover triggered and verified healthy afterward
Verification Failed	Failover ran, but post-failover health check found sync issues — flagged for manual review
Failover Failed	The ALTER AVAILABILITY GROUP ... FAILOVER command itself errored
Skipped	Couldn't determine current primary after retries

License

MIT License — feel free to use and modify for your environment.