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> , ... ] ]
  [ REPLICATION_SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]

ALTER FAILOVER 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 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

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 one (or more) properties to set for the failover group (separated by blank spaces, commas, or new lines). For more details about the properties you can set, see CREATE FAILOVER GROUP.

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.

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.

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

secondary_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

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

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, a 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.

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

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

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