Primary and Secondary Databases

When you enable data replication, you need to define the primary database as part of the setup. Under business-as-usual (BAU) operations, changes are written to and committed to this primary database first. When users or applications query the service, data is returned from this database. When the primary database is replicated to another account, this copy is called the secondary database.

The secondary database is in a read-only mode, meaning changes cannot be applied directly to it by a user or application. If this were permitted, then it wouldn’t be in sync with the primary database, breaking the replication process. When a DDL or DML operation is carried out on the primary database, those changes are periodically batched up and replayed against the secondary database. All data along with any changes to objects such as schemas, tables, or views are all replicated.

If you are running the Business Critical edition and you configure replication to a lower account, Snowflake will throw an error. This is a good feature and was put in place by design to prevent exposing sensitive data by mistake. As discussed, the Business Critical edition is typically used by organizations that handle highly sensitive data, which is why Snowflake recognizes this and tries to help out in this scenario. You can override this default behavior by using the IGNORE EDITION CHECK clause when executing the enabling replication statement, which I’ll cover later.

You should be aware that compute resources are used to support data replication operations.

Promoting Databases

The process of promoting a secondary database to a primary database is not an automated one. To promote a secondary database to a primary database, a role with the OWNERSHIP privilege on the database is required to execute the ALTER DATABASE mydb1 PRIMARY; command. You must then point your connections to the newly promote primary database.

Client Redirect

At the time of writing, the client redirect feature was in preview. However, I want to touch upon it here as the aim of this capability is to expose a secure URL for your organization’s Snowflake databases regardless of which Snowflake account they reside within. Although you still need to manually promote a secondary database to a primary as described above, this capability negates the need to manually update the URL your client connections and applications are using.

The hostname in the connection URL is composed of your organization name and the connection object name in addition to a common domain name: organization_name-connection_name.snowflakecomputing.com.

Take note that the hostname does not specify the account to which you are connecting. An account administrator determines the account to use by assigning an account to serve as the primary connection. When you use this URL to connect to Snowflake, you are connecting to the account that has been defined as the primary connection.

If an outage that impacts the primary database connection occurs, the administrator can point this URL to a Snowflake account that stores the secondary databases. Throughout the outage you can continue to use the same URL to access your databases.

Process Flow

The following set of diagrams walks through how the failover and failback processes work. Say your organization has two accounts, one in the AWS’ US East (Ohio) region and the other on Google’s asia-east2-b (Hong Kong) region.

Initially you promote the local database instance in Ohio to serve as the primary database. You also configure database replication for the secondary account in Hong Kong, as in Figure 6-1.

Next, within your secondary account, in Hong Kong, you create the secondary database and immediately kick off the data refresh to populate the database from the primary (Figure 6-2). The secondary database is in read-only mode.

Let’s assume there’s a major issue and it takes the US East (Ohio) region offline. You can then failover to the secondary database in Hong Kong, which is then promoted to become the primary, becoming writable in the process to support the newly directed traffic to this instance, as shown in Figure 6-3.

Once the issue in the US East (Ohio) region has been resolved and your database comes back online, then any data that has been written to the newly promoted primary database (in Hong Kong) will need to be written to this secondary database (Figure 6-4).

The databases are now in sync. You can now choose if this configuration is satisfactory, or you might want to failback. Let’s say most of your workloads and users are based in the US. Some users may experience a little more latency when using the instance based in Hong Kong. In this case, you decide to failback and promote the secondary instance (currently the Ohio one) back to the primary role (Figure 6-5).

Monitoring Replication Progress

You’re able to monitor the refresh duration within the Web UI in the Databases ➤ Replication tab. The Last Refresh Status contains a colored progress bar (Figure 6-6), which, when hovered over, provides a detailed breakdown of the time taken for each stage in the refresh process.

Highlighting the row of the database on this page brings up a sidebar displaying the current refresh status (Figure 6-7), along with the refresh start time and the number of bytes transferred.

Reconciling the Process

You may want to periodically conduct your own checks to ensure the primary and secondary databases are in sync.

Snowflake recommends using the HASH_AGG function across a random set of tables in all databases. This function creates a unique fingerprint for the rows you provide it with. You can provide it with an entire table (or a subset of a table using a WHERE clause or a TIME TRAVEL function such as TIMESTAMP, for example) and it will return an aggregate signed 64-bit hash value.

You can then use the hash values from both databases to compare for any differences. You can automate this process by writing the results to a metadata table.

Data Loss

Figure 6-8 illustrates where these two metrics sit in the disaster recovery process.

While the RTO for Snowflake can be minimal due to the fact you don’t need to deal with restoring physical backups, the RPO is important to consider when scheduling your data refresh from your primary to your secondary set. You should look to schedule your data refreshes so that if you lost your primary instance, any data loss is well within your RPO.

The Example Scenario

You’re going to set up a cross-cloud configuration between two different cloud providers. To do this, you need to create primary and secondary databases before scheduling an ongoing refresh between the two databases. Figure 6-9 illustrates what the finished setup will look like.

Steps

Step 1: Configure Replication and Failover

In the Databases section of the web UI, you will see a Replication tab. If you select it and then highlight the database you wish to use, you can click Enable Replication, which brings up the dialog box shown in Figure 6-10.

Step 2: Select an Account to Replicate the Data to

The Available to dropdown box lists all the accounts within your organization (Figure 6-11). Select the account you wish to replicate the data to and click Enable.

Step 3: Create a Secondary Database on the Replicated Account

Now you can head on over to the secondary account. Log into the web UI and go to the Replication tab in the Databases section. Since you have configured the replication to this account, you will see your database appear in the Available area (Figure 6-12).

Select the database and click Create Secondary Databases. The dialog box in Figure 6-13 will pop up. Ensure that the Refresh immediately after creation check box is checked and click Create.

Step 4: Monitor the Initial Data Refresh

If you’ve followed the steps above, then the data refresh from the primary to the secondary database will run immediately. The Last Refresh Status column will display In progress, as shown in Figure 6-14.

Once the data refresh is complete, you can hover over the progress bar and see the duration that relates to each state of the refresh process (Figure 6-15).

Step 5: Schedule Ongoing Data Refreshes

You can schedule ongoing refreshes to refresh your secondary database(s). Clicking Schedule Refresh brings up the dialog box (Figure 6-16) that contains a SQL template.

You can see that the template will create a new task. You can amend the schedule as needed and execute it for regular data refreshes in line with you RPO and RTOs.