Transactional replication with table partitions.

Question

This is my setup. ServerA receives data from the customers and is replicated to ServerB. In order to keep ServerA size small (100-200k rows) I have the tables partitioned on a date field (DateReceived) and the oldest partition is switched every night by a job. ServerB is billions of rows. I have altered the publication (sp_changepublication) and set the following properties:
'allow_partition_switch', 'true'
'replicate_partition_switch', 'false'
'replicate_ddl', 0

Everything works like a charm except every once in a while, the partition switch will kill replication. I would like to suspend replication and find out how to tell if all transactions have replicated then let the job continue and restart replication. Can someone provide some help on this please?

Answer 1

Hi @Spruill, Mitch ,

Partitioning properties are the article schema options that determine whether partitioning objects should be copied to the Subscriber. Specify a value of true for the properties Copy table partitioning schemes.

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.

Please refer to MS document Replicate Partitioned Tables and Indexes to get more notes and limits.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @CathyJi-MSFT

Thank you so much for the link. I only have partitioning set on the publisher not on the subscriber. Therefore, cannot replicate DDL commands. In the link provided I found the issue I am trying to solve.

From the link:
Before executing a split or merge operation on a replicated or CDC enabled table ensure that the partition in question does not have any pending replicated commands. You should also ensure that no DML operations are executed on the partition during the split and merge operations. If there are transactions which the log reader or CDC capture job has not processed, or if DML operations are performed on a partition of a replicated or CDC enabled table while a split or merge operation is executed (involving the same partition), it could lead to a processing error (error 608 - No catalog entry found for partition ID) with log reader agent or CDC capture job. In order to correct the error, it might require a reinitialization of the subscription or disabling CDC on that table or database.

Error 608 is the error I am receiving. Now the question is what is the proper way to ensure that the pending partition in question does not have any pending commands? Thank you for any assistance.