There are different types of failures in the database environment ranging from the loss of the network, to the loss of an instance, all the way to the loss of a node (the server hardware). A robust database is one that can detect such failures and automate the failover and recovery process without any user intervention.SchoonerSQL does exactly that: it detects failures and provides an immediate and automated failover process. Below are the failover scenarios and how SchoonerSQL will handle them.
Instance FailureConsider three instances in a synchronous replication group where Node 1 has the master instance, and Node 2 and Node 3 have slave instances.The master has one write virtual IP (10.1.1.2) and one read virtual IP (10.1.1.3); slave instances have read virtual IPs (10.1.1.4, 10.1.1.5) as shown in the diagram below.
When a master instance fails due to a crash or a shutdown, the failure handler will immediately detect it and migrate all the virtual IPs from the failed instance to Node 2 & Node 3. The entire process is generally quick with an average failover time of 3 to 5 seconds. Clients automatically connect to the surviving node and continue running the queries. The entire operation is transparent to the end-user.SchoonerSQL also possess self-healing capability with an auto restart when a crash happens. The instance will rejoin the cluster once it catches up and is in sync (virtual IPs will be re-distributed to this instance automatically when it joins the group).  Node Failure The failure handler detects when the entire node crashes or shuts down. Virtual IPs will then be migrated from the failed node to the instances on the remaining live nodes in the cluster. Replication Network FailureConsider two instances, one acting as master with write, read virtual IPs and the other acting as slave with read virtual IP.Consider a scenario where the replication network fails causing network partitioning. The failure handler immediately detects this and shuts down the slave. The virtual IP of the slave is then migrated over to the master as shown in the diagram. To conclude, SchoonerSQL not only detects failures but also automates the failover process thereby providing users a seamless and a transparent experience.