Wouldn’t it be nice if you could execute one powershell script that did all the tedious work to perform an AG failover for you – all while you watch the progress and health of your AGs polled real time during the failover? Take it one step further – what if it patched SQL for you and failed everything back to its original place? That’s exactly what SQLRoboFailover will do for you.
My org tasked me with this awesome project and SQLRoboFailover was the result. After the deployment of this solution, we have successfully handed over patching of our SQL QA and Reporting clusters to IT since all they needed to do was execute a powershell script (we’re still holding on to prod for now)
I’m not going to spend a lot of time explaining how to use the solution since I have done that in the README. SQLRoboFailover is just a bunch of core functions that are pieced together like Lego blocks to create a custom failover solution specific to your environment. It has been battle tested at our org and we continue to improve on it to make it as smooth and resilient as possible. These are just some of the features included in the solution:
- Comprehensive AG and database level health checks pre and post failover
- -ScriptOnly modes and -Confirm switches built into most functions
- Live polling of AG health that will print to console right after the failover to make sure that all replicas are healthy and synchronizing
- Setting replicas to sync / async
- Flags to check for in-flight backups / checkdb prior to failover
- All-in-one server health checks which will also optionally check all BI services and SQL Agent services are running
- Invoke-MakeSQLServerRestartReady – all-in-one function that incorporates everything mentioned above to get your server ready for patching / restarts. It will run all health checks, failover all primary AGs on the server to a sync commit replica, run post failover health polls, set all sync commit replicas on the node to async commit, and run a post health check
If you’re curious on how you can leverage this solution, check out the sample scripts of how we implement it in our org, as well as the core functions that you can piece together to customize it to your needs.