Share via


Replicating Partitioned Tables and Indexes

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. 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.

Object

Created by using

Partitioned table or index

CREATE TABLE or CREATE INDEX

Partition function

CREATE PARTITION FUNCTION

Partition scheme

CREATE 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:

Replication copies objects to the Subscriber during the initial synchronization. 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. Transactional and merge replication do not support the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. Merge replication does not support the SWITCH PARTITION command of ALTER TABLE, but transactional replication does, as described in the following section.

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. Data is moved by using the SWITCH PARTITION command. For more information, see Transferring Data Efficiently by Using Partition Switching. 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. This is also an issue for SQL Server 2000 Subscribers, because SQL Server 2000 does not support partitioned tables.

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.

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, when set to true, SWITCH PARTITION can be executed against the publication database.

  • @replicate_partition_switch determines whether the SWITCH PARTITION DDL statement should be replicated to Subscribers. This option is valid only when @allow_partition_switch is set to true.

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. To execute SWITCH PARTITION on a table that is enabled for merge replication, remove the table from the publication.