跨多个账户的数据库复制简介

Important

This section describes a limited database replication feature that is different from the account replication feature. Snowflake strongly recommends using the account replication feature to replicate and failover databases.

This feature enables replicating databases between Snowflake accounts (within the same organization) and keeping the database objects and stored data synchronized. Database replication is supported across regions and across cloud platforms.

什么是主数据库？

可以为任何现有的永久或临时数据库启用复制功能。启用复制会将数据库指定为 主数据库。一个账户中可以指定任意数量的数据库为主数据库。同样，主数据库可以复制到组织中任意数量的账户。这需要在每个目标账户中创建一个 辅助数据库，作为指定主数据库的副本。这些账户通常位于其他区域、相同或不同的云平台上，也可以与源账户位于同一区域。

全部 DML/DDL 操作在主数据库上执行。每个只读辅助数据库都可定期刷新主数据库快照，复制所有数据以及对数据库对象（即架构、表、视图等）上的 DDL 操作。

数据库复制概述

For the full list of replicated database objects, see Replicated database objects.

账户中的其他对象

Database replication is supported for databases only. Other types of objects in an account can be replicated with account replication. For the full list of supported objects for account replication, see Replicated objects.

访问控制

Privileges granted on database objects are not replicated to a secondary database. This includes privilege grants on existing database objects as well as grants on future objects (i.e. future grants).

Privilege grants can be replicated with account replication.

参数

Account parameters are not replicated with database replication. Account parameters can be replicated with account replication.

在架构或架构对象级别设置的对象参数会被复制：

Parameter Objects
DATA_RETENTION_TIME_IN_DAYS schema, table
DEFAULT_DDL_COLLATION schema, table
MAX_DATA_EXTENSION_TIME_IN_DAYS schema, table
PIPE_EXECUTION_PAUSED ^[1] schema, pipe
QUOTED_IDENTIFIERS_IGNORE_CASE schema, table

Parameter	Objects
DATA_RETENTION_TIME_IN_DAYS	schema, table
DEFAULT_DDL_COLLATION	schema, table
MAX_DATA_EXTENSION_TIME_IN_DAYS	schema, table
PIPE_EXECUTION_PAUSED ^[1]	schema, pipe
QUOTED_IDENTIFIERS_IGNORE_CASE	schema, table

Parameter replication is only applicable to objects in the database (schema, table) and only if the parameter is explicitly set using CREATE <object> <parameter> or ALTER <object> … SET <parameter>. Database level parameters are not replicated.

Parameters explicitly set on objects in the primary database overwrite parameters set on objects in the secondary database. For example, if the primary database has a schema s1 with DATA_RETENTION_TIME_IN_DAYS set to 10 and the secondary database has DATA_RETENTION_TIME_IN_DAYS set to 1 at the database level, DATA_RETENTION_TIME_IN_DAYS for schema s1 in the secondary database is set to 10 after replication.

在辅助数据库的数据库级显式设置的参数不会被覆盖。例如，如果辅助数据库参数 DATA_RETENTION_TIME_IN_DAYS 显式设置为 1 而主数据库参数 DATA_RETENTION_TIME_IN_DAYS 显式设置为 10，则 DATA_RETENTION_TIME_IN_DAYS 复制后辅助数据库仍设置为 1。

^[1] Note that PIPE objects are not replicated. If the PIPE_EXECUTION_PAUSED parameter is set at the schema level in the primary database, it is replicated to the secondary database. When the secondary database is promoted to primary database in the case of a failover and a pipe is created, the parameter setting will take effect.

将数据库复制到较低版本的账户

如果满足以下任一条件，则当本地数据库升级为主数据库时，Snowflake 会显示错误消息：

主数据库位于 Business Critical（或更高版本）账户中，但批准复制的一个或多个账户位于较低版本中。Business Critical Edition 适用于具有极其敏感数据的 Snowflake 账户。
The primary database is in a Business Critical (or higher) account and a signed business associate agreement is in place to store PHI data in the account per HIPAA and HITRUST CSF regulations, but no such agreement is in place for one or more of the accounts approved for replication, regardless if they are Business Critical (or higher) accounts.

实施此行为是为了防止 Business Critical（或更高版本）账户的账户管理员无意中将敏感数据复制到较低版本的账户。

An account administrator can override this default behavior by including the IGNORE EDITION CHECK clause when executing the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement. If IGNORE EDITION CHECK is set, the primary database can be replicated to the specified accounts on any Snowflake edition.

数据库复制的当前限制

Databases created from shares cannot be replicated.
Refresh operations fail if the primary database includes a stream with an unsupported source object. The operation also fails if the source object for any stream has been dropped.
Append-only streams are not supported on replicated source objects.

CREATE DATABASE …AS REPLICA 命令不支持 WITH TAG 子句。

This clause is not supported because the secondary database is read only. If your primary database specifies the WITH TAG clause, remove the clause prior to creating the secondary database. To verify whether your database has the WITH TAG clause, call the GET_DDL function in your Snowflake account and specify the primary database in the function argument. If a tag is set on the database, the function output will include an ALTER DATABASE … SET TAG statement.
Stage and pipe replication are not supported. You can replicate stages and pipes using account replication. For more information, see Stage, pipe, and load history replication.
Secrets is not supported. You can replicate secrets using a replication or failover group.