ALTER REPLICATION GROUP¶
Modifies the properties for an existing replication group.
From the source account, you can perform the following actions:
Rename the replication group.
Reset the list of specified object types enabled for replication.
Set or update the replication schedule for automatic refresh of secondary replication groups.
Add or remove account objects of the following types to or from a replication group:
Databases
Shares
Security integrations
API integrations
Certain types of notification integrations (refer to Integration Replication)
Add or remove target accounts enabled for replication.
Move databases or shares from one replication group to another replication group.
From the target account, you can perform the following actions:
Refresh objects in the target account from the source account.
Suspend scheduled replication.
Resume scheduled replication.
Syntax¶
Source Account
ALTER REPLICATION GROUP [ IF EXISTS ] <name> RENAME TO <new_name>
ALTER REPLICATION GROUP [ IF EXISTS ] <name> SET
[ OBJECT_TYPES = <object_type> [ , <object_type> , ... ] ]
[ REPLICATION_SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
ALTER REPLICATION GROUP [ IF EXISTS ] <name> SET
OBJECT_TYPES = INTEGRATIONS [ , <object_type> , ... ] ]
ALLOWED_INTEGRATION_TYPES = <integration_type_name> [ , <integration_type_name> ... ] ]
[ REPLICATION_SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
ADD <db_name> [ , <db_name> , ... ] TO ALLOWED_DATABASES
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
MOVE DATABASES <db_name> [ , <db_name> , ... ] TO REPLICATION GROUP <move_to_rg_name>
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
REMOVE <db_name> [ , <db_name> , ... ] FROM ALLOWED_DATABASES
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
ADD <share_name> [ , <share_name> , ... ] TO ALLOWED_SHARES
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
MOVE SHARES <share_name> [ , <share_name> , ... ] TO REPLICATION GROUP <move_to_rg_name>
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
REMOVE <share_name> [ , <share_name> , ... ] FROM ALLOWED_SHARES
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
ADD <org_name>.<target_account_name> [ , <org_name>.<target_account_name> , ... ] TO ALLOWED_ACCOUNTS
[ IGNORE EDITION CHECK ]
ALTER REPLICATION GROUP [ IF EXISTS ] <name>
REMOVE <org_name>.<target_account_name> [ , <org_name>.<target_account_name> , ... ] FROM ALLOWED_ACCOUNTS
Target Account
ALTER REPLICATION GROUP [ IF EXISTS ] <secondary_name> REFRESH
ALTER REPLICATION GROUP [ IF EXISTS ] <secondary_name> SUSPEND
ALTER REPLICATION GROUP [ IF EXISTS ] <secondary_name> RESUME
Parameters¶
Source Account
name
Specifies the identifier for the replication group.
RENAME TO new_name
new_name
Specifies the new identifier for the replication group. The new identifier cannot be used if the identifier is already in place for a different replication or failover group.
For more details, see Identifier Requirements.
SET ...
Specifies one (or more) properties to set for the replication group (separated by blank spaces, commas, or new lines). For more details about the properties you can set, see CREATE REPLICATION GROUP.
ADD db_name [ , db_name , ... ] TO ALLOWED_DATABASES
Specifies a comma-separated list of databases to add to the list of databases enabled for replication. To add a database, DATABASES must be included in the list of specified object types. If the list of object types does not already include DATABASES, you must add it.
db_name
Specifies the identifier for the database.
MOVE DATABASES db_name [ , db_name , ... ] TO REPLICATION GROUP move_to_rg_name
Specifies a comma-separated list of databases to move from one replication group to another replication group. The replication group the databases are being moved to must include DATABASES in the list of specified object types.
db_name
Specifies the identifier for the database.
move_to_rg_name
Specifies the identifier for the replication group the databases are being moved to.
REMOVE db_name [ , db_name , ... ] FROM ALLOWED_DATABASES
Specifies a comma-separated list of database to remove from the list of databases enabled for replication.
ADD share_name [ , share_name , ... ] TO ALLOWED_SHARES
Specifies a comma-separated list of shares to the list of shares for replication. To add a share, SHARES must be included in the list of specified object types. If the list of object types does not already include SHARES, you must add it.
share_name
Specifies the identifier for the share.
MOVE SHARES share_name [ , share_name , ... ] TO REPLICATION GROUP move_to_rg_name
Specifies a comma-separated list of shares to move from one replication group to another replication group. The replication group the shares are being moved to must include SHARES in the list of specified object types.
share_name
Specifies the identifier for the share.
move_to_rg_name
Specifies the identifier for the replication group the shares are being moved to.
REMOVE share_name [ , share_name , ... ] FROM ALLOWED_SHARES
Specifies a comma-separated list of shares to remove from the list of shares enabled for replication.
ADD org_name.target_account_name [ , org_name.target_account_name , ... ] TO ALLOWED_ACCOUNTS
Specifies a comma-separated list of target accounts to add to the primary replication group to enable replication of specified objects in the source account to the target account.
org_name
Name of your Snowflake organization.
target_account_name
Target account to which you are enabling replication of the specified objects.
REMOVE org_name.target_account_name [ , org_name.target_account_name , ... ] FROM ALLOWED_ACCOUNTS
Specifies a comma-separated list of target accounts to remove from the primary replication group to disable replication of specified objects in the source account to the target account.
org_name
Name of your Snowflake organization.
target_account_name
Target account to which you are disabling replication of the specified objects.
IGNORE EDITION CHECK
Allows replicating objects to accounts on lower editions in either of the following scenarios:
A primary replication group with only database and/or share objects is in a Business Critical (or higher) account but one or more accounts approved for replication are on lower editions. Business Critical Edition is intended for Snowflake accounts with extremely sensitive data.
A primary replication group with any object type is in a Business Critical (or higher) account and a signed business associate agreement is in place to store PHI data in the account per HIPAA and HITRUST regulations. However, no such agreement is in place for one or more of the accounts approved for replication, regardless if they are Business Critical (or higher) accounts.
Both scenarios are prohibited by default in an effort to help prevent account administrators for Business Critical (or higher) accounts from inadvertently replicating sensitive data to accounts on lower editions.
Target Account
secondary_name
Specifies the identifier for the replication group.
REFRESH
Refreshes the objects in the target (current) account from the source account.
SUSPEND
Suspend the scheduled refresh of the secondary replication group (if the primary replication group has automatically scheduled refresh set using the
REPLICATION_SCHEDULE
property).Note
Suspending scheduled replication does not suspend a refresh operation that is currently in progress. It suspends the schedule so that no additional refreshes are scheduled after the current one is completed.
RESUME
Resume scheduled refresh of the secondary replication group (if the primary replication group has automatically scheduled refresh set using the
REPLICATION_SCHEDULE
property).
Usage Notes¶
The following minimal privileges are required:
To refresh a secondary replication group using ALTER REPLICATION GROUP … REFRESH, a role must have either the OWNERSHIP or REPLICATE privilege on the replication group.
To make any other changes to the replication group, only a user with a role with the OWNERSHIP privilege on the group can execute this SQL command.
To add a database to a replication group, the active role must have the MONITOR privilege on the database.
To add a share to a replication group, the active role must have the OWNERSHIP privilege on the share.
Identifiers for failover groups and replication groups in an account must be unique.
Objects other than databases and shares must be in the same replication group.
A database can only be added to one replication or failover group.
To move databases or shares from one replication group (the move-from group) to another replication group (the move-to group):
Both groups must be of the same type: REPLICATION GROUP.
If the last database in the move-from group is moved to another group, the
allowed_databases
property for the move-from group is set to NULL. The same behavior applies to shares.If the move-to group does not have the object type that is being moved (
databases
orshares
) in theobject_types
list, it must be explicitly added to the move-to group before you move the objects.
To retrieve the list of accounts in your organization that are enabled for replication, use the SHOW REPLICATION ACCOUNTS command.
To retrieve the list of replication groups in your organization, use the SHOW REPLICATION GROUPS command. The
allowed_accounts
column lists all target accounts enabled for replication from a source account.Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.
Examples¶
Executed from the source account¶
Add myorg.myaccount3
to the list of target accounts to which replication of specified objects from the source account is enabled:
ALTER REPLICATION GROUP myrg ADD myorg.myaccount3 TO ALLOWED_ACCOUNTS;
Reset the object types list for replication in the source account:
ALTER REPLICATION GROUP myrg SET
OBJECT_TYPES = DATABASES, SHARES;
Add database db1
to the list of databases enabled for replication:
ALTER REPLICATION GROUP myrg
ADD db1 to ALLOWED_DATABASES;
Add share s2
to the list of shares enabled for replication:
ALTER REPLICATION GROUP myrg
ADD s2 TO ALLOWED_SHARES;
Move database db1
to another replication group, myrg2
:
ALTER REPLICATION GROUP myrg
MOVE DATABASES db1 TO REPLICATION GROUP myrg2;
Set the scheduled refresh interval time to 15 minutes:
ALTER REPLICATION GROUP myrg SET
REPLICATION_SCHEDULE = '15 MINUTE';
Executed from the target account¶
Refresh objects in the replication group myrg
in the target account:
ALTER REPLICATION GROUP myrg REFRESH;
Suspend automatic refreshes:
ALTER REPLICATION GROUP myrg SUSPEND;