I have the following challenge at hand.
Situation : I have a central database and 5 other regional database that get a small percentage of data from the central database. We replicate a total of 67 snapshots. Some are fast refresh others cannot be since they have complex queries associated with them.
Replication takes place every 2 hours.
Problem with current setup : We like to make the replication intervals as close to real time as possible. Secondly i seem to have a problem with locks on the mlog$ tables when more than one site tries to replicate the same data at the same time. Thirdly replicating to 5 sites puts a strain on the performance of the central database.
Any thoughts to help me with my challenge
By the way is my thinking right to say that snapshot replication is a pull and advanced replication is a push. ????
Originally posted by Vishaalc Some are fast refresh others cannot be since they have complex queries associated with them.
If you want 'almost real time' data, maybe you should rethink these complex queries.
That's what I did, I have fast refresh on all tables in a under 2 minutes period. Because of not using complex queries I have 2 tables with more data than I need, but in my environment this is not a problem (of security or integrity). Even with excessive data being refreshed, it's faster, it's putting less overhead on network and less work on master database than it would with full refresh.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
1 Consider an interium database between the central DB and 5 other Reg DBs.
Replicate from central DB to a interium DB only. Then replicate interium db to all other 5 reg dbs. This way the load on the central db will be minimized.
The option of changing all the snapshots to fast refresh means i would b changing one table to 100 000 records to a table of 1.6 million records which would severaly impact performance on the regional sites which is a definite no - no i am trying with test servers to get a fast refresh situation for all snapshots but so far with no luck to an acceptable performance.
Advanced replication was my thought but with the same result as as the above leaves me with no option unless it would be possible to get advanced replication on a view that minimises the data is this at all possible ? or more so practical.
And thanx for the thought of the additional database i had that thought as well My thought was advance replicate to this database and manipulate the data somehow on this server to give the regional servers less data and more performance Anymore thoughts on this