SQL Server Availability Group Failover Process

 

Sometimes you may need to restart one of the Always On servers. In such a case, you will need to failover the Availability Group on the server to the other server. In order to perform a failover process, it is necessary to first see the failover mode and the synchronization status of the primary and secondary databases on both servers.

If the Availability Groups are in Automatic failover mode and the databases are synchronized on both nodes, we can perform failover. To see the synchronization status for all databases on the server with a single script, you can read my article “SQL Server Availability Group Databases Synchronization Status“.

We can do the failover process in two ways.

 

Method1: We can perform over SSMS. Right click on AG and click on Failover.

 

On the next screen, click Do not show this message and click next.

On the next screen gives information about the instance. Click Next to proceed.

 

On the next screen, we select the server that will be the primary.

 

Click Next and Finish to complete the failover process.

Method2: We can perform failover by using Failover Cluster Manager. I am using this method because it is more practical and faster. But do not forget to run the script that shows the synchronization status at the beginning of the article when using this method. Right click on the relevant AG from Roles and click Move and Select Node.

 

The servers in the same windows cluster come up as below. Select the server we want to failover and then click the ok.

The failover process is completed after a while. In our example, there are 2 servers in the same cluster.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *