All in one solution to failover AlwaysOn Availability Groups in SQL Server.
Functions to run comprehensive health checks, failover availability groups, set AG replicas to sync / async modes. Can be combined with the Update-DbaInstance function for a fully automated patching solution.
Requires the SqlServer module.
Optional: Install dbatools module for patching functionality.
This solution is built to be flexible. Functions can be pieced together to build a fully automated solution. Detailed documentation to core functions can be found here.
Import-Module SqlServer -Force Import-Module .\src\SQLRoboFailover -Force Import-Module dbatools -ForceAll-in-one function to failover all primary AGs to a sync-commit replica and set all local replicas to async commit.
- Checks general sql health
- Fails over all primary AG's on the server to an available sync-commit replica
- Run post failover checks
- Set all sync-commit AGs on the server to async-commit
- Run health checks to ensure SQL is ready for restarts
Invoke-MakeSQLServerRestartReady-ServerInstance <ServerInstance>-RunPostFailoverChecks -ScriptOnly:$false-ConfirmUse dbatools module to patch SQL Server
[bool]$IsSQLServerHealthy=Test-IsSQLServerHealthy-ServerInstance <ServerInstance>-Verbose [bool]$IsRestartReady=Test-IsRestartReady-ServerInstance <ServerInstance>-Verbose if ($IsSQLServerHealthy-and$IsRestartReady){$Credential=Get-CredentialUpdate-DbaInstance-ComputerName <ServerInstance>-Version 2017CU20 -Path "\\fileshare.prod\FileShare\Database\UpgradeMedia\SQL2017"-Credential $Credential-Confirm } else{Write-Output"SQL Server - [$ServerInstance] is NOT ready to patch" }[bool]$IsSQLServerHealthy=Test-IsSQLServerHealthy-ServerInstance <ServerInstance>-RunExtendedAGChecks -Verboseif ($IsSQLServerHealthy){Set-AllSecondaryAsyncReplicasToSync-ServerInstance <ServerInstance>-ForceSingleSyncCopy -ScriptOnly:$false-Confirm }Fail over database to an available sync commit replica
Invoke-FailoverAvailabilityGroup-PrimaryServerInstance <PrimaryServer>-AvailabilityGroup "AG10"-RunPostFailoverChecks -Confirm:$true-ScriptOnly:$false