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
Certain types of notification integrations (refer to Integration Replication)
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
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
orshares
) in theobject_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:
First add
databases
toobject_types
:ALTER FAILOVER GROUP myfg3 SET OBJECT_TYPES = DATABASES, SHARES;Move
db2
tomyfg3
: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;