Replicating Account Objects¶

Executed on Source Account¶

1. Create a role and grant it the CREATE FAILOVER GROUP privilege. This step is optional:

   -- Execute the following SQL statements using the ACCOUNTADMIN role:
   USE ROLE ACCOUNTADMIN;
   
   CREATE ROLE myrole;
   
   GRANT CREATE FAILOVER GROUP ON ACCOUNT
    TO ROLE myrole;
   

   Copy

2. Create a failover group in the source account and enable replication to specific target accounts.

   Note

   • If you have databases to add to a replication or failover group that have been previously enabled for database replication and failover using ALTER DATABASE, follow the Transitioning From Database Replication to Group-Based Replication instructions (in this topic) before adding them to a group.

   • To add a database to a failover group, the active role must have the MONITOR privilege on the database. For details on database privileges, see Database Privileges (in a separate topic).

   USE ROLE myrole;
   
   -- Execute the following SQL statement using a role with the CREATE FAILOVER GROUP privilege:
   CREATE FAILOVER GROUP myfg
       OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES
       ALLOWED_DATABASES = db1, db2
       ALLOWED_ACCOUNTS = myorg.myaccount2, myorg.myaccount3
       REPLICATION_SCHEDULE = '10 MINUTE';
   

   Copy

   Attention

   If account objects (e.g. users or roles) that you do not want to drop during replication exist in the target account, do not include the REPLICATION_SCHEDULE parameter when you create a replication or failover group. For details, see Apply Global IDs to Objects Created by Scripts in Target Accounts.

Executed on Target Account¶

3. Create a role in the target account and grant it the CREATE FAILOVER GROUP privilege. This step is optional:

   -- Execute the following SQL statements using the ACCOUNTADMIN role:
   USE ROLE ACCOUNTADMIN;
   
   CREATE ROLE myrole;
   
   GRANT CREATE FAILOVER GROUP ON ACCOUNT
       TO ROLE myrole;
   

   Copy

4. Create a failover group in the target account as a replica of the failover group in the source account.

   USE ROLE myrole;
   
   -- Execute the following SQL statement using a role with the CREATE FAILOVER GROUP privilege:
   CREATE FAILOVER GROUP myfg
       AS REPLICA OF myorg.myaccount1.myfg;
   

   Copy

5. Manually refresh the secondary failover group. This is an optional step. If the primary failover group is created with a replication schedule, the initial refresh of the secondary failover group is automatically executed when the secondary failover group is created.

   -- Execute the following SQL statement using a role with the REPLICATE privilege:
   ALTER FAILOVER GROUP myfg REFRESH;
   

   Copy

View All Accounts Enabled for Replication¶

To retrieve the list of accounts in your organization that are enabled for replication, use SHOW REPLICATION ACCOUNTS.

-- Execute the following SQL statements using the ACCOUNTADMIN role:
SHOW REPLICATION ACCOUNTS;

+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| snowflake_region       | created_on              | account_name | account_locator |  comment        | organization_name |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_WEST_2          | 2020-07-15 21:59:25.455 | myaccount1   | myacctlocator1  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_EAST_1          | 2020-07-23 14:12:23.573 | myaccount2   | myacctlocator2  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_EAST_2          | 2020-07-25 19:25:04.412 | myaccount3   | myacctlocator3  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+

See the complete list of Region IDs.

View Failover and Replication Group Membership¶

Account, database, and share objects have constraints on group membership. Before creating new groups or adding objects to existing groups, you can review the list of existing failover groups and the objects in each group.

View all failover groups linked to the current account, and the object types in each group:

SHOW FAILOVER GROUPS;

Copy

View all the databases in failover group myfg:

SHOW DATABASES IN FAILOVER GROUP myfg;

Copy

View all the shares in failover group myfg:

SHOW SHARES IN FAILOVER GROUP myfg;

Copy

Enable Account and Database Object Replication from a Source Account to Target Account¶

Create a failover group of specified account and database objects in the source account and enable replication and failover to a list of target accounts. See CREATE FAILOVER GROUP for syntax.

For example, enable replication of users, roles, warehouses, resources monitors, and databases db1 and db2 from the source account to the myaccount2 account in the same organization. Set the replication schedule to automatically refresh myaccount2 every 10 minutes.

Executed on the source account:

use role myrole;

-- Execute the following SQL statement using a role with the CREATE FAILOVER GROUP privilege:
create failover group myfg
    object_types = users, roles, warehouses, resource monitors, databases, integrations, network policies
    allowed_databases = db1, db2
    allowed_integration_types = API INTEGRATIONS
    allowed_accounts = myorg.myaccount2
    replication_schedule = '10 MINUTE';

Grant the REPLICATE Privilege on Failover Group to Role — Optional¶

To execute the command to refresh a secondary replication or failover group in the target account, you must use a role with the REPLICATE privilege on the failover group. The REPLICATE privilege is currently not replicated and must be granted on a failover (or replication) group in both the source and target accounts.

Executed from the source account:

-- Execute the following SQL statements using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role;

Copy

Executed from the target account:

-- Execute the following SQL statements using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role;

Copy

Manually Refresh a Secondary Failover Group¶

For example, to refresh the objects in the failover group myfg, execute the following statement from the target account:

USE ROLE my_replication_role;

-- Execute the following SQL statements using a role with the REPLICATE privilege:
ALTER FAILOVER GROUP myfg REFRESH;

Copy

Example¶

View the progress of the most recent refresh operation for the failover group myfg:

SELECT PHASE_NAME, START_TIME, END_TIME, PROGRESS, DETAILS
  FROM TABLE(information_schema.replication_group_refresh_progress('myfg'));

Examples¶

Query the Information Schema REPLICATION_GROUP_REFRESH_HISTORY table function to view the account replication history of failover group myfg in the last 7 days:

SELECT PHASE_NAME, START_TIME, END_TIME, TOTAL_BYTES, OBJECT_COUNT
  FROM TABLE(information_schema.replication_group_refresh_history('myfg'))
  WHERE START_TIME >= current_date - interval '7 days';

Query the Account Usage REPLICATION_GROUP_REFRESH_HISTORY view to view the account replication history in the current month:

SELECT REPLICATION_GROUP_NAME, PHASE_NAME, START_TIME, END_TIME, TOTAL_BYTES, OBJECT_COUNT
  FROM snowflake.account_usage.replication_group_refresh_history
  WHERE START_TIME >= date_trunc('month', current_date());

Examples¶

Query the REPLICATION_GROUP_USAGE_HISTORY table function to view credits used for account replication in the last 7 days:

SELECT start_time, end_time, replication_group_name, credits_used, bytes_transferred
  FROM table(information_schema.replication_group_usage_history(date_range_start=>dateadd('day', -7, current_date())));

Query the Account Usage REPLICATION_GROUP_USAGE_HISTORY view to view the credits used by replication or failover group for account replication history in the current month:

SELECT start_time, 
  end_time, 
  replication_group_name, 
  credits_used, 
  bytes_transferred
FROM snowflake.account_usage.replication_group_usage_history
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE());

Examples¶

Executed on Target Account¶

1. Query the REPLICATION_GROUP_REFRESH_PROGRESS table function (in the Snowflake Information Schema). Note the primarySnapshotTimestamp in the DETAILS column for the PRIMARY_UPLOADING_METADATA phase. This is the timestamp for the latest snapshot of the primary database.

   SELECT PARSE_JSON(details)['primarySnapshotTimestamp']
     FROM TABLE(information_schema.replication_group_refresh_progress('myfg'))
     WHERE PHASE_NAME = 'PRIMARY_UPLOADING_METADATA';
   

   Copy

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

   SELECT HASH_AGG( * ) FROM mytable;
   

   Copy

Executed on Source Account¶

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

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

   Copy

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