Authors: Rachana Kadiam & Jashuva Mokka
Content:
ACCOUNT REPLICATION AND FAILOVER GROUPS
Introduction:
Account Replication expands replication beyond databases to account metadata and integrations, taking business continuity to the next level. Users are able to recover their accounts in seconds, at virtually any scale. We can replicate users, roles, warehouses, and resource monitors, share databases, and can allow multiple accounts at the same time.
Note: Replication of other account objects and failover requires Business Critical Edition or higher.
Workflow:
Create a new Snowflake account with a business-critical edition.
Switch role to orgadmin
Go to the organization and copy the account locator of the source account.
To enable the replication of this account.
use role orgadmin;
show organization accounts;
select system$global_account_set_parameter('LH49635',
'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');
show replication accounts;
Create a new role, “myrole,” and assign all privileges
Also, grant the creation privilege for failover group on account of the role
use role accountadmin;
create role myrole;
grant create failover group on account to role myrole;
Assign the created role to users and other roles, respectively
Create databases db1 and db2
Grant the privileges to role “myrole” in doing the operations.
use role myrole;
create or replace database db1;
create or replace database db2;
grant role myrole to role ACCOUNTADMIN;
grant usage on WAREHOUSE COMPUTE_WH to role myrole;
grant usage on WAREHOUSE NEW1 to role myrole;
grant create database on account to role myrole;
grant create failover group on account to role myrole;
Executed on soucre account
Create a failover group on the source account and enable replication to specific target accounts.
In the failover group, we can define all the object types and specify a replication schedule.
create failover group myfg
object_types = users, roles, warehouses, resource monitors, databases
allowed_databases = db1,db2
allowed_accounts = SFHQZUP.JASHUVA
replication_schedule = '10 MINUTE';
Create a new account within the same organization
In snowsight, click on Admin>>Accounts>> Create a new account and specify the cloud provider, region, and edition.
Fill in the credentials and create the account.
Click on the account url here and open the target account.
Execute on the target account
Create a replica for your failover group and mention your
Organization_name.account_name.failovergroup_name
Refresh the target account by simply running this command
Use role ACCOUNTADMIN;
Create failover group myorg1 as replica of SFHQZUP.PS98977.myorg1;
Alter failover group myorg1 refresh;
Now we will be able to see all the databases, schemas, and warehouses that are replicated from your source account to the target account.
Now we will see how reverse replication works, that is, from the target account to the source account. When an outage occurs for the primary account, then our secondary account works as the primary account.
As of now, we are changing our target account to a primary account by running the command in the target account.
ALTER failover group myorg1 primary;
Now our target account works as the primary account, and we will have read-and-write access.
After the outage is resolved for the source account now, all the data that is present in the secondary account should replicate back to the primary account.
Before doing that, we first have to run a refresh command in the source account.
ALTER failover group myorg1 refresh;
After running this command, reverse replication for our account will be done. That is everything in our account will be replicated from the target account to the source account.
CLIENT REDIRECT CONNECTION
Introduction:
The client redirect feature provides a connection URL that can be used by Snowflake clients to connect to Snowflake. This URL can redirect Snowflake clients to a different Snowflake account.
Create a support case for target and source accounts by requesting a support team to enable client redirect features to create a new connection.
Once they have enabled it, they will reach you through the mail, and then you can start creating a new connection.
Create a new connection and alter the connection and enable failover to your target account (organization.target account name).
The target account and source account must be in different regions.
Run a query show connections, and now you can see your source account promoted as your primary account.
If your source account is in a secondary connection, to change it back to the primary account, run a query alter connection connection_name primary .
We have to change the connection manually.
To create a replica in the target account, run a query.
create connection connection- name as replica of organization .source-account.connection-name
Run a query show connections, and now you can see your Target account promoted as your secondary connection.
Now, if you want to change the secondary connection back to the primary connection in your target account, run a query alter connection connection-name primary.
Now run a query show connection. You can see your target account now has a primary connection.
And you will be able to see your connection url
organization-name.connection-name.snowflakecomputing.com This connection url can be used by Snowflake clients to connect to snowflake.
Conclusion:
In this blog, we have learned about new features - Account replication, failover groups, and client redirect features. We have demonstrated how the account replication works in the Snowflake environment, along with the working of the client redirect connections. Snowflake’s replication and failover capabilities, paired with Client Redirect, will allow teams to perform disaster recovery drills so they can best prepare for recovery during an outage while minimizing the impact on the business.
References:
https://docs.snowflake.com/en/user-guide/account-replication-failover.html
https://docs.snowflake.com/en/user-guide/client-redirect.html#introduction-to-client-redirect