Replicating Account Objects¶

Step 1. Disable Replication for a Replication Enabled Database¶

Execute the SYSTEM$DISABLE_DATABASE_REPLICATION function to disable replication for a primary database, along with any secondary databases linked to it, in order to add it to a replication or failover group.

Execute the following SQL statement from the source account with the primary database:

SELECT SYSTEM$DISABLE_DATABASE_REPLICATION('mydb');

Step 2. Add the Database to a Primary Failover Group and Create a Secondary Failover Group¶

Once you have successfully disabled replication for a database, you can add the primary database to a failover group in the source account.

Then create a secondary failover group in the target account. When the secondary failover group is refreshed in the target account, the previously secondary database will automatically be added as a member of the secondary failover group and refreshed with the changes from the primary database.

For more details on creating primary and secondary failover groups, see Workflow.

Examples¶

Execute the following SQL statements in your preferred Snowflake client to enable account and database object replication and failover, and refresh objects.

Prerequisite: Enable Replication for Accounts in the Organization¶

The organization administrator (ORGADMIN role) must enable replication for the source and target accounts.

To enable replication for accounts, a user with the ORGADMIN role uses the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function to set the ENABLE_ACCOUNT_DATABASE_REPLICATION parameter to true. Note that multiple accounts in an organization can be enabled for replication from the same ORGADMIN account.

Log into an ORGADMIN account to enable replication for each source and target account in your organization.

USE ROLE ORGADMIN;

-- View the list of the accounts in your organization
-- Note the organization name and account name for each account for which you are enabling replication
SHOW ORGANIZATION ACCOUNTS;

-- Enable replication by executing this statement for each source and target account in your organization
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('<organization_name>.<account_name>', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

Though the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function supports the legacy account locator identifier, it causes unexpected results when an organization has multiple accounts that share the same locator (in different regions).

Step 1: Create a Role with the CREATE FAILOVER GROUP Privilege in the Source Account — Optional¶

Create a role and grant it the CREATE FAILOVER GROUP privilege. This step is optional. If you have already created this role, skip to Step 2: Create a Primary Failover Group in a Source Account.

USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE FAILOVER GROUP ON ACCOUNT
    TO ROLE myrole;

Step 2: Create a Primary Failover Group in a Source Account¶

Create a primary failover group and enable the replication and failover of specific objects from the current (source) account to one or more target accounts in the same organization.

SHOW REPLICATION ACCOUNTS;

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

USE ROLE myrole;

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

Step 3: Create a Role with the CREATE FAILOVER GROUP Privilege in the Target Account — Optional¶

Create a role in the target account and grant it the CREATE FAILOVER GROUP privilege. This step is optional. If you have already created this role, skip to Step 4: Create a Secondary Failover Group in the Target Account.

USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE FAILOVER GROUP ON ACCOUNT
    TO ROLE myrole;

Step 4: Create a Secondary Failover Group in the Target Account¶

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

Execute a CREATE FAILOVER GROUP … AS REPLICA OF statement in each target account for which you enabled replication in Step 2: Create a Primary Failover Group in a Source Account (in this topic).

Executed from each target account:

USE ROLE myrole;

CREATE FAILOVER GROUP myfg
  AS REPLICA OF myorg.myaccount1.myfg;

Step 5. Refresh a Secondary Failover Group in the Target Account Manually — Optional¶

To manually refresh the objects in a target account, execute the ALTER FAILOVER GROUP … REFRESH command.

As a best practice, we recommend scheduling your secondary refreshes by setting the REPLICATION_SCHEDULE parameter using CREATE FAILOVER GROUP or ALTER FAILOVER GROUP.

Use SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME() to Apply Global IDs¶

You can prevent the loss of some object types by linking matching objects with the same name in the source and target accounts. The SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME function adds a global identifier to account objects in the target account.

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 statement using the ACCOUNTADMIN role:

SELECT SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME('myfg');

Initial Replication of Users and Roles¶

The behavior of the initial refresh operation for USERS and ROLES object types can vary depending on whether or not there are matching objects with the same name in the target account.

• If there are existing users in the target account with the same name as users in the source account, the initial refresh operation fails and describes the two options you have to continue:

  • Force the refresh operation and allow any existing users in the target account to be dropped. The users in the source account will be replicated to the target account.

    To force a refresh for a group, use the FORCE parameter for the refresh command. For example, to force the refresh of a failover group, execute the following command:

    ALTER FAILOVER GROUP <fg_name> REFRESH FORCE;
    

    Copy

  • Link the account objects by name. The SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME function links users with the same name in both the target account and the source account. Users in the target account that are linked are not deleted.

    To link account objects by name, execute the following command:

    SELECT SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME('<rg_name>');
    

    Copy

    Note

    Any user in the target account that does not have a matching user in the source account with the same name is dropped.

• If there are no users in the target account with names matching users in the source account, the initial refresh operation in the target account drops all users. This can result in the following data and metadata loss:

  • If USERS are included in the OBJECT_TYPES list for a replication or failover group:

    • Worksheets are lost.

    • Query history is lost.

  • If USERS are included in the OBJECT_TYPES list, but ROLES is not:

    • Privilege grants to users are lost.

  • If ROLES are included in the OBJECT_TYPES list:

    • Privilege grants to share objects are lost.

To avoid dropping users or roles in the target account:

1. In the source account, manually recreate any users or roles that exist only in the target account before the initial replication.

2. In the target account, link matching objects with the same name in both accounts using the SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME function.

Amazon S3¶

The configuration process depends on how you set up event notifications. For example, suppose you have an auto-ingest pipe that relies on an Amazon Simple Notification Service (SNS) topic to publish messages about the Snowflake stage location.

When you replicate the pipe to a target account, Snowflake automatically creates a new Amazon Simple Queue Service (SQS) queue. You must subscribe this SQS queue for your target account to the SNS topic to get notifications about the stage location.

• If you use Amazon S3 Event Notifications with Amazon Simple Queue Service (SQS), follow the instructions in Step 4: Configure Event Notifications.

  Important

  To ensure that the pipe has not missed any notifications, you should refresh the pipe after switching to the new SQS queue.

• If you use Amazon Simple Notification Service (SNS), see Subscribing the Snowflake SQS Queue to your SNS topic.

• If you use Amazon EventBridge, see Option 3: Setting up Amazon EventBridge to Automate Snowpipe.

Microsoft Azure Blob Storage¶

A pipe that automatically loads data from files located on a stage in Microsoft Azure blob storage requires an Event Grid subscription, storage queue, and a notification integration bound to the storage queue. A secondary pipe in a target account needs a separate Event Grid, storage queue, and notification integration bound to the storage queue. The Event Grid in both source and target accounts must be configured as endpoints for the same Azure Storage source.

See the diagram below for configuration details:

Follow the detailed instructions in Configuring Automation With Azure Event Grid to configure an Event Grid subscription and notification integration.

External Stage for Google Cloud Storage¶

A pipe that automatically loads data from files located in Google Cloud Storage requires a Google Pub/Sub subscription and a notification integration that references that subscription. Each replicated pipe in a target account also requires a Google Pub/Sub subscription and a notification integration that references that subscription. The Pub/Sub subscription in each source and target account must be subscribed to the same Pub/Sub Topic that receives notifications from the Google Cloud Storage source.

See the diagram below for configuration details:

For detailed instructions on creating a Pub/Sub subscription and notification integration, see Configuring Automation Using GCS Pub/Sub.

Monitor Replication Using Snowsight¶

To monitor the replication progress and status for replication and failover groups in an organization, use the Replication page in Snowsight.

You can view the status and details of refresh operations:

• View the current status of the most recent refresh operation.

• View replica lag time (time since the last refresh operation).

• View the distribution of replica lag times across groups.

• View the date and time of the next scheduled refresh operation.

To view the replication status of each replication or failover group, complete the following steps:

1. Sign in to Snowsight and navigate to Admin » Accounts.

2. Select Replication and then select Groups.

The Groups page displays refresh operation details for all the groups for which your role has a privilege to view. You can use the tiles to filter the view.

• For example, if the Status tile indicates there are failed refresh operations, you can select the tile to investigate the group(s) with failures.

• The lag time in the Longest Replication lag tile refers to the duration of time since the last refresh operation. This is the length of time that the secondary replication or failover group lags behind the primary group. The longest lag time is the length of time since the oldest secondary replication group was last refreshed.

  For example, if you have three failover groups, fg_1, fg_2, fg_3, with independent replication schedules of 10 minutes, 2 hours, and 12 hours respectively, the longest lag time could be as long as 12 hours. If fg_3, however, was recently refreshed in the target account, its lag time resets to 0 and a different failover group could have a longer lag time.

• You can select an individual bar in the Group Lag Distribution tile to filter the results to an individual group.

You can also filter groups by using the search field or the dropdown menus:

• You can search by replication or failover group name using the (search) box.

• Choose Type to filter the results by replication or failover group.

• Choose Replicating to filter by primary (select To) or secondary groups (select From).

• Choose the (accounts) menu to filter the results by account name.

• Choose Status to filter results by refresh operation status:

  • Refresh Cancelled

  • Refresh Failed

  • Refresh In Progress

  • Refresh Successful

You can see the following details about your replication and failover groups:

You can select a replication or failover group to view detailed information about each refresh operation. For more information, see the section on replication history in Snowsight.

Monitor the Progress of Refresh Operations¶

This section provides information on how to monitor replication progress for a specific replication or failover group.

SELECT phase_name, start_time, end_time, progress, details
  FROM TABLE(INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_PROGRESS('myfg'));

Viewing Replication History¶

You can view replication history using Snowsight or using SQL.

View Replication History Using Snowsight¶

Preview Feature — Open

Available to accounts in all regions and cloud platforms. Currently this feature is not supported for accounts using private connectivity.

You can view the replication history and details for each refresh operation for a specific replication or failover group in the details page for the group.

1. Sign in to Snowsight and navigate to Admin » Accounts.

2. Select Replication, select Groups.

3. Select the name of a replication or failover group.

You can then review the following information about the group:

• Group type (replication group or failover group).

• Replication schedule (for example, every 10 minutes).

• Duration of each refresh operation.

• Replica lag time (length of time since last refresh operation).

• Date and time of the next scheduled refresh operation.

You can filter the data on the page by status and time period:

• Choose Status to filter results by refresh operation status:

  • Refresh Cancelled

  • Refresh Failed

  • Refresh In Progress

  • Refresh Successful

• Choose Duration to show refresh operation details for:

  • Last hour

  • Last 24 hours

  • Last 7 days

  • All

  Selecting All displays the last 14 days of refresh operations.

The details for each refresh operation include the following columns:

Column	Description
Query ID	Query ID of the refresh operation.
Status	Displays the status of the refresh operation. Valid values include Successful, Failed, In Progress.
Ended	Date and time the refresh operation ended.
Duration	The length of time the refresh operation took to complete. The duration period is broken down and color coded by replication phase. The width of each colored segment indicates the portion of the time spent in that phase. The image below is for reference only. This graph is available when you select the refresh operation for additional details.
Transferred	The number of bytes replicated.
Objects	The number of objects replicated.

Select a row to view additional details about a specific refresh operation including:

• Duration of each replication phase.

• Error message (for failed refresh operations).

• List of database objects replicated by type and number.

• Number of databases replicated and database names.

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

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

Monitor Replication Costs¶

To monitor credit usage for replication, query one of the following:

• REPLICATION_GROUP_USAGE_HISTORY View (in Account Usage).

• REPLICATION_GROUP_USAGE_HISTORY table function (in the Snowflake Information Schema).

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

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

Monitor Replication Costs for Databases¶

The cost for replication for an individual database included in a replication or failover group can be calculated by retrieving the number of copied bytes for the database and associating it with the credits used.

Example¶

In the examples below, the database mydb is included in the failover group myfg. The database mydb contains the table mytable.

Modify a Replication or Failover Group Using Snowsight¶

To edit the name of the group, you must be signed in to the target account. If you are not signed in, the Status column displays a sign in message instead of the refresh status.

1. Sign in to Snowsight and navigate to Admin » Accounts.

2. Select Replication, select Groups.

3. Locate the replication or failover group you want to edit. Select the More menu (…) in the last column of the row.

4. Select Edit.

5. To change the group name, enter a new name in the Group Name box that meets the following requirements:

   • Must start with an alphabetic character and cannot contain spaces or special characters unless the identifier string is enclosed in double quotes (e.g. “My object”). Identifiers enclosed in double quotes are also case-sensitive.

     For more information, see Identifier requirements.

   • Names for failover groups and replication groups in an account must be unique.

6. Choose Select Objects to add or remove share and account objects.

   Note

   Account objects can only be added to one replication or failover group. If a replication or failover group with any account objects already exists in your account, you cannot select those objects.

7. Choose Select Databases to add or remove database objects.

8. Select the Replication Frequency to change the replication schedule for a group.

9. Select Save Changes to update the group.

   If saving the changes to the group is unsuccessful, refer to Troubleshoot Issues with Creating and Editing Replication Groups using Snowsight for common errors and how to resolve them.

Modify a Replication or Failover Group Using SQL¶

You can modify a replication or failover group properties using the ALTER REPLICATION GROUP or ALTER FAILOVER GROUP command.

Drop a Primary Replication or Failover Group Using SQL¶

A primary replication or failover group can only be dropped after all the replicas of the group (i.e. secondary replication or failover groups) have been dropped. Alternatively, you can promote a secondary failover group to serve as the primary failover group, then drop the former primary failover group.

Note that only the group owner can drop the group.

Drop a Replication or Failover Group Using Snowsight¶

You can delete a primary replication or failover group and any linked secondary groups.

1. Sign in to Snowsight and navigate to Admin » Accounts.

2. Select Replication, select Groups.

3. Locate the replication or failover group you want to delete. Select the More menu (…) in the last column of the row.

4. Select Drop, then select Drop Group.

You cannot add a database to a group¶

Database '<database_name>' is already configured to replicate to
account '<account_name>' by replication group '<group_name>'.

Cannot directly add previously replicated object '<database_name>' to a
replication group. Please use the provided system functions to convert
this object first.

You cannot add a share to a group¶

Share '<share_name>' is already configured to replicate to
account '<account_name>' by replication group '<group_name>'.