ALTER REPLICATION GROUP¶

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> , ... ] ]
  [ ALLOWED_DATABASES = <db_name> [ , <db_name> , ... ] ]
  [ ALLOWED_SHARES = <share_name> [ , <share_name> , ... ] ]

ALTER REPLICATION GROUP [ IF EXISTS ] <name> SET
  OBJECT_TYPES = INTEGRATIONS [ , <object_type> , ... ]
  ALLOWED_INTEGRATION_TYPES = <integration_type_name> [ , <integration_type_name> ... ]

ALTER REPLICATION GROUP [ IF EXISTS ] <name> SET
  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 ] <name> REFRESH

ALTER REPLICATION GROUP [ IF EXISTS ] <name> SUSPEND [ IMMEDIATE ]

ALTER REPLICATION GROUP [ IF EXISTS ] <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 properties to set for the replication group (separated by blank spaces, commas, or new lines).

OBJECT_TYPES = object_type [ , object_type , ... ]

Reset the list of object types to replicate from the source account to target account(s).

Note

For database and share objects:

• If DATABASES or SHARES are included in the OBJECT_TYPES list, and remain in the OBJECT_TYPES list after the list is reset, the respective allowed objects list (ALLOWED_DATABASES or ALLOWED_SHARES) remains unchanged.

• If the OBJECT_TYPES list is reset to add or remove DATABASES, the ALLOWED_DATABASES list is set to NULL.

• If the OBJECT_TYPES list is reset to add or remove SHARES, the ALLOWED_SHARES list is set to NULL.

• Use the ADD, MOVE, and REMOVE clauses to modify the list of allowed database or share objects.

The following object types are supported:

ACCOUNT PARAMETERS:

Requires Business Critical Edition (or higher).

All account-level parameters. This includes account parameters and parameters that can be set for your account.

DATABASES:

Add database objects to the list of object types. If database objects were already included in the list of specified object types, the ALLOWED_DATABASES list remains unchanged. To modify the list of databases, use the ADD, MOVE, or REMOVE clauses.

INTEGRATIONS:

Requires Business Critical Edition (or higher).

Currently, only security, API, storage, external access [2], and certain types of notification integrations are supported. For details, see Integration replication.

If integration objects are included in the list of specified object types, the ALLOWED_INTEGRATION_TYPES parameter must be set.

NETWORK POLICIES:

Requires Business Critical Edition (or higher).

All network policies in the source account.

RESOURCE MONITORS:

Requires Business Critical Edition (or higher).

All resource monitors in the source account.

ROLES:

Requires Business Critical Edition (or higher).

All roles in the source account. Replicating roles implicitly includes all grants for object types included in the replication group. For example, if ROLES is the only object type that is replicated, then only hierarchies of roles (i.e. roles granted to other roles) are replicated to target accounts. If the USERS object type is also included, then role grants to users are also replicated.

SHARES:

Add share objects to the list of object types. If share objects were already included in the list of specified object types, the ALLOWED_SHARES list remains unchanged. To modify the list of shares, use the ADD, MOVE, or REMOVE clauses.

USERS:

Requires Business Critical Edition (or higher).

All users in the source account.

WAREHOUSES:

Requires Business Critical Edition (or higher).

All warehouses in the source account.

ALLOWED_DATABASES = db_name [ , db_name , ... ]

Specifies the database or list of databases for which you are enabling replication from the source account to the target account. In order for you to set this parameter, the OBJECT_TYPES list must include DATABASES.

db_name

Specifies the identifier for the database.

ALLOWED_SHARES = share_name [ , share_name , ... ]

Specifies the share or list of shares for which you are enabling replication from the source account to the target account. In order for you to set this parameter, the OBJECT_TYPES list must include SHARES.

share_name

Specifies the identifier for the share.

Note

If the ALLOWED_DATABASES or ALLOWED_SHARES lists are modified, any objects that were previously in the list and removed will be dropped in any target account with a linked secondary replication group when the next refresh operation occurs.

ALLOWED_INTEGRATION_TYPES = integration_type_name [ , integration_type_name , ... ]

Requires Business Critical Edition (or higher).

Type(s) of integrations for which you are enabling replication from the source account to the target account.

This property requires that the OBJECT_TYPES list include INTEGRATIONS to set this parameter.

The following integration types are supported:

SECURITY INTEGRATIONS:

Specifies security integrations.

This property requires that the OBJECT_TYPES list include ROLES.

API INTEGRATIONS:

Specifies API integrations.

API integration replication requires additional set up after the API integration is replicated to the target account. For more information, see Updating the remote service for API integrations.

STORAGE INTEGRATIONS:

Specifies storage integrations.

EXTERNAL ACCESS INTEGRATIONS:

Specifies external access integrations.

For more information, see Replication of stored procedures and user-defined functions (UDFs).

Note

External access is not supported in the Gov region.

NOTIFICATION INTEGRATIONS:

Specifies notification integrations.

Only some types of notification integrations are replicated. For details, see Integration replication.

REPLICATION_SCHEDULE ...

Specifies the schedule for refreshing secondary replication groups.

• USING CRON expr time_zone

  Specifies a cron expression and time zone for the secondary group refresh. Supports a subset of standard cron utility syntax.

  For a list of time zones, see the list of tz database time zones (in Wikipedia).

  The cron expression consists of the following fields:

  # __________ minute (0-59)
  # | ________ hour (0-23)
  # | | ______ day of month (1-31, or L)
  # | | | ____ month (1-12, JAN-DEC)
  # | | | | __ day of week (0-6, SUN-SAT, or L)
  # | | | | |
  # | | | | |
    * * * * *
  

  The following special characters are supported:

  *

  Wildcard. Specifies any occurrence of the field.

  L

  Stands for “last”. When used in the day-of-week field, it allows you to specify constructs such as “the last Friday” (“5L”) of a given month. In the day-of-month field, it specifies the last day of the month.

  /n

  Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if 4/3 is specified in the month field, then the refresh is scheduled for April, July and October (i.e. every 3 months, starting with the 4th month of the year). The same schedule is maintained in subsequent years. That is, the refresh is not scheduled to run in January (3 months after the October run).

  Note

  • The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the refresh.

  • The cron expression defines all valid run times for the refresh. Snowflake attempts to refresh secondary groups based on this schedule; however, any valid run time is skipped if a previous run has not completed before the next valid run time starts.

  • When both a specific day of month and day of week are included in the cron expression, then the refresh is scheduled on days satisfying either the day of month or day of week. For example, SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC' schedules a refresh at 0AM on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.

• num MINUTE

  Specifies an interval (in minutes) of wait time between refreshes. Accepts positive integers only.

  Also supports num M syntax.

  To avoid ambiguity, a base interval time is set:

  • When the object is created (using CREATE <object>) or

  • When a different interval is set (using ALTER <object> … SET REPLICATION_SCHEDULE)

  The base interval time starts the interval counter from the current clock time. For example, if an INTERVAL value of 10 is set and the scheduled refresh is enabled at 9:03 AM, then the refresh runs at 9:13 AM, 9:23 AM, and so on. Note that we make a best effort to ensure absolute precision, but only guarantee that refreshes do not execute before their set interval occurs (e.g. in the current example, the refresh could first run at 9:14 AM, but will definitely not run at 9:12 AM).

  Note

  The maximum supported value is 11520 (8 days). If the replication schedule has a greater num MINUTE value, the refresh operation never runs.

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.

Note

When you remove a database from a primary replication group, the database is dropped in any target account with a linked secondary replication group when the next refresh operation occurs.

To avoid dropping databases in the target account, you can drop the secondary replication group before the next time the modified primary replication group is replicated to the target account. When you drop the secondary replication group, read-only secondary databases that were included in the group become standalone read-write databases in the target account.

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.

Note

When you remove a share from a primary replication group, the share is dropped in any target account with a linked secondary replication group when the next refresh operation occurs.

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

name

Specifies the identifier for the replication group.

REFRESH

Refreshes the objects in the target (current) account from the source account.

SUSPEND [ IMMEDIATE ]

Suspend the scheduled refresh of the secondary replication group (if the primary replication group has automatically scheduled refresh set using the REPLICATION_SCHEDULE property).

The optional IMMEDIATE clause cancels an in-progress refresh operation for the secondary replication group (if there is one). Note that there might be a slight delay between the time that the statement returns and the time that the cancellation of the refresh operation is finished.

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, the active, primary 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 or shares) in the object_types list, it must be explicitly added to the move-to group before you move the objects.

• If database or share objects are removed from a primary replication group (by using the REMOVE parameter or SET parameter to modify the ALLOWED_DATABASES or ALLOWED_SHARES lists), those objects are dropped in any target account with a linked secondary replication group when the next refresh operation occurs.

  To avoid dropping these objects in the target account, you can drop the secondary replication group before the next time the modified primary replication group is replicated to the target account.

• Inbound shares (shares from providers) cannot be added to a replication or failover group.

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

ALTER REPLICATION GROUP myrg ADD myorg.myaccount3 TO ALLOWED_ACCOUNTS;

ALTER REPLICATION GROUP myrg SET
  OBJECT_TYPES = DATABASES, SHARES;

ALTER REPLICATION GROUP myrg
  ADD db1 to ALLOWED_DATABASES;

ALTER REPLICATION GROUP myrg
  ADD s2 TO ALLOWED_SHARES;

ALTER REPLICATION GROUP myrg
  MOVE DATABASES db1 TO REPLICATION GROUP myrg2;

ALTER REPLICATION GROUP myrg SET
  REPLICATION_SCHEDULE = '15 MINUTE';

ALTER REPLICATION GROUP myrg REFRESH;

ALTER REPLICATION GROUP myrg SUSPEND;