I'm trying to figure out what's the best way to design a active-active cluster that uses a replicated database. For network load balancing and failover, I can use Windows NLB. For database, I can use MySQL which can do master-master replication out of the box. This is the simple part.
Now my problem is how to program the messaging service, which is connected to a replicated database. What is the best way to go about designing it so that both services work with the same tables without conflict? On failure, the uncompleted transactions from the failed node must be assumed by the other node.
Here is how the messaging service works. Web clients will call the web service with a recipient and a message. The web service will insert the message into the database queue. When a specific condition is met, the message will be transmitted. This could happen within seconds or after a couple of days.
I've done extensive searches on the Internet but to no avail. Has anyone done something similar? Thanks.
If mysql isnt a requirement, i'd suggest a nosql memory optimized db like http://ravendb.net/, it is much more suited for high-availability than mysql. Just a thought.
Chat applications don't usually require transactions / intense reliability.
TSQL is more suited for financial applications, where reliability of data is the focus, for a chat you might be better off with a db where speed is the focus.
Just my 2 cents, since this seems more like a request for an opinion.
EDIT
Looks like raven supports ACID transactions, so even better.