Configuring 3:1 Failover Cluster for SQL Developer Edition on Windows Server with AWS AMI

Question

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 xxx.31.22.161, xxx.31.22.162 and xxx.31.22.163 respectively, in one network cluster with CIDR xxx.31.16.0/20. The other cluster network includes the fourth DB instance named 'PDB' with IP address xxx.31.39.150 and CIDR xxx.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.

Answer 1

Hi Zaid,

Hope you're doing well.

When hosting three DB instances in one network cluster, the failover cluster will determine which instance to route requests to based on the following factors:

1. You've designated PDB as the preferred owner. In case of failure, requests will be directed to PDB.
2. The cluster quorum configuration plays a crucial role. Quorum ensures that the cluster can make decisions even if some nodes are unavailable.
3. You can configure failover policies to control which instance becomes the active owner after a failover event.
4. The cluster continuously monitors the health of instances. When a failed instance recovers, it should automatically resume serving requests.

Below are the relevant doc for your reference:

clustering - Configuring 3:1 Failover Cluster for SQL Developer Edition on Windows Server with AWS AMI - Database Administrators Stack Exchange

SQL Server Multi-Subnet Clustering - SQL Server Always On | Microsoft Learn

Best Regards

Answer 2

Attached is the reference image for above question.