Merge metadata cleanup changes in SQL Server 2012 SP2 and SQL Server 2014 SP1

Content provided by: Hugo Nunes, Microsoft Support Escalation Engineer

TITLE:

Merge metadata cleanup changes in SQL Server 2012 SP2 and SQL Server 2014 SP1

Abstract:

This article briefly describes the change in the merge metadata cleanup logic introduced in SQL Server 2012 SP2 and SQL Server 2014 SP1, and provides some recommendations

Terms and definitions:

Dormant subscriber- subscriber that synchronizes rarely, sometimes close to the limit of the retention period

Upload phase- phase within merge agent synchronization, in which changes at the Subscriber are enumerated and sent to the Publisher

Download phase- phase within merge agent synchronization, in which changes at the Publisher are enumerated and sent to the Subscriber.

Common generation- generation value that is calculated at the beginning of both upload and download phases to establish the starting point for change enumeration of each phase.

What is the motivation behind this change?

In some rare cases it was observed that unnecessary changes would be synchronized if Merge cleanup removed generations from the publisher that still existed on subscribers that were dormant.

In these circumstances, when the dormant subscriber synchronized very close to the end of the retention period, it could re-introduce the generations that had been removed from the publisher. The re-introduction of these generations interferes with the change enumeration process and as result unnecessary changes could be enumerated and transferred.

NOTE: This may not be the only cause to observe unnecessary changes being processed.

What has changed?

The changes to the cleanup procedure introduce the concept of active generation. A generation is active if it is higher than the lowest common generation amongst all active subscriptions. Hence, the cleanup will not only take into consideration the retention period, but also the oldest common generation of all active subscribers. All common generations above that one will be considerate as active.

Active generations will not be removed even if they’ve passed the retention period. This way, both publisher and subscribers maintain a reference to all active generations. This behavior is intentional and expected since it guarantees that cleaned up generations will not be accidently re-introduced by a dormant subscriber.

To implement this concept, the stored procedure sp_mergemetadataretentioncleanup had additional logic added to be able to exclude active generations from cleanup. On top of that, additional columns were added to sysmergesubscriptions table.

What are the observable effects of this change?

• The new logic of the cleanup may allow for metadata to be retained for a longer period.
• This may result in an increase of metadata tables’ size when compared with previous versions.
• The increase of metadata should not have any observable effect, and therefore should not be a reason for concern in most deployments.
• However, when the publication design is already prone to generate a lot of metadata (usually found on publications with complex filters and join filters constructs), the accumulated metadata could be enough to induce performance degradation.

Known issues:

1) Merge agent may fail with the following error immediately after applying SQL Server 2012 SP2:

The merge process could not perform retention-based metadata cleanup in database ‘MergePub’. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

This error is also observed if you manually execute the sp_mergemetadataretentioncleanup stored procedure.

This issue is caused by a defect that was later fixed in SQL Server 2012 SP2 Cumulative Update 3.

This issue will not occur with SQL Server 2014 Service Pack 1.

2) The cleanup is not removing metadata.

This may occur if not all subscribers have synchronized at least once after installing SQL Server 2012 SP2 or SQL Server 2014 Service Pack 1.

In very rare cases, it’s possible that metadata inconsistency in sysmergesubscriptions may result in all generations being considerate active and, therefore, none is eligible for cleanup.

Make sure to synchronize all subscribers after installing the service pack and consider removing subscribers that are no longer required and that do not synchronize in a long time.

SQL Server 2012 SP2 Cumulative Update 4 also introduced a few changes that may make it more resilient against metadata inconsistencies. (At the time of this writing these improvements are estimated to be included in SQL Server 2014 Service Pack 1 Cumulative Update 1. Please note that this information is subject to change).

3) Decrease in synchronization performance.

This may be noticeable sometime after the installation of the service pack, after enough metadata has built up.

It’s important to note that this is likely to be driven by the publication design.

The additional metadata that may now be retained simply makes the issue more noticeable.

SQL Server 2012 SP2 Cumulative Update 4 introduces additional optimizations to the cleanup that can considerably reduce the amount of metadata retained. (At the time of this writing these improvements are estimated to be included in SQL Server 2014 Service Pack 1 Cumulative Update 1. Please note that this information is subject to change).

Recommendations:

• Install at least SQL Server 2012 SP2 Cumulative Update 3 immediately after installing SQL Server 2012 SP2. For SQL Server 2014, installing Service Pack 1 will be enough.
• Consider installing SQL Server 2012 SP2 Cumulative Update 4 to benefit from improvements in retention algorithm and increased resilience to metadata inconsistencies. For SQL Server 2014, these improvements are expected to be included on Cumulative Update 1 for SP1 (subject to change).
• Make sure to synchronize all merge agents at least once, after applying SQL Server 2012 SP2 or SQL Server 2014 SP1
• Synchronize all your merge agents more frequently so that the oldest common generation of all subscribers is as close as possible to the most recent one.
• Remove a subscription as soon as possible if it is no longer maintained or needed. Do not wait until such a subscription is marked as expired when it passes retention. This helps keeping the metadata footprint as small as possible.

References:

SQL Server 2012 SP2 Release Notes
http://msdn.microsoft.com/en-us/library/67cb8b3e-3d82-47f4-840d-0f12a3bff565

Bugs that are fixed in SQL Server 2012 Service Pack 2
http://support.microsoft.com/KB/2958429
(Note: Refer to the topic “Highlights for SQL Server 2012 SP2” – “Replication” – “Functionality”)

Download Service Pack 2 for Microsoft® SQL Server® 2012
http://www.microsoft.com/en-us/download/details.aspx?id=43340

Cumulative update package 3 for SQL Server 2012 SP2
https://support.microsoft.com/en-us/kb/3002049/

Cumulative Update 4 for SQL Server 2012 SP2
https://support.microsoft.com/en-us/kb/3007556/

FIX: Error when you restart the Merge Agent after you upgrade the Publisher to SQL Server 2012 SP2
http://support.microsoft.com/kb/2982019

Download Service Pack 1 for Microsoft® SQL Server® 2014
https://www.microsoft.com/en-us/download/details.aspx?id=46694