Behavior when I stop SQL Server Service :
The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management).
SQL Server does not connect with the Cluster ip (I'm using a fixed cluster ip inside the software for it to connect on both SQL1 and SQL2. using
x.x.x.10
should connect me on bothx.x.x.9
(SQL1) andx.x.x.8
(SQL2) because of thewindows failover cluster
.- I need to manually move AG to secondary ( I connect on SQL2 and
right click > failover
).
- I need to manually move AG to secondary ( I connect on SQL2 and
To return everything as it was, I need to start
SQL1 SERVICE
manually, connect onSQL1 SSMS
andright click > failover
manually. Stop NODE2 service on cluster management so it turns SQL1 node primary again.
Behavior when I stop the primary cluster node:
The availability group goes to SQL2/NODE 2 (Secondary is now primary).
Primary AG is not Resolving...
SQL Server connect with the cluster ip ( I'm talking about SSMS . connecting with ip
X.x.x.10
, connects me withx.x.x.8
that is the secondary node, after killing nodex.x.x.9
( Primary ).When I start the cluster node (primary) again, it doenst automatically returns do SQL1 (primary). connecting with
x.x.x.10
still connects me with the secondary node.If I want to make SQL1 primary again, I need to
**stop** node2 cluster service
and start it again. this way, SQL1 (primary) is primary again connecting with Cluster IP.
This is totally not the automatically I think I would have. I thought that if SQL Server dies, all connections goes to the secondary automatically, but it just failover if the primary NODE is down
(not sql server service). and then to return to the primary, I need to STOP SQL2 cluster service, so the primary becomes primary again.
Is there something that I'm missing?
We are not using a Failover Cluster with shared storage. There are 2 servers with their own disks.