Two distinct DB server are located on remote sites and there is the need to replicate bidirectionally every update (ins, upd, del) on every table across the two database.
We had setup a CDR replication on the two nodes, but the volume of operations is too high and the outbound replication queue (on written db) fastly grows (in 6 min 200000 operations to replicate, empty after 20 min), a 1 minute delay in replication is out of specification rules.
HDR replication is not suitable for administrative reasons (need 24H support).

Seeking for any idea to work around the problem.

Thanks in advance

What are you trying to achieve? Are you really sure that the architecture is correct? Check how data is selected for replication... are you sure you want to replicate absolutely everything (bi-directional mirror)? Would it be easier to have 1 central db with remote access from your applications and forget about replication altogether? I keep coming back to question that architecture...maybe there's a good reason for it... but maybe not.

PS : It wasn't too clear from the previous post I made, but Informix CDR is NOT recommended to replicate entire databases, let alone try to make bi-directional mirror. It's designed for replicating parts of databases, eg, data copies into reporting/data-warehouse type databases to avoid the differing & conflicting requirements of OLAP and OLTP in a single installation.

first: we can talk about everything.....architecture, DBMS, etc....so i can fully validate current solution

we are trying to achieve complete indipendence for the two geographical sites with replication of the underlying data, so if site 1 go down, site 2 will continue to work and when site 1 go up it will be automatically synchronized to site 2.

Q:Check how data is selected for replication...
A:what do you mean? I'm not the DBA, are there more than one method to replicate a table other than to replicate every single update?

Q:are you sure you want to replicate absolutely everything (bi-directional mirror)?
Abviously only non-static tables are replicated and yes, bi-directional mirror, so any change is visible everywhere

Q:Are you really sure that the architecture is correct?
A:I need arguments to show if it's correct or not.

Q:Would it be easier to have 1 central db with remote access from your applications and forget about replication altogether?
A:Unfortunately, if the line between the two sites go down or if the logical structure of the unique DB is broken, nobody works (site 1, site 2). Moreover, users have time constraints on answers and local reading of data has better performance

Feel free to write every doubt or hint

Thanks in advance

Q: Do both sites work on the same data?




"...

Q:Are you really sure that the architecture is correct?
A:I need arguments to show if it's correct or not.

..."

A: It isn't working, so something is obviously wrong! CDR replication is specifically identified as not being good for full-db replication. Therefore , the architecture (or at least this technical solution) is wrong. Your results show that already.

What do you mean by "..or if the logical structure of the unique DB is broken.." ?

Do you mean (logical) data corruption? With your current solution this will be propogated to the other database anyway, so both sites will still see the same logical corruption...what's the difference?

Let's think about this a different way....

I assume we're not talking NORAD here, so 1 or both sites are not going to be completely wiped out. Your concern is about 1 of the databases becoming corrupt, right?

How about....

Each site has 2 servers in a cluster (high-availability). They share the same high-availability disks, which are hardware-mirrored and of course local (much faster than the remote software mirroring you're trying to achieve).
When 1 server or 1 mirror goes down at a site, the other server and mirror kicks in...all done locally. No worries about network, replication tools.... what do you think?

Q: Do both sites work on the same data?
A: Yes, probably not on the same row but surely tables (same tables on distinct DB)

Q: Are you really sure that the architecture is correct?
A: Currently only data replication is slow, everything else is OK

Q: CDR replication is specifically identified as not being good for full-db replication.
A: it's not a full-db replication, only n tables on m total tables are replicated. We have already reduced the number of tables to minimum

Q: Are you using synch or asynch replication?
A: asynch replication (when update is performed it's replicated)

I mean db-page failures......so our DBA says.

one of the problems is that site 1 is the collector for data coming from nord zone and site 2 is the collector for sud zone

if data from site1 are not remotely replicated on site2 and viceversa, how can everybody works on every data?

I don't understand what he means by db-page failures. I guess he means disk corruption?

In your current solution, what would happen to site1 after they had a problem? site2 has to do all of site2 work plus all of site1 work? is that practical?

"one of the problems is that site 1 is the collector for data coming from nord zone and site 2 is the collector for sud zone

if data from site1 are not remotely replicated on site2 and viceversa, how can everybody works on every data?"


But you just said that everyone doesn't work on same data. They have some common data (static), which can be copied to both sites by each site admin. The rest is region specific. If you need to report on customers that are in both nord and sud zones, then generate seperate reports and merge in Excel for very cheap solution or setup a datawarehouse that gets fed by data from the 2 sites when you have time.

Q: In your current solution, what would happen to site1 after they had a problem? site2 has to do all of site2 work plus all of site1 work? is that practical?
A: Yes, it's a possible emergency in the night (i.e. 22:00-08:00) but load is not so heavy in these hours

"But you just said that everyone doesn't work on same data. "
it's sounds like misunderstanding

They have some static data (not replicated). The rest is dinamic data (need replication)

Hi Again.



"Q: In your current solution, what would happen to site1 after they had a problem? site2 has to do all of site2 work plus all of site1 work? is that practical?
A: Yes, it's a possible emergency in the night (i.e. 22:00-08:00) but load is not so heavy in these hours"


Why do you think that the database/server could only go down during the night? It could just as easily go down during the day or not? Imagine that your disks crashed at site 1 at 11:00.... what would happen?