Replicating databases and account objects across multiple accounts¶
This topic describes the steps necessary to replicate account objects and data across Snowflake accounts in the same organization, and keep the objects and data synchronized. Account replication can occur across Snowflake accounts in different regions and across cloud platforms.
Note
When you upgrade an account to Business Critical Edition (or higher), it might take up to 12 hours for failover capabilities to become available.
Region support for replication and failover/failback¶
Customers can replicate across all regions within a Region Group. To replicate between regions in different Region groups (for example, from a Snowflake commercial region to a Snowflake government region), please contact Snowflake Support to enable access.
Transitioning from database replication to group-based replication¶
Databases that have been enabled for replication using ALTER DATABASE must have replication disabled before they can be added to a replication or failover group.
Note
Execute the SQL statements in this section using the ACCOUNTADMIN role.
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.
Note
When you add a previously replicated database to a replication or failover group, Snowflake does not re-replicate the data that has already been replicated for that database. Only changes since the last refresh are replicated when the group is refreshed.
Workflow¶
The following SQL statements demonstrate the workflow for enabling account and database object replication and refreshing objects. Each step is discussed in detail below.
Note
The following examples require replication be enabled for the source and target accounts. For details, see Prerequisite: Enable replication for accounts in the organization.
Examples¶
Execute the following SQL statements in your preferred Snowflake client to enable account and database object replication and failover, and refresh objects.
Executed on source account¶
Create a role and grant it the CREATE FAILOVER GROUP privilege. This step is optional:
USE ROLE ACCOUNTADMIN; CREATE ROLE myrole; GRANT CREATE FAILOVER GROUP ON ACCOUNT TO ROLE myrole;
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; 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';
Executed on target account¶
Create a role in the target account and grant it the CREATE FAILOVER GROUP privilege. This step is optional:
USE ROLE ACCOUNTADMIN; CREATE ROLE myrole; GRANT CREATE FAILOVER GROUP ON ACCOUNT TO ROLE myrole;
Create a failover group in the target account as a replica of the failover group in the source account.
Note
If account objects (e.g. users or roles) exist in the target account that do not exist in the source account, refer to Initial replication of users and roles before creating a secondary group.
USE ROLE myrole; CREATE FAILOVER GROUP myfg AS REPLICA OF myorg.myaccount1.myfg;
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.
Create a role with the REPLICATE privilege on the failover group. This step is optional.
Execute in the target account using a role with the OWNERSHIP privilege on the failover group:
GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role;
Execute the refresh statement using a role with the REPLICATE privilege:
USE ROLE my_replication_role; ALTER FAILOVER GROUP myfg REFRESH;
Create a role with the FAILOVER privilege on the failover group. This step is optional.
Execute in the target account using a role with the OWNERSHIP privilege on the failover group:
GRANT FAILOVER ON FAILOVER GROUP myfg TO ROLE my_failover_role;;
Replicating account objects and databases¶
The instructions in this section explain how to prepare your accounts for replication, enable the replication of specific objects from the source account to the target account, and synchronize the objects in the target account.
Important
Target accounts do not have Tri-Secret Secure or private connectivity to the Snowflake service, such as AWS PrivateLink, enabled by default. If you require Tri-Secret Secure or private connectivity to the Snowflake service for compliance, security or other purposes, it is your responsibility to configure and enable those features in the target account.
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 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.
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 statement using the ACCOUNTADMIN role:
SHOW REPLICATION ACCOUNTS;
Returns:
+------------------+-------------------------------+--------------+-----------------+-----------------+-------------------+--------------+
| 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 |
+------------------+-------------------------------+--------------+-----------------+-----------------+-------------------+--------------+
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.
Note
Only an account administrator (user with the ACCOUNTADMIN role) or the group owner (role with the OWNERSHIP privilege on the group) can execute the SQL statements in this section.
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 replication from a source account to target account¶
You can create a replication or failover group using Snowsight or SQL.
Note
If you have databases to add to a replication or failover group that have been previously enabled for database replication using ALTER DATABASE, follow the Transitioning from database replication to group-based replication instructions (in this topic) before adding them to a group.
Create a replication or failover group using Snowsight¶
Note
Only account administrators can create a replication or failover group using Snowsight (refer to Limitations of using Snowsight for replication configuration).
You must be signed in to the target account as a user with the ACCOUNTADMIN role. If you are not, you will be prompted to sign in.
Both the source account and the target account must use the same connection type (public internet). Otherwise, signing in to the target account fails.
Complete the following steps to create a new replication or failover group:
Sign in to Snowsight and navigate to Admin » Accounts.
Select Replication, select Groups.
Select + Add Group.
Select Target Account, then select Next.
In the Group Name box, enter a name for the group 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.
Must be unique across failover and replication groups in an account.
Choose Select Objects to add share and account objects to your group.
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.
Choose Select Databases to add database objects to your group.
Select the Replication Frequency.
If the account is Business Critical Edition or higher, a failover group is created by default. You can choose to create a replication group instead. To create a replication group, select Advanced Options, then unselect Enable Failover.
Select Start Replication to create the replication group.
If creating the replication group is unsuccessful, refer to Troubleshoot issues with creating and editing replication groups using Snowsight for common errors and how to resolve them.
Create a failover group using SQL¶
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.
Execute the following statement on the source account:
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¶
Note
If account objects (e.g. users or roles) exist in the target account that do not exist in the source account, refer to Initial replication of users and roles before creating a secondary group.
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.
Note
If the user who calls the function in the target account was dropped in the source account, the refresh operation fails.
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.
Execute this statement from the source account using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role;Execute this statement from the target account using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role;
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; ALTER FAILOVER GROUP myfg REFRESH;
Step 6. Grant the FAILOVER privilege on failover group to role — Optional¶
To execute the command to fail over a secondary failover group in a target account, you must use a role with the FAILOVER privilege on the failover group. The FAILOVER privilege is currently not replicated and must be granted in each source and target account.
For more information, see Replication of roles and grants.
For example, to grant the FAILOVER privilege to role my_failover_role
on failover group my_fg
, execute the
following statement in the target account using a role with the OWNERSHIP privilege on the group:
GRANT FAILOVER ON FAILOVER GROUP myfg TO ROLE my_failover_role;
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Apply global IDs to objects created by scripts in target accounts¶
If you created account objects, for example, users and roles, in your target account by any means other than via replication (e.g. using scripts), these users and roles have no global identifier by default. The refresh operation uses global identifiers to synchronize these objects to the same objects in the source account.
In most cases, when a target account is refreshed from the source account, the refresh operation drops any account objects of the
types in the OBJECT_TYPES
list in the target account that have no global identifier. The initial replication of users and roles to
a target account, however, might cause the first refresh operation to fail. For details on this behavior, refer to
Initial replication of users and roles.
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.
Note
Global identifiers are only added to account objects that are included in a replication or failover group for the following object types:
RESOURCE_MONITOR
ROLE
USER
WAREHOUSE
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.
Note
The behavior described in this section applies only the first time these object types are replicated to the target account.
The scenarios below describe the replication of USERS. The same also applies to the replication of ROLES.
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;
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>');
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:
In the source account, manually recreate any users or roles that exist only in the target account before the initial replication.
In the target account, link matching objects with the same name in both accounts using the SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME function.
Configure cloud storage access for secondary storage integrations¶
If you enable storage integration replication, you must take additional steps after the storage integration is replicated to target accounts. The replicated integration has its own identity and access management (IAM) entity that is different from the identity and IAM entity of the primary integration. Therefore, you must update your cloud provider permissions to grant the replicated integration access to your cloud storage.
You only need to configure this trust relationship on target accounts one time.
The process is similar to granting access in the source account. See the following pages for more information:
Configure automated refresh for directory tables on secondary stages¶
If you replicate an external stage with a directory table, and you have configured automated refresh for the source directory table, you must take steps to configure automated refresh for the secondary directory table.
The process is similar to setting up automated refresh in your source account. See the following for more information:
Amazon S3: The configuration process depends on how you set up event notifications.
If you use Amazon S3 Event Notifications with Amazon Simple Queue Service (SQS), follow the instructions in Step 2: Configure event notifications. You can also migrate from SQS to SNS. For more information, see Migrate to Amazon Simple Notification Service (SNS).
If you use Amazon Simple Notification Service (SNS), see Subscribing the Snowflake SQS Queue to your SNS topic.
Google Cloud Storage: Create a new subscription to your Pub/Sub topic and a new notification integration in your target account. Then, grant Snowflake access to the Pub/Sub subscription. For instructions, see Configuring Automation Using GCS Pub/Sub.
Azure Blob Storage: Create a new Event Grid subscription and storage queue. Then, create a new notification integration in the target account and grant Snowflake access to your storage queue. For instructions, see Configuring Automation With Azure Event Grid.
Important
After you complete these configuration steps in your target account, you should perform a full refresh of your directory table to ensure that it has not missed any notifications.
For Google Cloud Storage and Azure Blob Storage, the name of the notification integration in each target account must match the name of the notification integration in the source account.
Configure notifications for secondary auto-ingest pipes¶
You must take additional steps to configure cloud notifications for secondary auto-ingest pipes before failover. This section covers why this additional configuration is required, and how to complete it for each supported cloud provider.
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.
You can also migrate from SQS to SNS. For more information, see Migrate to Amazon Simple Notification Service (SNS).
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:
Create a new Event Grid subscription and storage queue. Then, create a new notification integration in the target account and grant Snowflake access to your storage queue. For instructions, see Configuring Automation With Azure Event Grid.
Important
The name of the notification integration in each target account must match the name of the notification integration in the source account.
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:
- Create a new subscription to your Pub/Sub topic and a new notification integration in your target account.
Then, grant Snowflake access to the Pub/Sub subscription. For instructions, see Configuring Automation Using GCS Pub/Sub.
Important
The name of the notification integration in each target account must match the name of the notification integration in the source account.
Updating the remote service for API integrations¶
If you have enabled API integration replication, additional steps are required after the API integration is replicated to the target account. The replicated integration has its own identity and access management (IAM) entity that are different from the identity and IAM entity of the primary integration. Therefore, you must update the permissions on the remote service to grant access to replicated functions. The process is similar to granting access to the functions on the primary account. See the below links for more details:
Amazon Web Services Set up the trust relationship(s) between Snowflake and the new IAM role.
Google Cloud Platform: Create a GCP Security Policy for the Proxy Service.
Microsoft Azure:
Step 2. Create a validate-JWT policy
Monitoring replication¶
This section provides information on how to monitor account replication progress, history, and costs.
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.
Note
Snowsight lists the replication and failover groups for which your role has the MONITOR, OWNERSHIP, or REPLICATE privilege on.
Refresh operation details are only available to users with the ACCOUNTADMIN role or the OWNERSHIP privilege on the group.
You must be signed in to the target account to view refresh operation details. If you are not, you will be prompted to sign in.
Both the source account and the target account must use the same connection type (public internet). Otherwise, signing in to the target account fails.
To view the replication status of each replication or failover group, complete the following steps:
Sign in to Snowsight and navigate to Admin » Accounts.
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. Iffg_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:
Column |
Description |
---|---|
Name |
Name of the replication or failover group. |
Is Replicating |
Indicates if the group is being replicated to a target account or from a source account. If this column contains destinations available, there are no secondary replication or failover groups. The number of destinations available indicates the number of target accounts the primary group can be replicated to. |
Status |
Displays the status of the latest refresh operation. You must be signed in to the target account in order to access replication details. If you are not signed in, select Sign in to view refresh operation status for the secondary group. Both the source account and the target account must use the same connection type (public internet). Otherwise, signing in to the target account fails. |
Replication Lag |
The length of time since the last refresh operation. This is the length of time that the secondary replication group “lags” behind the primary replication group. |
Next Refresh |
The date and time of the next scheduled refresh operation. |
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.
Monitor the progress of refresh operations using Snowsight¶
You can view the status of a refresh operation in progress and the details of historical refresh operations using Snowsight.
Sign in to Snowsight and navigate to Admin » Accounts.
Select Replication, select Groups.
Select the name of a replication or failover group.
For more information about the detailed view, see the section on replication history in Snowsight.
Monitor the progress of refresh operations using SQL¶
To monitor the progress of a replication or failover group refresh, query the REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB table function (in the Snowflake Information Schema).
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'));
Viewing replication history¶
You can view replication history using Snowsight or using SQL.
Note
You can view the replication history for the replication and failover groups for which your role has the MONITOR, OWNERSHIP, or REPLICATE privilege on.
View replication history using Snowsight¶
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.
Sign in to Snowsight and navigate to Admin » Accounts.
Select Replication, select Groups.
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 |
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.
View replication history using SQL¶
To view the replication history of a specific replication or failover group within a specified date range, query one of the following:
REPLICATION_GROUP_REFRESH_HISTORY table function (in the Snowflake Information Schema).
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());
Monitor replication costs¶
To monitor credit usage for replication, query one of the following:
REPLICATION_GROUP_USAGE_HISTORY table function (in the Snowflake Information Schema).
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());
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.
Examples¶
Querying Account Usage views¶
The following examples calculate the costs for database replication in one replication group for the past 30 days.
Query the REPLICATION_GROUP_REFRESH_HISTORY Account Usage view and calculate the sum of the number of bytes replicated per database.
For example, to calculate the sum of the number of bytes replicated for databases in the replication group
myrg
in the last 30 days:select sum(value:totalBytesToReplicate) as sum_database_bytes from snowflake.account_usage.replication_group_refresh_history rh, lateral flatten(input => rh.total_bytes:databases) where rh.replication_group_name = 'MYRG' and rh.start_time >= current_date - interval '30 days';
Note the output of the sum of database bytes:
+--------------------+ | SUM_DATABASE_BYTES | |--------------------| | 22016 | +--------------------+
Query the REPLICATION_GROUP_USAGE_HISTORY Account Usage view and calculate the sum of the number of credits used and the sum of the bytes transferred for replication.
For example, to calculate the sum of the number of credits used and the sum of the bytes transferred for replication of the replication group
myrg
in the last 30 days:select sum(credits_used) as credits_used, SUM(bytes_transferred) as bytes_transferred from snowflake.account_usage.replication_group_usage_history where replication_group_name = 'MYRG' and start_time >= current_date - interval '30 days';
Note the output of the sum of the credits used and the sum of bytes transferred:
+--------------+-------------------+ | CREDITS_USED | BYTES_TRANSFERRED | |--------------+-------------------| | 1.357923604 | 22013 | +--------------+-------------------+
Calculate the replication costs for databases using the values of the bytes transferred for databases, sum of the credits used, and the sum of all bytes transferred for replication from the previous two steps:
(<database_bytes_transferred> / <bytes_transferred>) * <credits_used>
For example:
(22016 / 22013) * 1.357923604 = 1.35810866)
Querying Information Schema table functions¶
For refresh operations within the past 14 days, query the associated Information Schema table functions.
Query the REPLICATION_GROUP_REFRESH_HISTORY table function to view the sum of the number of bytes copied for database replication for the replication group
myrg
:select sum(value:totalBytesToReplicate) from table(information_schema.replication_group_refresh_history('myrg')) as rh, lateral flatten(input => total_bytes:databases) where rh.phase_name = 'COMPLETED' and rh.start_time >= current_date - interval '14 days';
Query the REPLICATION_GROUP_USAGE_HISTORY table function to view sum of the number of credits used and the sum of the bytes transferred for replication for the replication group
myrg
:select sum(credits_used), sum(bytes_transferred) from table(information_schema.replication_group_usage_history( date_range_start=>dateadd('day', -14, current_date()), replication_group_name => 'myrg' ));
Comparing data sets in primary and secondary databases¶
If database objects are replicated in a replication or failover group, the HASH_AGG function can be used to compare the rows in a random set of tables in a primary and secondary database to verify data consistency. The HASH_AGG function returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. Query this function on all or a random subset of tables in a secondary database and on the primary database (as of the timestamp for the primary database snapshot) and compare the output.
Example¶
In the examples below, the database mydb
is included in the failover group myfg
. The database mydb
contains the table
mytable
.
Executed on target account¶
Query the REPLICATION_GROUP_REFRESH_PROGRESS table function (in the Snowflake Information Schema). Note the
primarySnapshotTimestamp
in theDETAILS
column for thePRIMARY_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';
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¶
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);
Compare the results from the two queries. The output should be identical.
Modifying a replication or failover group¶
You can edit the name, included objects, and replication schedule of a replication or failover group using Snowsight or SQL.
Modify a replication or failover group using Snowsight¶
Note
Only account administrators can edit a replication or failover group using Snowsight (refer to Limitations of using Snowsight for replication configuration).
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.
Both the source account and the target account must use the same connection type (public internet). Otherwise, signing in to the target account fails.
Sign in to Snowsight and navigate to Admin » Accounts.
Select Replication, select Groups.
Locate the replication or failover group you want to edit. Select the More menu (…) in the last column of the row.
Select Edit.
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.
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.
Choose Select Databases to add or remove database objects.
Select the Replication Frequency to change the replication schedule for a group.
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.
Dropping a secondary replication or failover group¶
You can drop a secondary replication or failover using the DROP REPLICATION GROUP or the DROP FAILOVER GROUP command. Only the replication or failover group owner (i.e. the role with the OWNERSHIP privilege on the group) can drop the group.
To drop a secondary replication or failover group using Snowsight, you must drop the group in the source account. See Drop a replication or failover group using Snowsight.
Dropping a primary replication or failover group¶
You can drop a primary replication or failover group using Snowsight or SQL. If you are deleting a primary group using SQL, you must first drop all secondary groups. See Dropping a secondary replication or failover group.
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¶
Note
Only account administrators can delete a replication or failover group using Snowsight (refer to Limitations of using Snowsight for replication configuration).
You can delete a primary replication or failover group and any linked secondary groups.
Sign in to Snowsight and navigate to Admin » Accounts.
Select Replication, select Groups.
Locate the replication or failover group you want to delete. Select the More menu (…) in the last column of the row.
Select Drop, then select Drop Group.
Troubleshoot issues with creating and editing replication groups using Snowsight¶
The following scenarios can help you troubleshoot common issues that can occur when creating or editing replication or failover group using Snowsight.
You cannot add a database to a group¶
Error |
Database '<database_name>' is already configured to replicate to
account '<account_name>' by replication group '<group_name>'.
|
---|---|
Cause |
A database can only be in one replication or failover group. One of the databases you selected for the group is already included in another replication or failover group. |
Solution |
Choose Select Databases and unselect any database(s) that are already included in another group. |
Error |
Cannot directly add previously replicated object '<database_name>' to a
replication group. Please use the provided system functions to convert
this object first.
|
---|---|
Cause |
The database you want to add to a replication or failover group was previously configured for database replication. |
Solution |
Disable database replication for the database. See Transitioning from database replication to group-based replication. |
Limitations of using Snowsight for replication configuration¶
Only a user with the ACCOUNTADMIN role can create a replication or failover group using Snowsight. A user with a role with the CREATE REPLICATION GROUP or CREATE FAILOVER GROUP privilege can create a group using the respective SQL commands.
Only a user with the ACCOUNTADMIN role can edit or drop a replication or failover group using Snowsight. A user with a role with the OWNERSHIP privilege on a replication or failover group can edit and drop groups using the respective SQL commands.