Sharing thoughts on running an on-premise hosting platform.
Bas RooversFollowAuthor of onpremisys and working in IT for over 10 years. Loves good discussions and new technologies.
Fail-over SQL Server availability groups via Powershell
Using SQL Server’s availability groups is a bliss for companies who want to provide redundancy. But things can get pretty tedious when you are not using an Enterprise license and have a lot of databases.
Let me explain. When not using an Enterprise license of SQL Server you need to create an availability group for every database. They are calling this feature “Basic Availability Group”. Every availability group needs a listener on it’s own ip address which means firewall rules also need to be added in order for clients to access this database over the listener. Although these steps are only needed in the setup phase, there is more to come.
When you need to fail-over in case of maintenance, you need to set all availability groups in synchronous commit. This makes sure no data is lost during the fail-over. When this is done you can safely fail-over each and every one availability group. These steps will take you a lot of time and there is a high chance you forgotting something, believe me I’ve been there.
I’ve written a Powershell script for this exact scenario. It will probably save you a lot of time, even if you only have 3 availability groups.