I am doing some testing on SQL 2017 Availability Groups and have the following setup:
- 1 SQL Clustered instance on a 2-node WSFC in DC1
- 1 standalone SQL node in DC2
There is 1 AG, containing 3 DBs, and the clustered instance in DC1 is the primary replica.
The premise for this config is if DC1 goes down in its entirety, then we could failover to DC2 and be up and running in x time period. Therefore, I got one of our Infrastructure team to drop the NICs on the clustered nodes to simulate the DC being trashed. I was able to failover to DC2 and the secondary replica without issue.
However, when I asked my colleague to bring the clustered nodes back online, when they came up, they think they're the Primary replica too. I assume this is because that is the state it knew it was last in when it "crashed" and during the failover (via the Failover Wizard), I was not able to tell it to be the secondary.
Therefore, my question is - is there something I can do to make DC1 aware it's now the secondary OR in a scenario like mine, would I trash the AG once DC2 was the Primary and if/when DC1 came back online, I'd recreate the AG and when ready, fail back to DC1?
Hope that makes some sense to someone. Thanks.