MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

Question

For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode.

3 machines:

First machine runs Active Directory.

Second machine runs Node-1 (instance-1).

Third machine runs Node-2 (instance-2).

---

Availability group properties:

Cluster type: windows server failover cluster

Database level health detection is checked.

---

Availability replicas:

Instance-1 (Node-1) properties:

Role: primary

Availability mode: synchronous commit

Failover mode: manual

Connections in primary role: allow all connections

Readable secondary: yes

Seeding mode: manual

Session timeout: 10 seconds

Endpoint URL: TCP://somehost:port

---

Instance-2 (Node-2) properties:

Role: secondary

Availability mode: synchronous commit

Failover mode: manual

Connections in primary role: allow all connections

Readable secondary: yes

Seeding mode: automatic

Session timeout: 10 seconds

Endpoint URL: TCP://somehost:port (same port with instance-1)

---

Failover cluster manager properties:

Roles: 1 role is running and owner ise node-1.

Nodes: 2 nodes are up.

Disks: 1 cluster disk is online, owner node is node-2, capacity is 10GB.

Networks: 1 network is up, cluster use: cluster and client.

How can I fix it?

Best regards.

Answer 1

If I read correctly, your failover mode is set to "manual". In this case, SQL will not trigger a failover by itself.

If you are expecting node 2 to be promoted as primary automatically when node 1 goes down, then you will need to change the failover mode to "automatic" and then, that should do it.