Categories:

Account & Session DDL

CREATE FAILOVER GROUP

Creates a new failover group of specified objects in the system.

For more details see Replicating Account Objects Across Multiple Accounts

This command can be used to:

  • Create a failover group in the source account to enable replication and failover of specified objects to a target account in the same organization.

  • Create a secondary failover group in a target account as a replica of the primary failover group in the source account in the same organization.

See also:

ALTER FAILOVER GROUP , DROP FAILOVER GROUP , SHOW FAILOVER GROUPS

In this Topic:

Syntax

CREATE FAILOVER GROUP [ IF NOT EXISTS ] <name>
    OBJECT_TYPES = <object_type> [ , <object_type> , ... ]
    [ ALLOWED_DATABASES = <db_name> [ , <db_name> , ... ] ]
    [ ALLOWED_SHARES = <share_name> [ , <share_name> , ... ] ]
    [ ALLOWED_INTEGRATION_TYPES = <integration_type_name> [ , <integration_type_name> , ... ] ]
    ALLOWED_ACCOUNTS = <org_name>.<target_account_name> [ , <org_name>.<target_account_name> ,  ... ]
    [ IGNORE EDITION CHECK ]
    [ REPLICATION_SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]

Secondary Replication Group

CREATE FAILOVER GROUP [ IF NOT EXISTS ] <secondary_name>
    AS REPLICA OF <org_name>.<source_account_name>.<name>

Parameters

name

Specifies the identifier for the failover group. The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

OBJECT_TYPES = object_type [ , object_type , ... ]

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

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 are included in the list of specified object types, the ALLOWED_DATABASES parameter must be set.

INTEGRATIONS

Currently, only security and API integrations are supported.

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 are included in the list of specified object types, the ALLOWED_SHARES parameter must be set.

USERS

All users in the source account.

WAREHOUSES

All warehouses in the source account.

To modify the list of replicated object types to a specified target account, use ALTER FAILOVER GROUP to reset the list of object types.

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. The OBJECT_TYPES list must include DATABASES to set this parameter.

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. The OBJECT_TYPES list must include SHARES to set this parameter.

share_name

Specifies the identifier for the share.

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

Speficies API integrations.

API integration replication requires additional set up after the API integration is replicated to the target account. For details, see Update the Remote Service for API Integrations.

ALLOWED_ACCOUNTS = org_name.target_account_name [ , org_name.target_account_name , ... ]

Specifies the target account or list of target accounts to which replication and failover of specified objects from the source account is enabled. 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.

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.

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

Secondary Failover Group Parameters

secondary_name

Specifies the identifier for the secondary failover group. The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive. For more details, see Identifier Requirements.

The identifiers for the secondary failover group (secondary_name) and primary failover group (name) can be, but are not required to be, identical.

AS REPLICA OF org_name.source_account_name.name

Specifies the identifier of the primary failover group from which to create a secondary failover group.

org_name

Name of your Snowflake organization.

source_account_name

Source account from which you are enabling replication and failover of the specified objects.

name

Identifier for the primary failover group in the source account.

Access Control Requirements

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE FAILOVER GROUP

Account

Only the ACCOUNTADMIN role has this privilege by default. The privilege can be granted to additional roles as needed.

MONITOR

Database

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

OWNERSHIP

Share

To add a share to a failover group, the active role must have the OWNERSHIP privilege on the share.

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Access Control in Snowflake.

Usage Notes

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

  • To retrieve the set of accounts in your organization that are enabled for replication, use SHOW REPLICATION ACCOUNTS.

  • 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 on Source Account

Create a failover group named myfg in the source account and enable replication of users, roles, warehouses, resource monitors, database db1, and share s1, and failover from the source account to the myaccount2 account:

CREATE FAILOVER GROUP myfg
    OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES, SHARES, INTEGRATIONS, NETWORK POLICIES
    ALLOWED_DATABASES = db1
    ALLOWED_SHARES = s1
    ALLOWED_INTEGRATION_TYPES = SECURITY INTEGRATIONS
    ALLOWED_ACCOUNTS = myorg.myaccount2
    REPLICATION_SCHEDULE = '10 MINUTE';

Executed on Target Account

Create a failover group in the target account as a replica of the failover group myfg in the source account:

CREATE FAILOVER GROUP myfg
    AS REPLICA OF myorg.myaccount1.myfg;
Back to top