Categories:

Account & Session DDL

ALTER FAILOVER GROUP

Modifies the properties for an existing failover group.

From the source account, you can perform the following actions:

  • Rename the failover group.

  • Reset the list of specified object types enabled for replication and failover.

  • Set or update the replication schedule for automatic refresh of secondary failover groups.

  • Add or remove account objects of the following types to or from a failover group:

    • Databases

    • Shares

    • Security integrations

    • API integrations

  • Add or remove target accounts enabled for replication and failover.

  • Move shares or databases to another failover group.

From the target account, you can perform the following actions:

  • Refresh objects in the target account from the source account.

  • Promote a secondary failover group to primary (i.e. fail over the failover group of objects).

  • Suspend scheduled replication.

  • Resume scheduled replication.

See also:

CREATE FAILOVER GROUP , DROP FAILOVER GROUP , SHOW FAILOVER GROUPS

In this Topic:

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 on lower editions in either of the following scenarios:

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

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

Executed from the source account

Add myorg.myaccount3 to the list of target accounts to which replication of specified objects and failover from the source account is enabled.

ALTER FAILOVER GROUP myfg ADD myorg.myaccount3 TO ALLOWED_ACCOUNTS;

Reset the object types list for replication in the source account and add database db1:

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

Add databases db2 and db3 to the list of databases:

ALTER FAILOVER GROUP myfg
  ADD db2, db3 TO ALLOWED_DATABASES;

Move database db3 to another failover group, myfg2:

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

Move database db2 in myfg to another failover group, myfg3, that currently has no databases:

  1. First add databases to object_types:

    ALTER FAILOVER GROUP myfg3 SET
      OBJECT_TYPES = DATABASES, SHARES;
    
  2. Move db2 to myfg3:

    ALTER FAILOVER GROUP myfg
      MOVE DATABASES db2 TO FAILOVER GROUP myfg3;
    

Remove all databases from the list of databases in the source account for replication and failover:

ALTER FAILOVER GROUP myfg
  SET ALLOWED_DATABASES = NULL;

Note

Executing the statement above removes all databases from the list of databases to be replicated, but does not remove database objects from the list of specified object types for replication and failover.

To disable replication and failover of all databases and remove databases from the list of specified object types:

ALTER FAILOVER GROUP myfg
  REMOVE databases FROM OBJECT_TYPES;

Add (or modify) the interval for automatically scheduled refreshes:

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

Executed from the target account

Refresh objects in the failover group myfg in the target account:

ALTER FAILOVER GROUP myfg REFRESH;

Promote the secondary failover group in the current target account to primary:

ALTER FAILOVER GROUP myfg PRIMARY;

Suspend automatic refreshes:

ALTER FAILOVER GROUP myfg SUSPEND;
Back to top