All You Need To Know About Snowflake Database Replication & Fail-Over/Failback — Part 1

This blog will be multipart blog and going to start with basic and then increase the understanding level till complex concept. Please read all the part of this series to get the full understanding for this important topic. Database replication is standard edition ready feature and most of the organization are using this from long time. Recently Snowflakes has announced the Account replication feature(In Preview) which is going to further reduce the overhead of admin team. If you have not seen my blog on account replication than I am sharing a link for the same.

Above link also contains Snowflake Admin Series: Redirecting Client Connections which is another super feature recently introduce by Snowflake. This feature is available to Business Critical Edition (or higher). For more detail read out my blog on same.

How much relevant this topic is with respect to certification exam?

It will be one of important topic in Architect/Administration certification. Few straight forward question might appear into Snowpro exam, but that will be more of a direct question.

What is Disaster Recovery or DR in general ?

A very nice and self-explanatory definition from Wikipedia “Disaster recovery is the process of maintaining or reestablishing vital infrastructure and systems following a natural or human-induced disaster, such as a storm or battle.It employs policies, tools, and procedures. Disaster recovery focuses on the information technology (IT) or technology systems supporting critical business functions as opposed to business continuity. This involves keeping all essential aspects of a business functioning despite significant disruptive events; it can therefore be considered a subset of business continuity.Disaster recovery assumes that the primary site is not immediately recoverable and restores data and services to a secondary site”.

In Snowflake, to support a business continuity plan we set up one primary snowflake account, and then we replicate the data to one or many secondary accounts as per your organization business continuity plan. In event of DR we switch between primary & secondary to support continuity of business for our organization.

What is Region Group?

A very nice definition from Snowflake documentation “A region group is a group of Snowflake Regions that offer similar security controls, isolation, and compliance.”

You can replicate database within replication group. To replicate database across replication group is not enabled by default, and can be enabled by Snowflake on request. Below are the different replication group in snowflake.

• All Snowflake multi-tenant commercial regions (across all the supported cloud platforms) are in the same shared/general PUBLIC group.
• Each Snowflake multi-tenant government region is in a separate group specific to the region.
• Each single-tenant Virtual Private Snowflake (VPS) is in a separate region group specific to the VPS. If your organization has more than one VPS, you can have one VPS per region group or multiple VPSs can share the same region group.

For more details on Snowflake's region look into documentation.

What is Primary and Secondary Database?

When you enable the database replication for any existing database in a snowflake account that database is known as Primary database. Primary database is a read-write database where you perform all your operation and then get that replicated to secondary database. You can as many number of primary database, and you can replicate the same database to as many secondary accounts.

When you create the replica of primary database in to any no of target account across or same region for same or different cloud provider snowflake account than it is termed as secondary database. Secondary database is a read-only copy of the database unless you fail over to secondary account.

Can we enable replication for any database?

You can enable replication for any existing permanent or transient database.

What is Database Replication?

When any organization wants to replicate or sync any database from one account to another account within the same organization automatically across region and even across cloud provider . Replicated copy of database in secondary or target account is always read-only copy, and you will not able to perform any DML or DDL operation on them.

Database replication is a standard feature and available to all account.

What is Database Failover?

Database failover is very similar to Database replication. There is one major difference which make this feature available to Business Critical or higher edition only.

Database replication only replicates or sync the database from one account to another account within the same organization but in the event of disaster in primary database it will not switch to secondary database for business continuity. To do that you need to set up the database failover.

Database failover allows you to replicate the database to the target account as well as failover to a secondary database in event of disaster. It also supports to make secondary database as primary and start the database replication in reverse order once required.

What is Database Fail back?

Database fail back is the situation when your original primary database is now recovered and ready to be set for usage. Once you are sure that both primary and secondary database are in sync and there is no difference between them than you can switch back to original primary account using database failover feature.

How much secure is database replication? What about the data security while replicating database?

All data file is encrypted first before syncing to secondary account. Snowflake encrypts files for database replication operations using a random, unique key for each replication job.

Can we replicate the database from any Snowflake edition account to any edition?

In general, we should do replication between 2 account which are on same edition to overcome any limitation or feature difference between 2 different editions.

But if your primary account is on Business Critical edition and your secondary accounts approved for replication is on lower edition than Snowflakes by default raise error while you try to replicate to lower editions to secure the extremely sensitive data. You can bypass this by adding another edition ignore keyword. Snowflake has done this to protect the sensitive data in business critical account but leave it to organization if they really want to proceed with this on their own consent.

Snowflake also put an extra check for business critical edition if you store PHI data in primary account and business associate agreement is in place. Even if your secondary account is in business critical edition but without business associate agreement Snowflake raise an error message while you try to replicate the data to said secondary account.

Hope this blog helps you to get insight into the database replication and failover/fail back feature in Snowflake. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find Me:

Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me on LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/

#Keep learning #Keep Sharing #RajivGuptaEverydayLearning #SnowflakeDataSuperhero #RajivGupta