複寫資料分割資料表及索引Replicate Partitioned Tables and Indexes

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

分割大型資料表或索引將更易於管理,因為分割可讓您快速並有效率地管理及存取資料子集,同時又可維護資料收集的完整性。Partitioning makes large tables or indexes more manageable because partitioning enables you to manage and access subsets of data quickly and efficiently, and maintain the integrity of a data collection at the same time. 如需詳細資訊,請參閱< Partitioned Tables and Indexes>。For more information, see Partitioned Tables and Indexes. 複寫可支援資料分割,其方式是提供一組屬性來指定應該如何處理資料分割資料表和索引。Replication supports partitioning by providing a set of properties that specify how partitioned tables and indexes should be treated.

交易式和合併式複寫的發行項屬性Article Properties for Transactional and Merge Replication

下表列出用來分割資料的物件。The following table lists the objects that are used to partition data.

ObjectObject 建立的方法Created by using
資料分割資料表或索引Partitioned table or index CREATE TABLE 或 CREATE INDEXCREATE TABLE or CREATE INDEX
分割區函數Partition function CREATE PARTITION FUNCTIONCREATE PARTITION FUNCTION
分割區配置Partition scheme CREATE PARTITION SCHEMECREATE PARTITION SCHEME

與資料分割有關的第一組屬性是發行項結構描述選項,這些選項可決定是否應將資料分割物件複製到訂閱者。The first set of properties related to partitioning are the article schema options that determine whether partitioning objects should be copied to the Subscriber. 可透過下列方式來設定這些結構描述選項:These schema options can be set in the following ways:

  • 在新增發行集精靈的 [發行項屬性] 頁面中,或是在 [發行集屬性] 對話方塊中。In the Article Properties page of the New Publication Wizard or the Publication Properties dialog box. 若要複製上表所列的物件,請針對 [複製資料表資料分割配置][複製索引資料分割配置] 屬性指定 true的值。To copy the objects listed in the previous table, specify a value of true for the properties Copy table partitioning schemes and Copy index partitioning schemes. 如需如何存取 [發行項屬性] 頁面的詳細資訊,請參閱檢視和修改發行集屬性For information about how to access the Article Properties page, see View and Modify Publication Properties.

  • 藉由使用下列其中一個預存程序的 schema_option 參數:By using the schema_option parameter of one of the following stored procedures:

    若要複製上表所列的物件,請指定適當的結構描述選項值。To copy the objects listed in the previous table, specify the appropriate schema option values. 如需有關如何指定結構描述選項的詳細資訊,請參閱< Specify Schema Options>。For information about how to specify schema options, see Specify Schema Options.

複寫會在初始同步處理期間將物件複製到訂閱者。Replication copies objects to the Subscriber during the initial synchronization. 如果資料分割配置使用 PRIMARY 以外的檔案群組,這些檔案群組必須在初始同步處理之前存在於訂閱者上。If the partition scheme uses filegroups other than the PRIMARY filegroup, those filegroups must exist on the Subscriber before the initial synchronization.

在初始化訂閱者之後,資料變更會傳播到訂閱者,並套用到適當的資料分割。After the Subscriber is initialized, data changes are propagated to the Subscriber and applied to the appropriate partitions. 但是,不支援資料分割配置的變更。However, changes to the partition scheme are not supported. 交易式及合併複寫無法複寫下列命令:ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或 ALTER INDEX 的 REBUILD WITH PARTITION 陳述式。Transactional and merge replication do not support replicating the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. 這些命令所關聯的變更不會自動複寫到訂閱者。The changes associated with them will not be automatically replicated to the Subscriber. 使用者必須在訂閱者中手動進行類似的變更。It is the responsibility of the user to make similar changes manually at the Subscriber.

資料分割切換的複寫支援Replication Support for Partition Switching

資料表資料分割的其中一個重要優點,就是能夠快速及有效率地在資料分割之間移動資料子集。One of the key benefits of table partitioning is the ability to quickly and efficiently move subsets of data between partitions. 資料的移動是利用 SWITCH PARTITION 命令。Data is moved by using the SWITCH PARTITION command. 根據預設,當啟用資料表進行複寫時,會基於以下理由而封鎖 SWITCH PARTITION 作業:By default, when a table is enabled for replication, SWITCH PARTITION operations are blocked for the following reasons:

  • 如果資料移入或移出存在於發行者上但不存在於訂閱者上的資料表,則發行者和訂閱者彼此可能會不一致。If data is moved into or out of a table that exists at the Publisher but does not exist at the Subscriber, the Publisher and Subscriber could become inconsistent with one another. 當資料移入或移出臨時資料表時,通常會發生這個問題。This problem typically occurs when data is moved into or out of a staging table.

  • 如果訂閱者對於資料分割資料表的定義與發行者不同,則當散發代理程式嘗試在訂閱者上套用變更時,將會失敗。If the Subscriber has a different definition for the partitioned table than the Publisher, the Distribution Agent will fail when it tries to apply changes at the Subscriber.

雖然有這些潛在的問題,還是可以啟用資料分割切換來進行異動複寫。Despite these potential issues, partition switching can be enabled for transactional replication. 在您啟用資料分割切換之前,請確定與資料分割切換有關的所有資料表都存在於發行者和訂閱者上,並確定資料表定義和資料分割定義是相同的。Before you enable partition switching, make sure that all tables that are involved in partition switching exist at the Publisher and Subscriber, and make sure that the table and partition definitions are the same.

當資料分割在發行者端和訂閱者端擁有完全相同的資料分割配置時,您可以開啟僅將資料分割切換陳述式複寫至訂閱者的 allow_partition_switchreplication_partition_switchWhen partitions have the exact same partition scheme at the publishers and subscribers you can turn on allow_partition_switch along with replication_partition_switch which will only replicate the partition switch statement to the subscriber. 您也可以在不複寫 DDL 的情況下,開啟 allow_partition_switchYou can also turn on allow_partition_switch without replicating the DDL. 當您想要將舊的月份傳出資料分割,但針對其他年份,則將複寫的資料分割保留在原位以便在訂閱者端進行備份時,這非常實用。This is useful in the case where you want to roll old months out of the partition but keep the replicated partition in place for another year for backup purposes at the subscriber.

若您透過目前的版本啟用了 SQL Server 2008 R2 的資料分割切換功能,您可能很快就會需要執行分割及合併作業。If you enable partition switching on SQL Server 2008 R2 through the current version, you might also need split and merge operations in near future. 對複寫的資料表執行分割或合併作業之前,請確定有問題的分割區上沒有任何暫止的複寫命令。Before executing a split or merge operation on a replicated table ensure that the partition in question does not have any pending replicated commands. 此外也必須確定未在分割及合併作業期間,對分割區執行任何 DML 作業。You should also ensure that no DML operations are executed on the partition during the split and merge operations. 如有交易的記錄讀取器未處理,或在執行分割或合併作業時,對複寫資料表的資料分割執行了 DML 作業 (對象均為同一個資料分割),可能會導致記錄讀取器代理程式發生處理錯誤。If there are transactions which the log reader has not processed, or if DML operations are performed on a partition of a replicated table while a split or merge operation is executed (involving the same partition), it could lead to a processing error with log reader agent. 若要更正此錯誤,可能需要重新初始化訂閱。In order to correct the error, it might require a re-initialization of the subscription.

警告

由於用來偵測並解決衝突之隱藏資料行的緣故,您不得為點對點發行集啟用資料分割切換。You should not enable partition switching for Peer-to-Peer publications, due to the hidden column which is used to detect and resolve conflict.

啟用資料分割切換Enabling Partition Switching

交易式發行集的下列屬性可讓使用者控制複寫環境中的資料分割切換行為:The following properties for transactional publications enable users to control the behavior of partition switching in a replicated environment:

  • @allow_partition_switch,當設定為 true 時,SWITCH PARTITION 可以針對發行集資料庫來執行。@allow_partition_switch, when set to true, SWITCH PARTITION can be executed against the publication database.

  • @replicate_partition_switch 會決定 SWITCH PARTITION DDL 陳述式是否應該複寫到訂閱者。@replicate_partition_switch determines whether the SWITCH PARTITION DDL statement should be replicated to Subscribers. 只有當 @allow_partition_switch 設定為 true 時,這個選項才有效。This option is valid only when @allow_partition_switch is set to true.

您可以在建立發行集時使用 sp_addpublication 來設定這些屬性,或是在建立發行集之後使用 sp_changepublication 來設定。You can set these properties by using sp_addpublication when the publication is created, or by using sp_changepublication after the publication is created. 如前所述,合併式複寫不支援資料分割切換。As noted earlier, merge replication does not support partition switching. 若要在已啟用合併式複寫的資料表上執行 SWITCH PARTITION,請從發行集中移除此資料表。To execute SWITCH PARTITION on a table that is enabled for merge replication, remove the table from the publication.

另請參閱See Also

發行資料和資料庫物件Publish Data and Database Objects