SQL Server AlwaysOn: How to promote a secondary replica to primary role?

Think about a situation where you got a SQL Server Always On cluster with three nodes where one is the primary node and the other two are secondaries. The failover mode is set to manual for all three nodes. Now suddenly your primary has gone down and you need to make one of the secondaries a primary node.

You can immediately notice that all the databases which are part of the AG are in the “Not Synchronizing” state in all the sync secondaries. This is expected because the primary node is not available.

Let’s see how to manage this situation.

You need to connect to a secondary node where you want it to be the primary and then execute the following T-SQL command:

ALTER DATABASE [db_name] SET HADR RESUME

Apply this command for each database in the AG. You will notice the databases are now showing as Synchronized in SSMS. Yet, there is no primary. While being connected to the same secondary, issue the following command to make it the primary.

ALTER AVAILABILITY GROUP [AG-NAME] FORCE_FAILOVER_ALLOW_DATA_LOSS

Just replace the AG-NAME with the actual AG name. Now, the cluster has a primary node and your application can resume working. I just explained here a hypothetical situation but the same thing can be applied in a real situation where you’re primary node had failed or it could not be failed over due to some reason. The most appropriate solution at this point is to promote one of the secondaries to a primary role, as described in the blog post.

About the post header picture: It was taken during the hike in Roral Arch Trail in Colorado. The picture is shown the final point of the hike on Mar, 7th 2020.