High Availability (HA)and Disaster Recovery (DR) FAQs

What triggers reprovisioning, and how to identify that it is happening?

Reprovisioning is the process of overwriting a replica's data with the master’s data.  Before 7.0, this copied over all the data, but now it only copies snapshot and log data, as well as blob delta (blobs on the master that are not on the replica).

Reprovision happens in two cases:

1. If a replica is too far behind the master in terms of data.

2. If the replica diverges from the master after a failover (this is a rare occurrence).

To identify reprovisioning is happening, you can use the event table and look for the DATABASE_REPROVISION event.  This event will also describe why a reprovision happened.

Can the re-replication of a database or a single partition be forced? What role does SNAPSHOT DATABASE play here?

You cannot force re-replication. In case of DR, you can drop the secondary and recreate it.

SNAPSHOT DATABASE can help when setting up DR, as the database will have to replicate a smaller amount of logs but otherwise has no major role.

How does an intensive ingest on the primary side affect DR and any potentially running provisioning?

In a rare case, DR may lag slightly, but replication is generally very efficient. It should not affect provisioning a new replica much either because an LSN to sync to is created as part of replication, and once that point is reached the operation is complete.

What happens if there is a DDL operation on the primary side during replication? Does this block any operations?

DDL statements (CREATE/ALTER/DROP ) are all propagated to the replicate. No DDL operations are blocked.

How are users/grants/roles/groups/passwords handled with replication?

They are all maintained at the cluster level while replication is set up at the database level.

What are the different configurations for setting up DR?

1. DR across availability zones within a region: If you are setting up HA ie. `redundancy_level=2`, all the leaf nodes of the cluster should be within the same availability zone for better performance. All the nodes in the cluster have some partitions which are master. So putting availability groups, AG1 in one zone and AG2 in another zone can lead to query latency. It is better to configure an entire cluster in one zone and replicate to a DR cluster in another zone within the region. If the primary zone goes down, this gives you the ability to failover to the DR cluster without much impact.

2. DR across regions: This configuration is recommended if you are also concerned about availability in case of data-center failures.

Can you drop a database if replication is set up?

Yes, you can.

What is the impact of leaf node removal or failure on the redundancy mode of data?

It depends on the setting of the leaf_failover_fanout global variable.

In 'paired' mode, redundancy is not maintained when removing leaf nodes.

In 'load_balanced' mode, redundancy is maintained when removing leaf nodes. except for the case where we are removing the last leaf node in an availability group.

Why is it not advisable to have multiple availability groups (AGs) on the same host?

Rebalance uses AGs to make sure it puts a replica of a partition on a different AG than the master partition so that, if the node with the master goes offline, the cluster can failover to the replica which is on a different node. If both master and replica are on the same node you will lose the HA.

How do temporary tables behave in a DR instance?

Temporary tables are not persistent and are not replicated for DR.

What is the Failover Grace Period for flapping leaves in HA?

For sync replication, the default since 7.0, there is no grace period, and failover happens right away since there are no data retention concerns.

For async replication, if a leaf node fails repeatedly and HA is enabled, then a grace period of 300 seconds is initiated (and increases exponentially if flapping continues). During this grace period, failover will not be performed. Grace period is initiated upon any leaf node failure in async replication and starts at 5 minutes. If the leaf node fails after the grace period, the grace period is reset to 0. If the leaf node is not failing anymore, then it must be flapping and the grace period is doubled. Grace period can be disabled with the following settings in memsql.cnf on every node: 'failover_initial_grace_interval_seconds = 0' and 'failover_maximum_grace_interval_seconds = 0'.

How are pipelines handled in replication?

CREATE/DROP/ALTER pipeline commands are replicated to the replicate database. However, the replicate database is read-only so the pipelines will be running only on the primary not on the replicate.  

One caveat, when you stop replication, the pipelines on the replicate cluster will inherit the state when the replication was stopped. So if the pipelines were running on the primary when the replication stops, they will start running on the replicate. Conversely, if the pipelines are stopped, they will remain stopped on the replicate database after the replication is stopped. FS pipelines require special consideration ( i.e. access to the same filesystem).

Does taking a snapshot of the primary database have an impact on the speed of replication?

Taking a snapshot will help the speed at which replication is first set up. However, it will not change the replication speed after replication is up and running.

How are inflight queries and transactions handled in a failover scenario?

For the most part, "connection lost errors" are not retried if there is an "offline" failover (i.e. a node is down).

UPDATEs and DELETEs are retried.

SELECT queries that have sent any rows back to the user already cannot be retried.

Queries inside multi-statement transactions other then the first query cannot be retried. For example:

BEGIN;
INSERT;
INSERT;  <--- if we hit a failover here we can't retry.
COMMIT;

This  transaction will rollback if any node fails.

Last modified: March 16, 2023

Was this article helpful?