Mastering Partition Switching in Transactional Replication

Introduction

Partition switching is a powerful feature in SQL Server that allows for efficient data management and movement. However, when it comes to transactional replication, there are specific properties that must be managed to ensure consistency and performance. In this blog post, we’ll explore how to control the behavior of partition switching in a replicated environment.

Understanding the Properties

Two key properties govern partition switching in transactional publications:

• @allow_partition_switch: When set to true, this property enables the execution of the SWITCH PARTITION command against the publication database.
• @replicate_partition_switch: This property decides if the SWITCH PARTITION DDL statement should be replicated to Subscribers. It’s only applicable if @allow_partition_switch is true.

These properties can be set using sp_addpublication when creating the publication or sp_changepublication after the publication’s creation.

The Case with Merge Replication

It’s important to note that merge replication does not support partition switching. If you need to execute SWITCH PARTITION on a table that is part of merge replication, you must first remove the table from the publication.

The Benefits and Challenges

Table partitioning’s main advantage is the swift and efficient movement of data subsets between partitions, typically done using the SWITCH PARTITION command. By default, replication blocks SWITCH PARTITION operations to prevent inconsistencies between the Publisher and Subscriber, especially if the Subscriber lacks the table or has a different partitioned table definition.

Enabling Partition Switching

Despite the challenges, you can enable partition switching for transactional replication. Before doing so, ensure that all involved tables exist at both the Publisher and Subscriber and that the table and partition definitions match.

When the partitions share the same scheme at the Publisher and Subscriber, you can activate @allow_partition_switch along with @replicate_partition_switch. This setup replicates only the partition switch statement to the Subscriber.

Alternatively, you can enable @allow_partition_switch without replicating the DDL, which is useful for rolling out old data from the partition while maintaining the replicated partition at the Subscriber for backup purposes or when you have a different partition scheme on the Subscriber.

Unsupported Scenarios

The following scenarios are not supported when using replication with partition switching:

• Peer-to-peer replication: Peer-to-peer replication is not supported with partition switching.
• Use of variables with partition switching: Using variables with partition switching on tables published with transactional replication or Change Data Capture (CDC) is not supported for the ALTER TABLE ... SWITCH TO ... PARTITION ... statement.
• Foreign Keys with CHECK CONSTRAINT: Tables with foreign keys using WITH CHECK CHECK CONSTRAINT cannot use partition switching. Options include:
  • Disabling the foreign key check to enable table switching via ALTER TABLE [SourceTable] SWITCH TO [TargetTable].
  • Changing the foreign key to NOCHECK CONSTRAINT. Running ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT <constraint> will alter it back, but this can take time and cause blocking on foreign keys referencing larger tables.

Conclusion

Partition switching can significantly enhance data management in a replicated environment. By carefully setting the appropriate properties and ensuring consistency between Publisher and Subscriber, you can leverage this feature to its full potential.