The simplest answer is, no, you won't be able to facilitate this with a 3 node cluster in the manner described.
The reason is due to quorum. Assuming the 3 nodes, 2 at Primary and 1 at DR with Windows Server 2012R2. Dynamic quorum is on by default, this will automatically adjust node weights in case of a node failure. Dynamic witness is also on by default which will change the witness vote to keep the number of total votes odd.
The thing is that dynamic quorum only works if less than half of the nodes go down simultaneously. If 50% or more voting nodes go down at once there won't be enough voters left to keep quorum or for dynamic quorum to decide that this isn't a split brain scenario.
How could you potentially achieve this?
If it would be possible to put 2 nodes at the Primary site, 2 nodes at the DR site, and a witness at a 3rd site then it should do what you're looking for.
is there any way to configure this in a way which automatic failover will occur if the primary and DR sites lose connectivity or if the primary site goes down entirely?
These look the same from the perspective of the DR site. Whether it loses connection with the servers at the primary site or whether the primary site goes down doesn't look any different. In each case they no longer can "see" the other nodes, only the local ones. This results in the race to acquire the lock on the witness. Whichever side attains the lock first, wins.
There is an additional setting in Windows Server 2012R2 called LowerQuorumPriorityNodeID which can be used to weight one side or the other when these types of situations happen.
You have to have a read-routing list, even with only two instances, if you want to offload reads to a secondary replica.
The following script is an example of setting this up (you would change the AG name to whatever your AG is, and the replicas to your servernames (using the fully qualified domain name, and adjusting the port if required).
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA1.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA2.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA2', 'REPLICA1')));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA1', 'REPLICA2')));
As regards the flags:
ReadIntent allows for connections only passed along with the
ApplicationIntent=ReadOnly flag
Yes allows for any connection to the
database (meaning you could connect directly with SSMS and run
queries)
Best Answer
I believe you are taking about unexpected shutdown or similar scenario where primary server not available in this case first WSFC will see if it is in position to update cluster registry to set the status like not synchronizing if the server shutdown is immediate Registry may not get updated with the state. When primary is down it would not send log blocks to secondary and if secondary is also done redoing and undoing all the log records it will also not do any work but wait for log records to be pulled when primary comes online. But let us say when the primary went down secondary was behind it, primary it will continue its process of replaying the log records to come alike primary.
Database will NOT be removed from AG unless user does that, so my above statement does not apply to this.