Disaster Recovery: Replication, Failover & DR Guide

Imagine your company's entire data warehouse—customer orders, financial records, real-time analytics—is hosted on Snowflake. One day, a regional cloud provider outage takes down your primary Snowflake account. Without a disaster recovery plan, your business grinds to a halt. This is where Snowflake's business continuity features come in.

Snowflake offers robust replication and failover capabilities to ensure your data remains available even during catastrophic failures. Whether you need cross-region replication for geographic redundancy or cross-cloud replication to avoid vendor lock-in, Snowflake provides the tools to design a resilient architecture.

In this tutorial, you'll learn how to set up database replication, perform failover and failback, and implement a comprehensive disaster recovery strategy. We'll cover real-world production incidents, debugging guides, and best practices to keep your data safe and accessible. By the end, you'll be able to configure Snowflake for high availability and confidently handle outages.

Understanding Snowflake's Business Continuity Model

Snowflake's architecture separates storage and compute, allowing for unique disaster recovery capabilities. Business continuity in Snowflake revolves around three key concepts: replication, failover, and failback.

Replication is the process of copying databases from a primary account to one or more secondary accounts. These secondary accounts can be in different regions or even different cloud providers. Replication is asynchronous, meaning there is some lag between when data is written to the primary and when it appears on the secondary. This lag determines your Recovery Point Objective (RPO).

Failover is the act of switching from the primary account to a secondary account during a disaster. Snowflake supports manual failover (where you explicitly promote a secondary to primary) and automatic failover via client redirect (where the Snowflake service automatically redirects connections to a healthy account).

Failback is the process of returning to the original primary after the disaster is resolved. This involves reversing the replication direction and ensuring data consistency.

Snowflake also offers a feature called 'Business Critical Edition' which includes enhanced replication and failover options. The following sections will guide you through setting up these features.

-- Check current replication status
SHOW REPLICATION DATABASES;

-- View replication lag
SELECT DATABASE_NAME, REPLICATION_LAG_SECONDS
FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY
ORDER BY START_TIME DESC;

Setting Up Database Replication

To set up replication, you need at least two Snowflake accounts: a primary and a secondary. Both accounts must be on the same edition (e.g., Business Critical) and have the same cloud platform (or different if cross-cloud).

Step 1: Enable replication on the primary account. Step 2: Create a replication link between accounts. Step 3: Enable replication for specific databases.

Here's how to do it using SQL commands. First, on the primary account, run:

ALTER ACCOUNT SET ENABLE_REPLICATION = TRUE;

Then, on the primary, create a replication link to the secondary account:

CREATE REPLICATION LINK my_link AS REPLICATION OF ACCOUNT TO ACCOUNT <secondary_account_locator>;

Now, enable replication for a database:

ALTER DATABASE sales_db ENABLE REPLICATION TO ACCOUNTS <secondary_account_locator>;

On the secondary account, you must create the database as a replica:

CREATE DATABASE sales_db AS REPLICA OF <primary_account_locator>.sales_db;

Once created, replication starts automatically. You can monitor progress using SHOW REPLICATION DATABASES.

-- On primary account
ALTER ACCOUNT SET ENABLE_REPLICATION = TRUE;

CREATE REPLICATION LINK my_link
  AS REPLICATION OF ACCOUNT
  TO ACCOUNT 'XY12345';

ALTER DATABASE sales_db ENABLE REPLICATION
  TO ACCOUNTS 'XY12345';

-- On secondary account
CREATE DATABASE sales_db AS REPLICA OF
  'AB12345'.sales_db;

Performing Manual Failover and Failback

When a disaster strikes, you need to promote a secondary account to primary. This is a manual process unless you've configured automatic failover.

Manual Failover Steps: 1. Verify that the secondary account is healthy and has the latest data. 2. On the secondary account, promote it to primary:

ALTER ACCOUNT SET PRIMARY = TRUE;

This command makes the secondary account the new primary. All databases that were replicas become primary databases.

1. Update your application connection strings to point to the new primary account URL.

Failback Steps: After the original primary is restored, you need to reverse replication and fail back.

1. On the new primary (original secondary), enable replication back to the original primary:

ALTER DATABASE sales_db ENABLE REPLICATION TO ACCOUNTS <original_primary_locator>;

1. On the original primary, create the database as a replica:

CREATE DATABASE sales_db AS REPLICA OF <new_primary_locator>.sales_db;

1. Once replication is caught up, promote the original primary back:

ALTER ACCOUNT SET PRIMARY = TRUE;

1. Update connection strings again.

-- On secondary account during disaster
ALTER ACCOUNT SET PRIMARY = TRUE;

-- Check new primary status
SELECT SYSTEM$GLOBAL_ACCOUNT_CURRENT_REGION();

-- On original primary after recovery (failback)
ALTER DATABASE sales_db ENABLE REPLICATION
  TO ACCOUNTS 'XY12345';

CREATE DATABASE sales_db AS REPLICA OF
  'AB12345'.sales_db;

-- Promote original primary back
ALTER ACCOUNT SET PRIMARY = TRUE;

Configuring Automatic Failover with Client Redirect

Snowflake offers automatic failover through client redirect. When enabled, the Snowflake service monitors the health of your primary account. If it detects an outage, it automatically redirects new connections to a secondary account.

To enable automatic failover: 1. Ensure replication is set up as described earlier. 2. On the primary account, configure failover objects:

ALTER ACCOUNT SET FAILOVER_OBJECT = '<secondary_account_locator>';

1. On the secondary account, set it to accept failover:

ALTER ACCOUNT SET ACCEPT_FAILOVER = TRUE;

1. Optionally, set a failover group to include multiple databases:

CREATE FAILOVER GROUP my_group OBJECT_TYPES = DATABASES ALLOWED_DATABASES = (sales_db, analytics_db);

Now, when the primary account becomes unavailable, Snowflake will automatically redirect new connections to the secondary. Existing connections may be dropped and need to reconnect.

You can test automatic failover by simulating an outage (e.g., disabling the primary account temporarily).

-- On primary account
ALTER ACCOUNT SET FAILOVER_OBJECT = 'XY12345';

-- On secondary account
ALTER ACCOUNT SET ACCEPT_FAILOVER = TRUE;

-- Create failover group (optional)
CREATE FAILOVER GROUP my_group
  OBJECT_TYPES = DATABASES
  ALLOWED_DATABASES = (sales_db, analytics_db);

-- Check failover status
SHOW PARAMETERS LIKE 'FAILOVER_MODE';

Monitoring Replication and Failover Health

Continuous monitoring is essential to ensure your disaster recovery plan works. Snowflake provides several views and functions to track replication and failover status.

Set up alerts for replication lag exceeding your RPO. For example, use Snowflake's task and notification integration to send an email when lag > 5 minutes.

Example alert setup:

CREATE OR REPLACE TASK check_replication_lag WAREHOUSE = my_wh SCHEDULE = '5 MINUTE' AS CALL SYSTEM$SEND_EMAIL( 'my_int', 'admin@example.com', 'Replication Lag Alert', 'Lag is high: ' || (SELECT MAX(REPLICATION_LAG_SECONDS) FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY) );

-- Check current lag for all databases
SELECT DATABASE_NAME, REPLICATION_LAG_SECONDS
FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY
WHERE START_TIME > DATEADD('hour', -1, CURRENT_TIMESTAMP());

-- Get last replication timestamp for a database
SELECT SYSTEM$LAST_REPLICATION_TIMESTAMP('sales_db');

-- View failover events
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.FAILOVER_EVENTS
ORDER BY EVENT_TIMESTAMP DESC;

Cross-Cloud Replication and Multi-Region Strategies

Snowflake supports replication across different cloud providers (AWS, Azure, GCP) and regions. This is crucial for avoiding vendor lock-in and meeting compliance requirements.

Cross-cloud replication works similarly to cross-region replication, but requires that both accounts are on Business Critical Edition (or higher). The setup steps are identical, but you must ensure network connectivity between clouds (Snowflake handles this).

Multi-region strategies: For maximum resilience, replicate to at least two secondary accounts in different geographic regions. For example, primary on AWS us-east-1, secondary on AWS eu-west-1, and another secondary on Azure eastus.

Example: Replicate to a different cloud:

-- On primary (AWS) ALTER DATABASE sales_db ENABLE REPLICATION TO ACCOUNTS 'AZ12345'; -- Azure account

-- On secondary (Azure) CREATE DATABASE sales_db AS REPLICA OF 'AWS12345'.sales_db;

-- Enable replication to an Azure account from AWS
ALTER DATABASE sales_db ENABLE REPLICATION
  TO ACCOUNTS 'AZ12345';

-- On Azure account, create replica
CREATE DATABASE sales_db AS REPLICA OF
  'AWS12345'.sales_db;

-- Verify replication
SHOW REPLICATION DATABASES;

Best Practices for Disaster Recovery Testing

A disaster recovery plan is only as good as its last test. Regular testing ensures that your team knows the procedures and that the configuration works.

Best practices: 1. Schedule quarterly failover drills in a non-production environment. 2. Automate testing using Snowflake's SQL API or Terraform. 3. Validate data consistency after failover by comparing row counts or checksums. 4. Document the runbook with exact commands and contact information. 5. Include application teams in testing to ensure connection strings and authentication work.

Example test script:

-- Simulate failover ALTER ACCOUNT SET PRIMARY = TRUE; -- Run queries on new primary SELECT COUNT(*) FROM sales_db.orders; -- Failback ALTER ACCOUNT SET PRIMARY = FALSE; -- Not a real command; use proper failback steps

Use a separate test account to avoid impacting production.

-- Test failover on secondary account (non-production)
ALTER ACCOUNT SET PRIMARY = TRUE;

-- Verify data
SELECT COUNT(*) FROM sales_db.orders;

-- Check replication lag
SHOW REPLICATION DATABASES;

-- Failback (reverse replication)
-- (Assume original primary is restored)
ALTER DATABASE sales_db ENABLE REPLICATION TO ACCOUNTS 'PRIMARY_LOCATOR';
CREATE DATABASE sales_db AS REPLICA OF 'SECONDARY_LOCATOR'.sales_db;
ALTER ACCOUNT SET PRIMARY = TRUE;