Merge Replicated Summarized Account Balance Column

[cheyney]

Here’s my dilemma: I have a system in which users have a balance. A user’s balance is basically the sum of their credits and debits, inputted as transactions. In order to avoid a sum calculation every time a user’s balance is requested (there may be MANY transactions), the balance is just a column on the user table, updated by a trigger on the transaction table.

This has worked fine for awhile.

Now I need to run this database in multiple locations. User accounts have to be homogeneous between locations, meaning decrements at location A have to result in the right balance at location B, and vice versa. I cannot just replicate the balance column, because if the replication link gets severed and updates for a single user happen at both locations, the most recent balance will become the total balance, and not take into account what happened on the other server.

I have set this up as merge replication, and enabled the triggers on replication and tried to remove the user balance column as a merge article, but it doesn’t seem to insert new users if the subscribers column set isn’t the same as the published article set (i.e. the subscriber would have a balance column, but the publication wouldn’t, so inserts don’t go through).

Any suggestions? How are these kind of systems usually set up? Should I be using transactional replication?

Any help will be greatly appreciated. Thanks in advance,

Cheyney

 

[cheyney]

I have read that Transactional replication probably isn't what I want to use. For one, both the servers are peers, with (foreseeably) the same amount of inserts/updates/deletes. Secondly, they may not have a consistently available communication link between them. Does this sound like a fair assessment? Is merge replication the right route here?

Cheyney