2

I do contracting with about 10 chicken farms. Each chicken has a GUID so we can track it through the entire life process (govt. requirement). We have 2 million chickens in the tables at any one time. One of my largest clients wants me to implement merge replication to another server so that they have 2 databases that look the same at any one time. SQL Server A will have data handling with users in the warehouses inputting data. SQL server B will have bulk data coming into it from grocery stores concerning our chickens and their sales information and will merge with Server A every 5 min. If the GUIDs are the same the information with the latest updated date will win the Resolver. We have about 1000 batch requests per second CPU is about 45% on a 16 Core Processor and 250GB of memory. On our Publication (server A) DB. Server B looks the same except we have the bulk info come in 2 times a day with about 10000 DML queries. We are trying to merge 900+ tables of which 45 of them are busy with Transactional processing all the time. The 865 or so tables get updated about once a week. We are getting consistent dead locks on our MergeGenHistory table. Look below. We have also set our generation_leveling_threshold as below UPDATE sysmergepublications SET generation_leveling_threshold = 200000 WHERE NAME = ‘datbaseName’. Updating statistics on the merge replication tables every 5 min, right before the merge agent runs.

We have created a custom agent those settings are also below. Everything not shown in the pick below is set at the default value.

mergeHenHistoryPIC

MSMerge_ContentsPIC

Custom agent PIC

  • I don't think Merge Replication is the appropriate approach here. You may have better luck with a formal Peer-to-Peer setup due to the fact that you're already working with a transactional replication configuration. A Peer-to-Peer setup would require enterprise edition though, but the benefit would be a single replication methodology as I think the mix/match approach in use is your biggest issue here. – John Eisbrener Aug 26 at 16:57
  • thank you John All tables above are involved in Merge Replication. there is no Transactional Replication. There is a lot of DML type activities going on in the 45 busy tables. I will look into Peer-to-Peer. – IMeitzen Aug 26 at 18:48
  • Sorry, I read "Transactional Processing" as "Transactional Replication". Either way, Peer-to-Peer is likely a more appropriate solution here. – John Eisbrener Aug 26 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.