Categories:

Account & Session DDL

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

  • 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.

See also:

CREATE REPLICATION GROUP , DROP REPLICATION GROUP , SHOW REPLICATION GROUPS

In this Topic:

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:

  • The primary replication group is in a Business Critical (or higher) account but one or more of the accounts approved for replication are on lower editions. Business Critical Edition is intended for Snowflake accounts with extremely sensitive data.

  • The primary replication group 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, but 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.

    • Both groups must have the same accounts listed in the allowed_accounts list.

    • 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 or shares) in the object_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;
Back to top