Failing over account objects

This topic describes the steps necessary to fail over replicated account objects across multiple accounts in different regions for disaster recovery.

Prerequisite requirements

  1. Enable replication for a primary failover group in a set of accounts.

  2. Create at least one secondary failover group (i.e. replica) of the primary failover group in one or more accounts and regularly refresh (i.e. synchronize) the replica with the latest updates to the objects in the failover group.

For instructions, see Replicating databases and account objects across multiple accounts.

Promoting a target account to serve as the source account

To promote a target account to serve as the source account, execute the ALTER FAILOVER GROUP … PRIMARY command.

Promote a secondary failover group to primary failover group

Note

The example in this section must be executed by a role with the FAILOVER privilege.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

The following example promotes myaccount2 in the current myorg organization to serve as the source account for replication of the objects specified in the failover group myfg.

Executed from myaccount2 account:

ALTER FAILOVER GROUP myfg PRIMARY;
Copy

Resume scheduled replication in target accounts

On failover, scheduled refreshes on all secondary failover groups are suspended. ALTER FAILOVER GROUP … RESUME must be executed in each target account with a secondary failover group to resume automatic refreshes.

ALTER FAILOVER GROUP myfg RESUME;
Copy

Reopen active channels for Snowpipe Streaming in newly promoted source account

Tables in a primary database that are populated by Snowpipe Streaming are replicated to secondary databases. After failover, reopen active Snowpipe Streaming channels for tables and re-insert any missing data rows for the channels:

  1. Reopen active channels for the table by calling the openChannel API.

  2. Fetch offset tokens:

    1. Call the getLatestCommittedOffsetToken API or

    2. Execute the SHOW CHANNELS command to retrieve a list of the active channels of the table.

  3. Re-insert data rows for the channel from the fetched offset tokens.

Snowpipe Streaming and the Kafka connector

If you are using the Kafka connector and Snowpipe Streaming, follow these steps after failover:

  1. Update the Kafka connector configuration to point to the newly promoted source account.

  2. Execute the SHOW CHANNELS command to retrieve the list of active channels and the offset tokens. Each channel belongs to a single partition in the Kafka topic.

  3. Manually reset offsets in the Kafka Topic for each of those partitions (channels).

  4. Restart the Kafka Connector.

For more information, refer to: