How to create P2P replication to enable table partition switching

Sateesh Yele
Microsoft SQL Server Support

Peer-2-Peer Replication Wizard provides no options to enable table partition switching. There are 2 ways to enable this feature. 

1) create the publication on ALL the peers using TSQL sp_addpublication
2) create the subscriptions using the user interface

-- or --

1) create the publication using the Replication Wizard
2) use sp_changepublication to set properties on ALL peers
3) create the subscriptions

MSDN ONLINE: Replicating Partitioned Tables and Indexes

exec sp_addpublication @publication = N'mypub’

. . .

, @enabled_for_p2p = N'true'
, @enabled_for_het_sub = N'false'
, @p2p_conflictdetection = N'true'
, @p2p_originator_id = 1
, @allow_partition_switch = N'true'

, @replicate_partition_switch = N'true'

According to BOL when P2P is enabled, the following restrictions will apply in the publication properties.

· allow_anonymous must be false.
· allow_dts must be false.
· allow_initialize_from_backup must be true.
· allow_queued_tran must be false.
· allow_sync_tran must be false.
· conflict_policy must be false.
· independent_agent must be true.
· repl_freq must be continuous.
· replicate_ddl must be 1.