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;
   

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';
   

   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 Step 5. Apply Global IDs to Objects Created by Scripts in Target Accounts — Optional.

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;
   

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;
   

5. Apply global IDs to objects. This is an optional step for any account objects not created via replication.

   -- Execute the following SQL statements using the ACCOUNTADMIN role:
   SELECT SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME('myfg');
   

   Attention

   This step must be executed if account objects (e.g. users or roles) that you do not want to drop during replication exist in the target account. Skipping this step may result in the loss of user worksheets or privilege grants to roles on objects (e.g. shares). For more details, see Step 5. Apply Global IDs to Objects Created by Scripts in Target Accounts — Optional.

Refresh a Secondary Group Manually¶

To manually refresh a secondary failover group in the target account, see Refreshing a Secondary Failover Group in a Target Account Manually (in this topic).

Enable Stream and Task Replication Preview — Optional¶

To enable the stream replication and task replication preview for an account, database, or replication or failover group, set the ENABLE_STREAM_TASK_REPLICATION parameter to true for the source account or primary object.

alter database mydb set ENABLE_STREAM_TASK_REPLICATION = true;

alter replication group myrg set ENABLE_STREAM_TASK_REPLICATION = true;

alter account myorg.myaccount set ENABLE_STREAM_TASK_REPLICATION = true;

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;

View all the databases in failover group myfg:

SHOW DATABASES IN FAILOVER GROUP myfg;

View all the shares in failover group myfg:

SHOW SHARES IN FAILOVER GROUP myfg;

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';

Create Replication or Failover Groups Without a Replication Schedule¶

When a secondary replication or failover group is created as a replica of a primary replication or failover group with a replication schedule, an initial refresh of the secondary group is automatically executed when the secondary replication or failover group is created. In order to prevent this from happening before you can execute the following instructions, create the primary replication or failover group without setting the REPLICATION_SCHEDULE parameter.

After the secondary group is created in the target account, use the SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME function to add global identifiers to account objects in the target account. For account objects that exist only in the target account, replicate them manually in the source account before calling this function.

After you apply global identifiers to objects in the target account, or recreate them manually in the source account, use the ALTER REPLICATION GROUP or ALTER FAILOVER GROUP command to set the replication schedule for the primary replication or failover group.

Use SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME() to Apply Global IDs¶

Apply global identifiers to account objects in the target account of the types included in the object_types list for failover group myfg:

-- Execute the following SQL statements using the ACCOUNTADMIN role:
SELECT SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME('myfg');

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';
   

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;
   

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);
   

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