If you want to be able to sustain a failover of two nodes within the failover cluster, then you'll need to ensure that you have five voters for quorum.
You currently have three nodes, and even if you were to add a disk witness, you'd still be at the same point: whether you have three or four possible quorum votes, you'd still need to have three votes for quorum (you need to have more than half of the votes for quorum).
I've never heard the terminology of a "witness server" before, and I think that person is just trying to give a logical name to simply adding another node to your cluster.
In other words, if you now have a four node cluster with a disk witness, that'll be a total of five voters. Five voters will allow you to sustain two nodes to fail (provided your disk witness continues to be functioning properly).
With that being said, you also need to answer a few questions for yourself:
Does you really need three separate instances in this cluster? What is the driving force behind that?
Can one of your possible owning nodes for the FCI be able to sustain all three instances of SQL Server? (think about it, there could be a lot of serious contention there)
Do all of these instances need to be in the same cluster?
And I do want to echo what @AaronBertrand has said above in his comment on your question. There is no such thing as an active / active [ / n [ / n ... ] ] cluster for SQL Server. It's extremely misleading to think of it like that, and that terminology has traditionally caused a lot of confusion (not to mention, the basic problem of a wrong title for something).
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.
Best Answer
With the current configuration you cannot assuming the 2 nodes simultaneous went down. To avoid this you have to add one more node from the DR side into the WSFC configuration and set up a fileshare witness(preferred). The FS witness should reside on VM or machine which is accessible to both production and DR and should be kept at "third place" which is not affected when either of DC or DR is completely down. I would suggest cloud witness here. Cloud witness do have some lags so please test the scenario with various test cases. With 4 nodes and 1 FS witness even if 2 nodes at DC went down there are still 3 votes from 2 nodes at DR and one FS witness to keep WSFC up and running.
The quorum for this configuration would be
Node and Fileshare majority.Replying to your question from comments
No WSFC would not survive because there are 4 voting members in WSFC and we need more than 2(More than 50%) to be voting for quorum and WSFC to be online.
Now this is something different from unexpected failover, yes you can remove votes from DR nodes or, if you are using
Windows server 2012 r2and above you can gracefully shutdown the 2 nodes at DC and still the DR node would be online, this is called as last man standing. This is because of dyanamic quorum and dynamic witness functionality of Windows server 2012 r2 server. But please keep in mind, in this scenario you are doing shutdown in planned manner and that is why dynamic quorum and witness are working if the 2 nodes go down unexpectedly nothing would work and whole WSFC would come down.For your current configuration if suppose due to unexpected DC shutdown your WSFC is down you can use forcequorum to bring the WSFC online at DR node. This method is not complex and if some downtime can be afforded this is useful method. A simple practice and it would hardly take 5-10 mins to bring WSFC online. This will save cost of additional nodes and additional infrastructure.