Hi Mates,
Today I would like to explore different scenarios on Multi Subnet Always on.
There are many ways to configure Multi Subnet Always on your Environment. However I followed the instructions in the blog as they are simple & clear.
After setting up the below Environment I just want to explore few cases.
Windows Version: 2012 R2
SQL Version: 2017 RTM
Configured Always on between 2 Nodes (Node 1 & Node 2) & they are operating in Synchronous mode. And I have not configured any quorum.
Case 1: If we operate in Automatic fail over mode & shutdown the primary node (graceful shutdown) then as expected the other node would become active & it becomes the primary.
Case 2: if we are operate in manual mode then if you shutdown primary node(graceful) then secondary node wouldn't become primary and the state of the Availability group is Resolving. So we need to fire ALTER AVAILABILITY GROUP <GROUPNAME> FAILOVER.
Case 3: We are operating in Automatic fail over mode & I unplugged the network cable no issues occurred and could see node getting failed over to another
Case 4: We are operating in Manual mode now when I unlpugged the network cable boom we are gone. Now everything went down & even the status of the AG Group is appearing as Distributed.
until I plugged it back I was not to resolve the issue
All the above cases are done with out file share witness or disk witness . We didn't found any issues in the above first 3 cases but when it comes to 4th we are completely helpless.
To overcome this problem We need Quorum so in my case I went with file share witness & unplugged the network cable .Here is the snap showing our node is down
However this time we can get back the services to run by firing below command based on the mode you are operating
Synchronous:
ALTER AVAILABILITY GROUP <GROUPNAME> FAILOVER
Asynchronous:
ALTER AVAILABILITY GROUP <GROUPNAME> FORCE_FAILOVER_ALLOW_DATA_LOSS
Now you guys knows the importance of QUORUM. In the next post I will come up with few other scenarios. Until then Happy Reading.
Today I would like to explore different scenarios on Multi Subnet Always on.
There are many ways to configure Multi Subnet Always on your Environment. However I followed the instructions in the blog as they are simple & clear.
After setting up the below Environment I just want to explore few cases.
Windows Version: 2012 R2
SQL Version: 2017 RTM
Configured Always on between 2 Nodes (Node 1 & Node 2) & they are operating in Synchronous mode. And I have not configured any quorum.
Case 1: If we operate in Automatic fail over mode & shutdown the primary node (graceful shutdown) then as expected the other node would become active & it becomes the primary.
Case 2: if we are operate in manual mode then if you shutdown primary node(graceful) then secondary node wouldn't become primary and the state of the Availability group is Resolving. So we need to fire ALTER AVAILABILITY GROUP <GROUPNAME> FAILOVER.
Case 3: We are operating in Automatic fail over mode & I unplugged the network cable no issues occurred and could see node getting failed over to another
Case 4: We are operating in Manual mode now when I unlpugged the network cable boom we are gone. Now everything went down & even the status of the AG Group is appearing as Distributed.
until I plugged it back I was not to resolve the issue
All the above cases are done with out file share witness or disk witness . We didn't found any issues in the above first 3 cases but when it comes to 4th we are completely helpless.
To overcome this problem We need Quorum so in my case I went with file share witness & unplugged the network cable .Here is the snap showing our node is down
However this time we can get back the services to run by firing below command based on the mode you are operating
Synchronous:
ALTER AVAILABILITY GROUP <GROUPNAME> FAILOVER
Asynchronous:
ALTER AVAILABILITY GROUP <GROUPNAME> FORCE_FAILOVER_ALLOW_DATA_LOSS
Now you guys knows the importance of QUORUM. In the next post I will come up with few other scenarios. Until then Happy Reading.
Comments