%3CLINGO-SUB%20id%3D%22lingo-sub-1448792%22%20slang%3D%22en-US%22%3ESchema%20change%20getting%20applied%20repeatedly%20every%20time%20Merge%20agent%20runs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448792%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ERecently%20worked%20on%20an%20interesting%20case.%20Customer%20had%20an%20existing%20Merge%20Replication%20and%20it%20was%20working%20fine.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EThey%20added%20a%20new%20article%20to%20the%20existing%20Publication%20and%20ran%20the%20Snapshot%20Agent.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ENow%20Every%20time%20Merge%20agent%20runs%20we%20see%20that%20Schema%20changes%20were%20getting%20re-applied.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MergeApplyingSchemaChanges.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197309iA141E2510478668E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22MergeApplyingSchemaChanges.png%22%20alt%3D%22MergeApplyingSchemaChanges.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAt%20the%20start%20of%20a%20synchronization%2C%20the%20schemaversion%20of%20the%20subscription%20is%20checked%20against%20sysmergeschemachange%20by%20the%20Merge%20Agent%2C%20and%20schema%20changes%20are%20applied%20to%20the%20subscriber%20accordingly.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ERan%20these%20queries%20on%20the%20subscriber%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26gt%3B%26gt%3B%26gt%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eselect%20max(schemaversion)%20from%20sysmergeschemachange%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E--22%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eselect%20schemaversion%2C%20pubid%2C%20subid%20from%20sysmergesubscriptions%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Ewhere%20pubid%26lt%3B%26gt%3Bsubid%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eschemaversion%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bpubid%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bsubid%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E15%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BA0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B85CD9161-5659-42CC-A501-BB43072B0045%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EHere%20we%20have%20new%20schema%20versions%20after%2015.%20So%20we%20will%20apply%20all%20the%20schema%20changes%20until%20schemaversion%20becomes%2022.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWhat%20we%20saw%20was%20schemaversion%20was%20not%20getting%20updated%20on%20the%20subscriber.%20It%20remained%2015%20after%20every%20Merge%20agent%20completion.%20That%20was%20the%20reason%20schema%20changes%20were%20getting%20applied%20repeatedly.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWe%20Checked%20what%20profile%20is%20used%20by%20the%20Merge%20agent.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26gt%3B%26gt%3B%26gt%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ERight%20click%20on%20the%20subscription%20and%20click%20on%20Agent%20Profile%3A%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorTaiyeb_Zakir_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MergeProfile.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197310iB22FEDB93AB2DE06%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22MergeProfile.png%22%20alt%3D%22MergeProfile.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EIn%20this%20case%20we%20are%20using%20%22High%20Volume%20Server%20to%20Server%20Profile%22%20and%20with%20this%20ParallelUploadDownload%20is%20set%20to%201.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EParallelUploadDownload%26nbsp%3B%20is%20a%20deprecated%20feature.%20There%20is%20a%20known%20issue%20where%20we%20don%E2%80%99t%20update%20schema%20water%20marks%20if%20ParallelUploadDownload%20is%20set%20to%201.%26nbsp%3BWe%20changed%20Merge%20agent%20profile%20to%20use%20%22Default%20agent%20profile%22%20or%20change%20Merge%20agent%20Job%20and%20add%20%22-ParallelUploadDownload%200%22.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ERan%20the%20Merge%20agent%20again.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ENow%20the%20schema%20watermark%20was%20updated%20successfully%20on%20the%20subscriber%20and%20the%20schema%20changes%20were%20not%20getting%20reapplied.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eselect%20max(schemaversion)%20from%20sysmergeschemachange%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E--22%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eselect%20schemaversion%2C%20pubid%2C%20subid%20from%20sysmergesubscriptions%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Ewhere%20pubid%26lt%3B%26gt%3Bsubid%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3Eschemaversion%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bpubid%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bsubid%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E22%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BA0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B85CD9161-5659-42CC-A501-BB43072B0045%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1448792%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ETips%20%26amp%3B%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Recently worked on an interesting case. Customer had an existing Merge Replication and it was working fine.

They added a new article to the existing Publication and ran the Snapshot Agent.

Now Every time Merge agent runs we see that Schema changes were getting re-applied.

 

MergeApplyingSchemaChanges.png

At the start of a synchronization, the schemaversion of the subscription is checked against sysmergeschemachange by the Merge Agent, and schema changes are applied to the subscriber accordingly.

 

Ran these queries on the subscriber

>>>

select max(schemaversion) from sysmergeschemachange

--22

 

select schemaversion, pubid, subid from sysmergesubscriptions

where pubid<>subid

 

schemaversion        pubid        subid

15        A0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83        85CD9161-5659-42CC-A501-BB43072B0045

 

Here we have new schema versions after 15. So we will apply all the schema changes until schemaversion becomes 22.

What we saw was schemaversion was not getting updated on the subscriber. It remained 15 after every Merge agent completion. That was the reason schema changes were getting applied repeatedly.

 

We Checked what profile is used by the Merge agent.

>>>

Right click on the subscription and click on Agent Profile:

 

MergeProfile.png

In this case we are using "High Volume Server to Server Profile" and with this ParallelUploadDownload is set to 1.

ParallelUploadDownload  is a deprecated feature. There is a known issue where we don’t update schema water marks if ParallelUploadDownload is set to 1. We changed Merge agent profile to use "Default agent profile" or change Merge agent Job and add "-ParallelUploadDownload 0".

 

Ran the Merge agent again.

Now the schema watermark was updated successfully on the subscriber and the schema changes were not getting reapplied.

 

select max(schemaversion) from sysmergeschemachange

--22

 

select schemaversion, pubid, subid from sysmergesubscriptions

where pubid<>subid

 

schemaversion        pubid        subid

22        A0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83        85CD9161-5659-42CC-A501-BB43072B0045