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:
In the Article Properties page of the New Publication Wizard or the Publication Properties dialog box. 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 How to: View and Modify Publication and Article Properties (SQL Server Management Studio).
By using the schema_option parameter of one of the following stored procedures:
sp_addarticle or sp_changearticle for transactional replication
sp_addmergearticle or sp_changemergearticle for merge replication
To copy the objects listed in the previous table, specify the appropriate schema option values. For information about how to specify schema options, see How to: Specify Schema Options (Replication Transact-SQL Programming).
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. Snapshot replication does permit changes to the partitioning scheme because the snapshot is reinitialized every time the subscriber synchronizes with the publication.
Transactional and merge replication support a maximum of 1024 partitions per table or index.
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.
When 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. You 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.
Warning
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, 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.