Failing over databases across multiple accounts¶

Important

This section describes a limited database replication feature that is different from the account replication feature. Snowflake strongly recommends using the account replication feature to replicate and failover databases.

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

Note

Only account administrators (users with the ACCOUNTADMIN role) can enable and manage failover for a database.
Snowflake recommends using the account replication feature to failover databases. Replication and failover groups enable replication of multiple databases and other account objects with point-in-time consistency. Failover groups additionally enable failing over a collection of objects as a unit. For a full list of feature availability and supported objects, refer to Introduction to replication and failover across multiple accounts.

Web interface for database replication and failover/failback¶

Attention

Managing and monitoring replication and failover/failback in Snowsight and Classic Console are only available to accounts using private connectivity.

For all other accounts, see Use Snowsight to monitor replication and Replicating account objects and databases.

Account administrators (users with the ACCOUNTADMIN role) can manage replication and failover/failback actions in either Snowsight or Classic Console.

Snowsight¶

See Web interface for database replication and failover/failback for instructions on promoting a local database to serve as the primary database.

Classic Console¶

Use the Replication area of the Databases tab in the Classic Console to perform most actions related to configuring and managing database replication, including the following actions:

Promote a local database to serve as a primary database.
Enable failover for a primary database (Business Critical Edition accounts (or higher)).
Refresh a secondary database, either once (manually) or repeatedly (on a schedule, using a task).
Promote a secondary database to serve as a primary database (Business Critical Edition accounts (or higher)).
Disable replication and/or failover for a primary database.

Account identifier for replication and failover SQL commands¶

The example SQL statements in the instructions below use an account identifier in the format, organization_name.account_name. However, account identifiers in the format snowflake_region.account_locator are supported.

For more details, see Account identifiers for replication and failover.

Prerequisite requirements¶

Enable replication for a primary database in a set of accounts.
Create at least one secondary database (i.e. replica) of the primary database in one or more of the accounts specified in Step 1, and regularly refresh (i.e. synchronize) the replica with the latest updates to the primary database.

For instructions, see Replicating databases across multiple accounts.

Step 1: Viewing all accounts enabled for replication¶

Query SHOW REPLICATION ACCOUNTS to view the list of accounts in your organization in which replication has been enabled.

SHOW REPLICATION ACCOUNTS;

Copy

+------------------+---------------------------------+---------------+------------------+---------+-------------------+
| snowflake_region | created_on                      | account_name  | account_locator  | comment | organization_name |
|------------------+---------------------------------+---------------+------------------+---------+-------------------|
| AWS_US_WEST_2    | 2018-11-19 16:11:12.720 -0700   | ACCOUNT1      | MYACCOUNT1       |         | MYORG             |
| AWS_US_EAST_1    | 2019-06-02 14:12:23.192 -0700   | ACCOUNT2      | MYACCOUNT2       |         | MYORG             |
+------------------+---------------------------------+---------------+------------------+---------+-------------------+

See the complete list of Region IDs.

Step 2: Enabling failover for a primary database¶

Note

Skip this step if you enabled failover for this primary database in Replicating databases across multiple accounts.

Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE … ENABLE FAILOVER TO ACCOUNTS statement. The replica of this primary database in any one of these accounts (i.e. a secondary database) can be promoted to serve as the primary database.

Note that enabling failover for a primary database can be done either before or after a replica of the primary database has been created in a specified account.

Example¶

Enable failover for primary database mydb1 to accounts myaccount2 and myaccount3. In this example, suppose the primary database is stored in the myaccount1 account and all three accounts belong to the organization, myorg. The ALTER DATABASE command must be executed from myaccount1.

ALTER DATABASE mydb1 ENABLE FAILOVER TO ACCOUNTS myorg.myaccount2, myorg.myaccount3;

Copy

Step 3: Promoting a replica database to serve as the primary database¶

Any replica of a primary database can be promoted to serve as the primary database by executing an ALTER DATABASE … PRIMARY statement. When promoted, the database becomes writeable. At the same time, the previous primary database becomes a read-only replica database.

Execute the ALTER DATABASE statement in the account containing the secondary database that you are promoting.

Note

To promote a secondary database, the role used to perform the operation must have the OWNERSHIP privilege on the database.

Example¶

Promote a secondary database to serve as the primary database.

ALTER DATABASE mydb1 PRIMARY;

Copy

Verify that the former secondary database was promoted successfully.

SHOW REPLICATION DATABASES;

Copy