We are in the process of configuring a 3:1 failover cluster mechanism for SQL Developer Edition hosted on Windows Server, deployed using an AWS AMI. Our objective is to ensure that if any of the three database instances (DB01, DB02, DB03) encounter a failure, requests are seamlessly routed to the fourth instance (PDB). The fourth database (PDB) contains all the data from DB01, DB02, and DB03.
Currently, we have set up three database instances named DB01, DB02, and DB03 with IP addresses 172.31.22.161, 172.31.22.162 and 172.31.22.163 respectively, in one network cluster with CIDR 172.31.16.0/20. The other cluster network includes the fourth DB instance named 'PDB' with IP address 172.31.39.150 and CIDR 172.31.32.0/20.
Currently, in the event of an instance failure, requests continue to be routed via PDB. We are uncertain if this approach is correct and are not achieving the desired outcome. We seek guidance on the correct approach to achieve this setup.
In addition, we have a question regarding how the cluster will determine which database instance to route requests to when hosting three DB instances in one network cluster. Could you please provide clarification on this aspect as well?
We have configured roles in failover cluster and designated 'preferred owners' as the fourth DB (PDB), so that in the event of failure of any of the three DB instances, requests are directed to PDB. However, when a DB instance returns to a healthy state, requests should be served by that specific DB instance and not by PDB.
