Replicating databases across multiple accounts¶

Manage primary databases¶

1. Sign in to Snowsight to a Snowflake account that contains a primary database.

2. Select the dropdown menu in the upper left (next to your login name) » Switch Role » ACCOUNTADMIN.

3. From the left-hand navigation pane, select Data » Databases. Select a primary database in the database object explorer. The database details page opens.

   Alternatively, to view only databases that have been enabled for replication, use the Replication Status » Primary filter to list primary databases in the account. Select a database from the list to open the details page.

   Note

   The Replication Status filter is only available if an account is a source or target account for database replication.

4. Select » Enable Replication. The Enable replication dialog opens.

   Choose the action that you want to perform:

   • Enable failover. This feature requires Business Critical Edition (or higher).

   • Create a secondary database in one or more target accounts.

     If a primary database in another account is enabled for replication to the current account, you can create a secondary database in the current account. To add additional target accounts, use the ALTER DATABASE command in the source account to update the primary database.

   • Refresh each secondary database once, after it is created.

5. For each target account for this database, check the options to create a secondary database and refresh the database.

6. Sign in to the target account as a user who was previously granted the ACCOUNTADMIN role in that account.

   Snowflake performs the requested actions and displays a success dialog.

   Manage replication for this database from the Replication tab in the database details.

Manage secondary databases¶

1. Sign in to Snowsight to a Snowflake account that contains a secondary database.

2. Select the dropdown menu in the upper left (next to your login name) » Switch Role » ACCOUNTADMIN.

3. From the left-hand navigation pane, select Data » Databases.

   The following actions are available from the actions (…) button in the upper-right corner of the page:

   • Create a secondary database.

     Note

     This option is only available if an account is a source or target account for database replication.

     If a primary database in another account is enabled for replication to the current account, you can create a secondary database in the current account. To add additional target accounts, use the ALTER DATABASE command in the source account to update the primary database.

4. Select a secondary database in the database object explorer. The database details page opens.

5. Select the Replication tab.

   The following actions are available from the actions (…) button in the upper-right corner of the page:

   • Promote the secondary database to serve as the primary database. This feature requires Business Critical Edition (or higher).

     Note

     In order to promote a secondary database to serve as the primary, the primary database must have failover enabled to the target account where the secondary database is located.

     If this option is not available, you can use the ALTER DATABASE command in the source account to enable failover for the primary database to the target account. For more information, see Step 3: Enabling failover for a primary database.

   • Refresh the secondary database.

   • Copy a template to create a task that refreshes the secondary database on a schedule. Paste the template into a Snowsight worksheet and edit it to specify the desired schedule.

Step 1: Viewing all accounts in your organization¶

Retrieve the list of accounts in your organization in which replication has been enabled. Any existing permanent or transient database in these accounts can be modified to serve as a primary database. Replicas of a primary database (i.e. secondary databases) can only be created in these accounts.

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

SHOW REPLICATION ACCOUNTS;

+------------------+---------------------------------+---------------+------------------+---------+-------------------+
| 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: Promoting a local database to serve as a primary database¶

Modify an existing permanent or transient database to serve as a primary database using an ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement. Provide a comma-separated list of accounts in your organization that can store a replica of this database (i.e. a secondary database), allowing users in those accounts to query objects in the secondary database.

ALTER DATABASE mydb1 ENABLE REPLICATION TO ACCOUNTS myorg.account2, myorg.account3;

Step 3: Enabling failover for a primary database¶

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.

-- Executed from primary account
ALTER DATABASE mydb1 ENABLE FAILOVER TO ACCOUNTS myorg.account2, myorg.account3;

Step 4: Creating a secondary database¶

Create a replica of an existing primary database in the same account that stores the primary database, or a different account (in the same or a different region). Note that you can only create a secondary database in an account specified in the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement in Step 2: Promoting a Local Database to Serve as a Primary Database.

Execute a CREATE DATABASE … AS REPLICA OF statement in each target account to create a replica of the specified primary database.

To view the list of primary and secondary databases in your organization, query SHOW REPLICATION DATABASES. After a secondary database is created, an account administrator can transfer ownership of the database to another role (using GRANT OWNERSHIP.)

-- Log into the ACCOUNT2 account.

-- Query the set of primary and secondary databases in your organization.
-- In this example, the MYORG.ACCOUNT1 primary database is available to replicate.
SHOW REPLICATION DATABASES;

+------------------+-------------------------------+-----------------+----------+---------+------------+----------------------------+---------------------------------+------------------------------+-------------------+-----------------+
| snowflake_region | created_on                    | account_name    | name     | comment | is_primary | primary                    | replication_allowed_to_accounts | failover_allowed_to_accounts | organization_name | account_locator |
|------------------+-------------------------------+-----------------+----------+---------+------------+----------------------------+---------------------------------+------------------------------+-------------------+-----------------|
| AWS_US_WEST_2    | 2019-11-15 00:51:45.473 -0700 | ACCOUNT1        | MYDB1    | NULL    | true       | MYORG.ACCOUNT1.MYDB1       | MYORG.ACCOUNT2, MYORG,ACCOUNT1  | MYORG.ACCOUNT1               | MYORG             | MYACCOUNT1      |
+------------------+-------------------------------+-----------------+----------+---------+------------+----------------------------+---------------------------------+------------------------------+-------------------+-----------------+

-- Create a replica of the 'mydb1' primary database
-- If the primary database has the DATA_RETENTION_TIME_IN_DAYS parameter set to a value other than the default value,
-- set the same value for the parameter on the secondary database.
CREATE DATABASE mydb1
  AS REPLICA OF myorg.account1.mydb1
  DATA_RETENTION_TIME_IN_DAYS = 10;

-- Verify the secondary database
SHOW REPLICATION DATABASES;

+------------------+-------------------------------+---------------+----------+---------+------------+-------------------------+---------------------------------+------------------------------+-------------------+-----------------+
| snowflake_region | created_on                    | account_name  | name     | comment | is_primary | primary                 | replication_allowed_to_accounts | failover_allowed_to_accounts | organization_name | account_locator |
|------------------+-------------------------------+---------------+----------+---------+------------+------------------------------------------+----------------+------------------------------+-------------------------------------|
| AWS_US_WEST_2    | 2019-11-15 00:51:45.473 -0700 | ACCOUNT1      | MYDB1    | NULL    | true       | MYORG.ACCOUNT1.MYDB1    | MYORG.ACCOUNT2, MYORG.ACCOUNT1  | MYORG.ACCOUNT1               | MYORG             | MYACCOUNT1      |
| AWS_US_EAST_1    | 2019-08-15 15:51:49.094 -0700 | ACCOUNT2      | MYDB1    | NULL    | false      | MYORG.ACCOUNT1.MYDB1    |                                 |                              | MYORG             | MYACCOUNT2      |
+------------------+-------------------------------+---------------+----------+---------+------------+-------------------------+---------------------------------+------------------------------+-------------------+-----------------+

Step 5. Refreshing each secondary database¶

The instructions in this section explain how to refresh a secondary database from a snapshot of its primary database (using ALTER DATABASE … REFRESH). A snapshot includes changes to the objects and data. For the initial replication of a very large primary database, we recommend increasing the statement timeout.

To verify the current region after you log into an account, query the CURRENT_REGION function.

ALTER DATABASE mydb1 REFRESH;

You can also refresh a secondary database in the web ui.

Step 6. Refreshing a secondary database on a schedule¶

As a best practice, we recommend scheduling your secondary database refreshes. This section provides instructions for starting a database refresh automatically on a specified schedule.

The frequency with which you refresh a secondary database depends on the Recovery Point Objective (RPO) for the data in the secondary database. For example, if applications that rely on the data can tolerate up to 1 hour of data loss, then you must refresh the data at least every hour. If the data loss tolerance is 5 minutes, then refresh the secondary database at least every 5 minutes.

Complete the steps in this section to start a database refresh automatically on a specified schedule.

Prerequisites:

The following Snowflake objects are required in the account that stores the secondary database:

• The secondary database.

• A separate database to store the new objects created in this section. Because secondary databases are read-only, this database must be separate from the secondary database. This database must also include the following objects:

  • Schema. Use the PUBLIC schema, or create a new schema using CREATE SCHEMA.

  • Warehouse. Any warehouse can be provided here to meet the syntax requirement but is not used for the database refresh. Create a new warehouse using CREATE WAREHOUSE.

  • Task that refreshes the secondary database on a schedule.

Required privileges:

The steps in this section require a role with the following privileges in the account in which the secondary database is refreshed:

Object Type	Object	Privilege	Notes
Account	Account that stores the secondary database	EXECUTE TASK	Required to run the new task.
Database	Secondary database	OWNERSHIP	Required to refresh the secondary database.
Database	Database that stores the new task	USAGE
Schema	Schema that stores the new task	USAGE, CREATE TASK
Task		OWNERSHIP	The role that creates the task owns the object by default. Ownership can be transferred to a different role using GRANT privileges … TO ROLE.
Warehouse	Warehouse used to configure the task	USAGE	Specifying a warehouse is required to configure the task, however the warehouse is not used to run the task or for the refresh operation.

Steps:

Complete the following steps for each secondary database you want to refresh on a schedule:

1. Create a task that starts the database refresh on a schedule (using CREATE TASK). Note that although the CREATE TASK syntax for specifying a replication schedule requires a warehouse, the warehouse is not used for replication.

   For example, create a task named refresh_mydb1_task that refreshes a secondary database named mydb1 every 10 minutes with a 4 hour timeout. The task is configured using the existing warehouse mywh:

   CREATE TASK refresh_mydb1_task
     WAREHOUSE = mywh
     SCHEDULE = '10 minute'
     USER_TASK_TIMEOUT_MS = 14400000
   AS
     ALTER DATABASE mydb1 REFRESH;
   

   Copy

2. A task is suspended by default when it is created. Resume the task to allow it to run based on the parameters specified in the task definition:

ALTER TASK refresh_mydb1_task RESUME;

Copy

Example¶

Execute the following SQL statements in your preferred Snowflake client to enable replication and failover, do an initial database refresh and set up scheduled refreshes.

-- The commands below are executed from the source account

-- View replication enabled accounts
SHOW REPLICATION ACCOUNTS;

ALTER DATABASE mydb ENABLE REPLICATION TO ACCOUNTS myorg.account2, myorg.account3;
ALTER DATABASE mydb ENABLE FAILOVER TO ACCOUNTS myorg.account2, myorg.account3;

-- The commands below are executed from each target account

-- View replication enabled databases
-- Note the primary column of the source database for the CREATE DATABASE statement below
SHOW REPLICATION DATABASES;

-- If the primary database has the DATA_RETENTION_TIME_IN_DAYS parameter set to a value other than the default value,
-- set the same value for the parameter on the secondary database.
CREATE DATABASE mydb
  AS REPLICA OF myorg.account1.mydb
  DATA_RETENTION_TIME_IN_DAYS = 10;

-- Increase statement timeout for initial refresh
-- Optional but recommended for initial refresh of a large database
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 604800;
-- If you have an active warehouse in current session, update warehouse statement timeout
SELECT CURRENT_WAREHOUSE();
ALTER WAREHOUSE my_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 604800;
-- Reset warehouse statement timeout after initial refresh
ALTER WAREHOUSE my_wh UNSET STATEMENT_TIMEOUT_IN_SECONDS;

-- Refresh a secondary database
ALTER DATABASE mydb REFRESH;

-- Create task
-- Set up refresh schedule for each secondary database using a separate database
USE DATABASE my_db2;

-- Create a task and RESUME the task for each secondary database
-- Edit the task schedule and timeout for your specific use case
CREATE TASK my_refresh_task
  WAREHOUSE = my_wh
  SCHEDULE = '10 minute'
  USER_TASK_TIMEOUT_MS = 14400000
AS
  ALTER DATABASE mydb REFRESH;

-- Start task
ALTER TASK my_refresh_task RESUME;

Using the legacy account locator¶

Though the legacy snowflake_region.account_locator format is currently supported when identifying an account in replication and failover commands, its use is discouraged as it may stop working in the future.

Executed on the secondary database¶

1. On the secondary database, query the DATABASE_REFRESH_PROGRESS table function (in the Snowflake Information Schema). Note the snapshot_transaction_timestamp in the DETAILS column for the PRIMARY_UPLOADING_DATA phase. This is the timestamp for the latest snapshot of the primary database.

   select parse_json(details)['snapshot_transaction_timestamp']
   from table(information_schema.database_refresh_progress(mydb))
   where phase_name = 'PRIMARY_UPLOADING_DATA';
   

   Copy

2. Query the HASH_AGG function for a specified table. The following query returns a hash value for all rows in the mytable table:

   SELECT HASH_AGG( * ) FROM mytable;
   

   Copy

Executed on the primary database¶

3. On the primary database, query the HASH_AGG function for the same table. Using Time Travel, specify the timestamp when the latest snapshot was taken for the secondary database:

   SELECT HASH_AGG( * ) FROM mytable AT(TIMESTAMP => '<snapshot_transaction_timestamp>'::TIMESTAMP);
   

   Copy

4. Compare the results from the two queries. The output should be identical.