0

My knowledge about AG is very limited and I'm not a DBA.

We have 4 machines - each of them is hosting two databases, say DB1 and DB2. DB1 is has its own AG (say AG1) which is replicated to other 3 machines, and DB2 also has its own AG (say AG2) which is also replicated to same 3 machines. DB1 and DB2 are write replicas. Other 3 machines have read-replicas.

Now if machine 1 fails, what usually happens is that DB1 from machine 1 can fail over to DB1 on machine 3 (and becomes a WRITE replica), and DB2 from machine 1 can fail over to DB2 on machine 4 (and become a WRITE replica).

Question -

In case of machine 1 goes down, is it possible to enforce sql server to fail over both databases (DB1 and DB2) from machine 1 to the same machine? Meaning, I want WRITE replicas of both databases to be always on one machine. Thank you!

Sanjeev Dhiman
  • 1,169
  • 1
  • 11
  • 20

1 Answers1

1

In case of machine 1 goes down, is it possible to enforce sql server to fail over both databases (DB1 and DB2) from machine 1 to the same machine?

Yes. Put both databases in the same Availability Group.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    The real question is why do you have 2 AG's configured in the first place? And why do you have a 4 node cluster? If all nodes are in the same DC - that seems a bit of overkill for just 2 databases. If - however - 2 of the nodes on are DC1 and the other 2 are in DC2 then you should change the failover mode and commit mode so that the 2 instances in the 'other' DC are set to manual failover and asynchronous commit. Then if node1 fails - the failover goes to node2 in that DC. – Jeff Oct 09 '21 at 15:38
  • Yes, actually 2 of them are in 1 DC and while other 2 are in other DC. There are some reasons that we can't have both databases in one AG. – Sanjeev Dhiman Oct 11 '21 at 15:43
  • Then do what @Jeff suggests and disable automatic failover between DCs, which you don't really want anyway. – David Browne - Microsoft Oct 11 '21 at 15:50