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.