ALTER FAILOVER GROUP¶

Syntax¶

Source Account

ALTER FAILOVER GROUP [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER FAILOVER GROUP [ IF EXISTS ] <name> SET
  [ OBJECT_TYPES = <object_type> [ , <object_type> , ... ] ]
  [ ALLOWED_DATABASES = <db_name> [ , <db_name> , ... ] ]
  [ ALLOWED_SHARES = <share_name> [ , <share_name> , ... ] ]

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

ALTER FAILOVER GROUP [ IF EXISTS ] <name> SET
  REPLICATION_SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }'

ALTER FAILOVER GROUP [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  ADD <db_name> [ , <db_name> ,  ... ] TO ALLOWED_DATABASES

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  MOVE DATABASES <db_name> [ , <db_name> ,  ... ] TO FAILOVER GROUP <move_to_fg_name>

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  REMOVE <db_name> [ , <db_name> ,  ... ] FROM ALLOWED_DATABASES

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  ADD <share_name> [ , <share_name> ,  ... ] TO ALLOWED_SHARES

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  MOVE SHARES <share_name> [ , <share_name> ,  ... ] TO FAILOVER GROUP <move_to_fg_name>

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  REMOVE <share_name> [ , <share_name> ,  ... ] FROM ALLOWED_SHARES

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  ADD <org_name>.<target_account_name> [ , <org_name>.<target_account_name> ,  ... ] TO ALLOWED_ACCOUNTS
  [ IGNORE EDITION CHECK ]

ALTER FAILOVER GROUP [ IF EXISTS ] <name>
  REMOVE <org_name>.<target_account_name> [ , <org_name>.<target_account_name> ,  ... ] FROM ALLOWED_ACCOUNTS

Target Account

ALTER FAILOVER GROUP [ IF EXISTS ] <name> REFRESH

ALTER FAILOVER GROUP [ IF EXISTS ] <name> PRIMARY

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

ALTER FAILOVER GROUP [ IF EXISTS ] <name> RESUME

Parameters¶

Source Account

name

Specifies the identifier for the failover group.

RENAME TO new_name

new_name

Specifies the new identifier for the failover 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 failover group (separated by blank spaces, commas, or new lines).

OBJECT_TYPES = object_type [ , object_type , ... ]

Reset the list of object types for which you are enabling replication and failover 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:

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:

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:

All network policies in the source account.

RESOURCE MONITORS:

All resource monitors in the source account.

ROLES:

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:

All users in the source account.

WAREHOUSES:

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 and failover 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 and failover 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 failover group when the next refresh operation occurs.

ALLOWED_INTEGRATION_TYPES = integration_type_name [ , integration_type_name , ... ]

Type(s) of integrations for which you are enabling replication and failover 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 Azure 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 failover 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.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

ADD db_name [ , db_name ,  ... ] TO ALLOWED_DATABASES

Specifies a comma-separated list of additional databases to enable for replication and failover. To add databases, 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 FAILOVER GROUP move_to_fg_name

Specifies a comma-separated list of databases to move from one failover group to another failover group. The failover 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_fg_name

Specifies the identifier for the failover group the databases are being moved to.

REMOVE db_name [ , db_name ,  ... ] FROM ALLOWED_DATABASES

Specifies a comma-separated list of databases to remove from the list of databases enabled for replication and failover.

Note

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

To avoid dropping databases in the target account, you can drop the secondary failover group before the next time the modified primary failover group is replicated to the target account. When you drop the secondary failover 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 additional shares to enable for replication and failover. To add shares, 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 FAILOVER GROUP move_to_fg_name

Specifies a comma-separated list of shares to move from one failover group to another failover group. The failover 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_fg_name

Specifies the identifier for the failover 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 and failover.

Note

When you remove a share from a primary failover group, the share is dropped in any target account with a secondary failover 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 failover group to enable replication and failover of specified objects in the source account to the target account. Secondary failover groups in the target accounts in this list can be promoted to serve as the primary failover group in case of failover.

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 failover group to disable replication of specified objects in the source account to the target account. Removing a target account disables failover from the current account to this 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 in the following scenario:

The primary failover 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. 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.

This scenario is prohibited by default.

Target Account

name

Specifies the identifier for the failover group.

REFRESH

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

PRIMARY

Promote a secondary failover group and its specified objects in the target (current) account to primary (in case of failover).

SUSPEND [ IMMEDIATE ]

Suspend the scheduled refresh of the secondary failover group (if the primary failover group has scheduled refreshes using the REPLICATION_SCHEDULE property).

The optional IMMEDIATE keyword cancels a scheduled refresh operation that is currently in progress for the secondary failover 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 failover group (if the primary failover group has scheduled refreshes using the REPLICATION_SCHEDULE property).

Usage notes¶

• The following minimal privileges are required:

  • To refresh a secondary failover group using ALTER FAILOVER GROUP … REFRESH, the active, primary role must have either the OWNERSHIP or REPLICATE privilege on the failover group.

  • To fail over a secondary failover group using ALTER FAILOVER GROUP … PRIMARY, a role must have either the OWNERSHIP or FAILOVER privilege on the failover group.

  • To make any other changes to the failover group, only a role with the OWNERSHIP privilege on the group can execute this SQL command.

  • To add a database to a failover group, the active role must have the MONITOR privilege on the database.

  • To add a share to a failover 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 failover group.

• A database can only be added to one failover group.

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

• Promoting a secondary failover group to primary (in case of failover) fails if a refresh is in progress.

• If a refresh is in progress when the replication schedule is updated, the refresh continues until completion and the next refresh will use the new schedule.

• On failover, scheduled refreshes on all secondary failover groups are suspended. ALTER FAILOVER GROUP ... RESUME must be executed on each secondary to resume automatic refreshes.

• To move databases or shares from one failover group (the move-from group) to another failover group (the move-to group):

  • Both groups must be of the same type: FAILOVER 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 failover 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 when the next refresh operation occurs.

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

• 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 failover groups in your organization, use SHOW FAILOVER GROUPS.

• Automatically scheduled refresh operations are executed using the role with the OWNERSHIP privilege on the group. If a scheduled refresh operation fails due to insufficient privileges, grant the required privileges to the role with the OWNERSHIP privilege on the group.

• The ALTER FAILOVER GROUP … SUSPEND IMMEDIATE command doesn’t cancel an in-progress refresh operation if it was manually triggered. For information, see Cancel an in-progress refresh operation that wasn’t automatically scheduled.

• Canceling an in-progress refresh operation that is in the SECONDARY_DOWNLOADING_METADATA or SECONDARY_DOWNLOADING_DATA phase might result in an inconsistent state on the target account. For more information see View the current phase of an in-progress refresh operation.

• If you create a replication or failover group with a tag or modify a replication or failover group by setting a tag on it, tag lineage does not apply to any objects that you specify in the replication or failover group.

  Tag lineage is only applicable to objects with a parent-child relationship, such database, schema, and table. There are no child objects of replication or failover groups.

• You cannot set a tag or modify a tag on a secondary replication or failover group because these objects are read only.

• When you refresh a secondary replication or failover group, any tags that are set on the primary group are then set on the secondary group.

• 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 FAILOVER GROUP myfg ADD myorg.myaccount3 TO ALLOWED_ACCOUNTS;

ALTER FAILOVER GROUP myfg SET
  OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES
  ALLOWED_DATABASES = db1;

ALTER FAILOVER GROUP myfg
  ADD db2, db3 TO ALLOWED_DATABASES;

ALTER FAILOVER GROUP myfg
  MOVE DATABASES db3 TO FAILOVER GROUP myfg2;

ALTER FAILOVER GROUP myfg
  SET ALLOWED_DATABASES = NULL;

ALTER FAILOVER GROUP myfg
  REMOVE databases FROM OBJECT_TYPES;

ALTER FAILOVER GROUP myfg
  SET REPLICATION_SCHEDULE = '15 MINUTE';

ALTER FAILOVER GROUP myfg REFRESH;

ALTER FAILOVER GROUP myfg PRIMARY;

ALTER FAILOVER GROUP myfg SUSPEND;