Replication Strategy - Am I on the wrong track?

Question

Replication Strategy - Am I on the wrong track?

aaustin

SSC Enthusiast

Points: 158
More actions
September 17, 2008 at 3:19 pm

#382688

I have a mission critical call switch that logs all activities and call transactions (call duration, which agent handled the call, etc.). The vendor produces an application which polls the call switch for the logs and then enters them into their appropriate tables. Each record is identified with a unique identifier assigned by the call switch. Our revenue is derived from these logs, so redundancy and accuracy is essential. We utilize multiple instances of the polling application on different machines which write to different databases.
My goal is to synchronize these databases to compensate for potentially missing records.
I have successfully established merge replication, but it is taking an incredible amount of time (project time to completion increases after each sync; the first sync after the snapshot was 2 hours, the second was 4 hours, today's was 8 days) which leads me to believe that I'm doing something WRONG. Am I making a configuration error or am I choosing the completely incorrect tool? Is the length of time due to SS2K5 attempting to resolve the conflicts (keep in mind that most, if not all records will already exist in the subscriber's database)? If so, can I circumvent this / instruct SS2K5 to ignore any INSERT fails due to duplicate primary keys? If not, is there another type of replication scheme or another method to accomplish my goal using SS2K5 built-in tools?
Thank you for your time!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chris_P SSCommitted Points: 1753 More actions · Answer 1

Hi.

Merge replication is the only built in way to easily sync these databases, unless you want to write some sort of custom solution which could get messy fast.

I suggest trying to tune Merge replication. I know you're using SQL 2005, but perhaps some of the tweaks included in this article would help.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/mergperf.mspx

Chris.

aaustin SSC Enthusiast Points: 158 More actions · Answer 2

Thanks for the link and the response, Chris. I read through the optimization procedures and read that the Merge Agent will retry a record that failed a constraint check repeatedly. I've been pouring over MSSQL manuals, but so far I have been unable to find a way (either through the UI or via T-SQL) to modify the number of retries the Merge Agent will attempt on a failed constraint check. Is it possible to perform my own PK constraint check in a trigger and then discard the Insert if the constraint check returns a match? Is this the wrong way to go about it / is there a better way? My reading leads me to believe that a BEFORE INSERT trigger still occurs after a constraint check (which leads me to believe that the Merge Agent will continue to retryretryretryretryretryretry the Insert); is there another type of trigger that I'm missing?

Andrew

Chris_P SSCommitted Points: 1753 More actions · Answer 3

Well... I think you COULD put an instead_of trigger on the table for the insert event, and then throw out the duplicate record or better yet, log it somewhere so that you can look into why this is happening.

Alternatively, if this is some sort of reporting table, you could drop the primary key and live with the dupes.

Both of these are workarounds. If you do this, I recommend you spend some time trying to figure out why you end up with the duplicate records.

Chris.

aaustin SSC Enthusiast Points: 158 More actions · Answer 4

Chris,

Let me clarify the setup a bit:

There is 1 call switch

There are 2 apps that read the same buffer on the switch

Each app writes to it's own database; the PK of each entry is a number assigned to it by the call switch

I'd like to compensate for potentially missing records in the databases by merging the two together. The standard case will be duplicate PKs; duplicate PKs (in this case) are a good thing as both applications are working properly.

Chris_P SSCommitted Points: 1753 More actions · Answer 5

Is the subscriber/merged db separate from the above dbs?

Chris.

aaustin SSC Enthusiast Points: 158 More actions · Answer 6

No; these DBs are the subscribed / merged DBs. I'd like to keep them 'live'. Would the scheme be easier to implement if they were? e.g. Instead of LIVEPUBLISHER LIVESUBSCRIBER?

Chris_P SSCommitted Points: 1753 More actions · Answer 7

Probably not if you are going with the instead_of trigger, although you might want to start out with a separate db to test it.

Any idea why you end up with duplicate PKs?

Chris.

aaustin SSC Enthusiast Points: 158 More actions · Answer 8

The PK is a RecID (record ID) that is assigned by the call switch. Each polling application gets the same data, so each subscriber database should have the same data. So each subscriber table has identical information, including PKs. Duplicate PKs mean that the applications recorded the same information which is a good thing.

I'll be testing the INSTEAD_OF triggers this weekend. Thank you very much for all of your help.

aaustin SSC Enthusiast Points: 158 More actions · Answer 9

It's been a while since the last activity here, but just in case anyone comes across this with a search:

I wasn't able to get around the above issues using Replication. I did solve this problem using a series of cascading stored procedures.

I linked the servers that held the DBs that needed to be synchronized.

The job manager executed a stored procedure for the database that I needed to synchronize.

That stored procedure then executed the synchronization logic for each of the tables (iterating through the records on each table; if the PKs matched then move on, otherwise add the missing record).

I then recreated this for each of the databases that I needed to synchronize.

There may be a more elegant solution out there, but this solution has worked well for the past ~3 months.

Thanks again to Chris for all of his help!