SQL Server 2014 Transactional Replication Partition Switching

Question

I am working with SQL Server 2014 Transactional Replication with tables that have partitions. The source tables are partition switched when they are loaded and so far I have been able to replicate this successfully.

The replication source I am working with performs some dynamic partition management by creating partitions on the Partition Function/Partition Scheme during the load into the table. This is not natively supported by Transactional Replication per the documentation found here:

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 replicating the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. The changes associated with them will not be automatically replicated to the Subscriber. It is the responsibility of the user to make similar changes manually at the Subscriber.

This is where I am getting hung up. We've worked through most of the problems with the loading and switching of partitions and we are now dynamically creating the new partitions, as they come in, on both the replication source (publisher) and replication target (subscriber) through the use of a stored procedure as part of the load to the publisher. The stored procedure exists on both the publisher and subscriber (manually put there).

When running a test last night, we saw that partitions were dynamically created on the publisher and subscriber, but there was a new error on the Log Reader Agent. At this point, I don't even know where to begin to start tracking this down.

Error messages: The process could not execute 'sp_replcmds' on 'RNCAZFAST2'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011

No catalog entry found for partition ID 72057598976393216 in database 27. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption. (Source: MSSQLServer, Error number: 608)

I started looking up the error and it looked like it was a bug back in 2005, but we are on SQL Server 2014 Enterprise on an Azure VM. Any help is greatly appreciated!

Answer 1

We ended up coming up with a solution for this. Basically, we had to manage the partition creation for both the publisher and subscriber databases/tables when we made a change. We were loading data using SSIS and there were mechanics that were built in to create new partitions on the publisher, so we implemented the same logic to do the work on the subscriber too.

Of course, this can be difficult if you get into having multiple subscribers. I suppose one solution would be to query the subscription details and dynamically perform the same ALTER PARTITION statement to any subscriber database.

Answer 2

I have recently seen this behaviour, in SQL 2008R2 and SQL2014. In my case, the error was caused by partition functions being MERGEd immediately after individual partitions on a published table had been SWITCHed out.

When sp_replcmds was executed, it was attempting to retrieve metadata about a given partition_id (the switched partition). However, since the partition function merge operation, that partition_id no longer existed.

My fix was to decouple the partition SWITCH and MERGE ops into different stored procedures, and ensure that the log reader had time to execute correctly in between execution of each.

N.B.: This was agonizing to troubleshoot.