There are 3 timesten nodes in our production setup .There will be around 5 million rows in each node initially which will gradually increase to about 10 million.Once our application moves to production, there will be around 50-70 transactions per second in each node , which need to be replicated on to the other node.
Initially we thought of going with Active-Standby-Subscriber replication.However in this case if active and standby node go down,then it will be a site failure case.So is Active-Active (Multimaster replication) configuration a good option ? Will data collision happen when replication happens in both directions?
Thanks in advance.
Multi-master replication is rarely a good idea. You will get data collisions unless you rigorously partition the workload. Conflict detection and resolution is not adequate to guarantee consistency over time. Recovery back to a consistent state after a failure is complex and error prone. I'd strongly advise against a multi-master setup, especially for a high volume system.
You seem to be concerned that 2 out of the 3 systems may fail resulting in a site outage. The likelihood of that is small if you have set things up with separate power etc. With the A/S pair based approach you would still have query capability if the two master systems failed. The chances of all 3 systems failing is not that much less than of just 2 failing in reality I would say (depending on the reason for the failure).