Failing Over Databases Across Multiple Accounts

Business Critical Feature

Requires Business Critical (or higher). To inquire about upgrading, please contact Snowflake Support.

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.

In this Topic:

Web Interface for Database Replication and Failover/Failback

Account administrators (users with the ACCOUNTADMIN role) can use the Replication area of the Databases tab in the Snowflake web interface 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.

Prerequisite Requirements

1. Enable replication for a primary database in a set of accounts.

2. 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 in Your Organization

Retrieve the list of accounts in your organization to determine which accounts have been linked for database replication and failover.

To view the list of accounts in your organization, query SHOW REPLICATION ACCOUNTS.

SHOW REPLICATION ACCOUNTS;

+------------------+---------------------------------+---------------+------------+
| snowflake_region | created_on                      | name          | comment    |
|------------------+---------------------------------+---------------+------------|
| AWS_US_WEST_2    | 2018-11-19 16:11:12.720 -0700   | MYACCOUNT1    |            |
| AWS_US_EAST_1    | 2019-06-02 14:12:23.192 -0700   | MYACCOUNT2    |            |
+------------------+---------------------------------+---------------+------------+

The following table displays the complete list of Snowflake Region IDs:

Snowflake Region IDs

Region	Region ID	Snowflake Region ID	Notes
Amazon Web Services (AWS)
US West (Oregon)	us-west-2	aws_us_west_2
US East (Ohio)	us-east-2.aws	aws_us_east_2
US East (N. Virginia)	us-east-1	aws_us_east_1
US East (Commercial Gov - N. Virginia)	us-east-1-gov.aws	aws_us_east_1_gov	Available only for accounts on Business Critical (or higher); not located in AWS GovCloud (US), which is a separate, dedicated cloud not yet supported by Snowflake.
Canada (Central)	ca-central-1.aws	aws_ca_central_1
EU (Ireland)	eu-west-1	aws_eu_west_1
EU (Frankfurt)	eu-central-1	aws_eu_central_1
Asia Pacific (Tokyo)	ap-northeast-1.aws	aws_ap_northeast_1
Asia Pacific (Mumbai)	ap-south-1.aws	aws_ap_south_1
Asia Pacific (Singapore)	ap-southeast-1	aws_ap_southeast_1
Asia Pacific (Sydney)	ap-southeast-2	aws_ap_southeast_2
Google Cloud Platform (GCP)
US Central1 (Iowa)	us-central1.gcp	gcp_us_central1
Europe West2 (London)	europe-west2.gcp	gcp_europe_west2
Europe West4 (Netherlands)	europe-west4.gcp	gcp_europe_west4
Microsoft Azure
West US 2 (Washington)	west-us-2.azure	azure_westus2
East US 2 (Virginia)	east-us-2.azure	azure_eastus2
US Gov Virginia	us-gov-virginia.azure	azure_usgovvirginia	Available only for accounts on Business Critical (or higher).
Canada Central (Toronto)	canada-central.azure	azure_canadacentral
West Europe (Netherlands)	west-europe.azure	azure_westeurope
Southeast Asia (Singapore)	southeast-asia.azure	azure_southeastasia
Switzerland North (Zurich)	switzerland-north.azure	azure_switzerlandnorth
Australia East (New South Wales)	australia-east.azure	azure_australiaeast

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 (in region aws_us_west_2) to accounts myaccount2 and myaccount3 (in regions aws_us_east_1 (AWS) and azure_westeurope (Azure), respectively). In this example, suppose the primary database is stored in the myaccount1 account. The ALTER DATABASE command must be executed in that account:

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

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;

-- Verify that the former secondary database was promoted successfully.
SHOW REPLICATION DATABASES;