- Aug 29, 2022
- 5 min read

Business Continuity Plan

Updated: Mar 15

Author: Abhinav Shet

Introduction

Business continuity planning (BCP) is the process that involves creating a system of prevention and recovery from potential threats to a corporation. The plan ensures that personnel and assets are protected and are able to function quickly in the event of a disaster.

In the event of a large outage (due to a network issue, software bug, etc.) that disrupts the cloud services in a very given region, access to Snowflake is unavailable until the source of the outage is resolved, and services are restored. To ensure continued availability and data durability in such a scenario, replicate your critical databases to a different Snowflake account in your organization in a different region.

Database Replication

Replication involves writing or copying identical data to different locations. For instance, data will be copied between two on-premises hosts, between hosts in other locations, to multiple storage devices on the identical host, or to or from a cloud-based host. Data may be copied on demand, transferred in bulk or batches in line with a schedule, or replicated in real time because the data is written, changed, or deleted within the master source.

Whenever a primary database/account is replicated, a snapshot of its database objects and data is transferred to the secondary database/account. However, some database objects don't seem to be replicated.

Created an account in the AWS_US_EAST_2 region.
Under this account created a database to be replicated with the name “EMP_details” and created a table “EMP” and loaded it with some data.

Create database EMP_details;

Create schema EMP_basic;

Create table EMP ( EMP_ID INTEGER NOT NULL, FIRST_NAME VARCHAR NOT NULL, LAST_NAME VARCHAR NOT NULL, EMAIL VARCHAR NOT NULL);

In Orgadmin, under Organization, created another account in AWS_AP_SOUTH1 Region.
Afterward, enabled database replication for both accounts.

select system$global_account_set_parameter('EI91358',

'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

select system$global_account_set_parameter('IM28998',

'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

Promoted our local database to serve as a primary database.

alter database EMP_details enable replication to accounts AWS_AP_SOUTH_1.IM28998;

Created a replica of the primary Database in the Secondary Account.

create database EMP_details_rep as replica of AWS_US_EAST_2.EI91358.EMP_details;

Refresh the Secondary database.

alter database EMP_details_rep refresh;

Database Failover

Failover may be a backup operational mode that automatically switches to a standby database, server, or network if the first system fails or is packed up for servicing. Failover is an especially important function for critical systems that need always-on accessibility.

Use the Replication area of the Databases tab within the classic web interface to perform most actions associated with configuring and managing database replication, including the subsequent actions:

1. Promote a local database to serve as a primary database.

2. Enable failover for a primary database.

alter database EMP_DETAILS enable failover to accounts AWS_AP_SOUTH_1.IM28998;

3. Refresh a secondary database.

4. Promote a secondary database to serve as a primary database

alter database EMP_DETAILS_REP primary;

Database Failback

Failback is the process of restoring operations to a primary machine or primary facility after they need to be shifted to a secondary machine or facility during failover. During a site-wide failover, I/O (input/output) and its processes are shifted from a primary location to a short-lived disaster recovery (DR) location.

Once the Outage is resolved, By Failback, we can convert the new secondary database back to primary.

alter database EMP_DETAILS primary;

Client Redirect

Introduction:

Client Redirect enables redirecting your client connections to Snowflake accounts in numerous regions for business continuity and disaster recovery or when migrating your account to a different region or cloud platform.

Client Redirect is mainly implemented with the help of a Snowflake connection object. The connection object always stores a secure connection URL that you just use with a Snowflake client to attach to Snowflake.

The hostname within the connection URL consists of your organization name and the connection object name in addition to a common domain name

organization_name-connection_name.snowflakecomputing.com

The connection object name must be unique across all connection and account names within the organization.

Note that this hostname doesn't specify the account to which you're connecting. An account administrator determines the account to use by designating the connection therein to function as the first connection. Once you use the connection URL to attach to Snowflake, you're connecting to the account that contains the first connection.

If an outage occurs in a region or cloud platform and also the outage affects the primary connection account, then a connection in a different account in a different region or cloud platform is promoted to function as the primary connection by the administrator.

Through this outage, you'll still use the identical connection URL to attach to Snowflake. Snowflake resolves the connection URL to the account with the newly promoted connection (the account outside of the region or cloud platform full of the outage).

Implementation:

Prerequisite: We can contact the Snowflake Support team to enable the Client Redirect feature on our accounts. Enabling this feature requires that the Organizations feature must even be enabled on your accounts.

Create a Primary Connection: Create a brand new primary connection using CREATE CONNECTION. The name of every primary connection must be unique across all connection and account names within the organization. The connection name is included as a part of the connection URL to connect with Snowflake accounts.

** create connection Connection1; **

We can modify this primary connection using an ALTER CONNECTION ENABLE FAILOVER TO ACCOUNTS statement. Give a comma-separated list of accounts in your organization that store a failover option for this connection (i.e., a secondary connection).

alter connection myconnection enable failover to accounts myorg.myaccount2, myorg.myaccount3;

Create a Secondary Connection: Create a secondary connection in one or more accounts, linked to a primary connection using CREATE CONNECTION … AS REPLICA OF. Note that you can only create a secondary connection in an account specified in the ALTER CONNECTION … ENABLE FAILOVER TO ACCOUNTS statement used to create a Primary Connection. Execute a CREATE CONNECTION … AS REPLICA OF statement in each target account to create a replica of the specified primary connection.

create connection myconnection

as replica of myorg.myaccount1.myconnection;

Redirecting Client Connections

Promoting a Secondary Connection to Serve as the Primary Connection:

Initiating the redirect involves promoting a secondary connection in an available region to function as the first connection using ALTER CONNECTION. Concurrently, the previous primary connection becomes a secondary connection.

Execute the SQL statements given below within the target account containing the present secondary connection that you just are promoting.

-- promoting the secondary connection to serve as the primary connection

alter connection myconnection primary;

-- verifying that the previous secondary connection was promoted successfully

show connections;

Conclusion

Database Replication always enables storing read-only replicas of a primary database/account in other Snowflake databases/accounts. These accounts, which must be grouped within the same organization, may be located in several regions. Refreshing each replica (secondary database) syncs the database objects and stored data with its primary database/account.

Database Failover/Failback promotes a duplicate database to function as the first database. At that time, the previous primary database becomes a read-only secondary database, and therefore the former replica becomes the read-write primary database.

Client Redirect feature becomes vital when an outage occurs during a region or cloud platform, and also, the outage affects the account with the first connection, where the administrator can promote a connection in a very different account in a different region or cloud platform to function as the first connection.

Through this outage, you'll be able to still use the identical connection URL to attach to Snowflake. Snowflake resolves the connection URL to the account with the newly promoted connection (the account outside of the region or cloud platform laid low with the outage).