1

I was wondering whether we can use partitioned tables at the Publication level and non-partitioned tables at the Subscriber level? I was reading this article, Replicating Partitioned Tables and Indexes

It doesn't mention anything about what if Publication has a partitioned table and subscribers are non-partitioned. Is it even possible? If yes how does it work? Because I have read somewhere that partition switch just changes the metadata of that partition. How would SQL Server know there’s new data to be replicated?

James Z
  • 12,209
  • 10
  • 24
  • 44
techno
  • 192
  • 13

1 Answers1

1

If you have a mixed setup, where the publisher has a partition setup the subscriber does not, then you cannot perform partition SWITCHES. Doing so will result in a failure (due to the underlying associated transaction for the SWITCH, not being possible).

See the Replication Support for Partition Switching section, bullet two:

If the Subscriber has a different definition for the partitioned table than the Publisher, the Distribution Agent will fail when it tries to apply (SWITCH) changes at the Subscriber.

Additional comments: This is not to imply that you CANNOT have a mix setup, just that you cannot perform a SWITCH on the publisher, IF it will impact/replicate to the subscribers. During your article setup, sp_addarticle, there are options for replicating partition schema and indexes, under the @schema_options

Andrew Loree
  • 263
  • 1
  • 8