Author: Vineela Sowjanya Sabbella
Introduction:
The Business Continuity process can be enabled by supporting data in multiple accounts in different regions. So, any unexpected cases such as network issues, technical errors, or disaster conditions may lead to discontinuity in an organization or a business. To overcome this, we can replicate the databases. We can use the replica data from one account in another account found in different regions within the same organization. In the snowflake account, we have two features called database replication and failover/failback to implement this process.
In general, the replica database is known as the secondary database (target account), and from which it has been replicating is called the primary database (source account).
Database Replication:
In the database replication, we can create a replica of the primary database. We cannot change that replica.
Note: organization name-AAOEWSO
Primary Database-DV64904
Secondary Database-Singapore
Script:
1) Enable replication for each source and target account in the organization where ‘HK10986’ is the account name of my account. Apply the command for both the primary and secondary accounts by their account name.
use role orgadmin; show organization accounts; select system$global_account_set_parameter('HK10986','ENABLE_ACCOUNT_DATABASE_REPLICATION','true'); show replication accounts;
2) Promoting a Local Database to Serve as a Primary Database. Here, we are enabling database replication for a primary database called ‘raw_db’ and ‘analytical_db’. Later, in the secondary account, create replica database.
alter database raw_db ENABLE REPLICATION TO ACCOUNTS AAOEWSO.singapore; alter database analytical_db ENABLE REPLICATION TO ACCOUNTS AAOEWSO.singapore; —-in secondary account create database raw_dbase as replica of AAOEWSO.DV64904.raw_db; create database analytical_dbase as replica of AAOEWSO.DV64904.analytical_db; ------ Verify the secondary Database-------- show replication databases;
3) Refreshing Each Secondary Database. To synchronize the data in both databases, the refresh command is used, as shown below.
alter database raw_dbase refresh; alter database analytical_dbase refresh;
4) Scheduling tasks every 10 minutes. As the above-shown command is a manual process, we are using tasks to automate it.
create task refresh_raw_dbase_task warehouse = mywh schedule = '10 minute' as alter database raw_dbase refresh; alter task refresh_raw_dbase_task resume; create task refresh_analytical_dbase_task warehouse = mywh schedule = '10 minute' as alter database analytical_dbase refresh; alter task refresh_analytical_dbase_task resume;
We can also perform manual refresh as shown below in the web user interface.
Up to this step, we are done with the database replication. The data which we have replicated to the secondary account, i.e., raw_db, analytical_db to raw_dbase, and analytical_dbase respectively is only the read-only databases. We cannot change the database in the secondary account. For changing the secondary read-only database to read write primary database, we are applying failover.
Failover/Failback:
Failover promotes the read-only secondary database to a read-write primary database.
Script:
1) Enabling Failover for a Primary Database. Enable failover to both the replicated database, i.e., raw_db and analytical_db.
alter database raw_db enable failover to accounts AAOEWSO.singapore; alter database analytical_db enable failover to accounts AAOEWSO.singapore;
2) Converting read-only secondary databases to read and write primary databases. The below command converts the secondary read-only database ‘raw_dbase’ to a primary read-write one and the primary ‘raw_db’ to a read-only secondary database.
alter database raw_dbase primary; alter database analytical_dbase primary;
Conclusion:
In any error-prone condition where we cannot use data in one account in the organization, we can continue our business process in another account that is in another region by enabling the database replication and failover/failback. In this way, our data flow will be in a continuous format.
References:
https://docs.snowflake.com/en/user-guide/database-replication-config.htm