Re: Replication in databases

On Sep 1, 11:56 am, M2Y <mailtoya..._at_gmail.com> wrote:
> If the isolation level is set to Serializable, why cant we just ship
> the transaction statements from the master to the backup and replay
> those transactions. Why do we adopt complicated solutions like log
> shipping and all such kind of things for replicating databases.

Some things to think about:

1. Queries aren't the only way data is loaded into a database; bulk data loading, for example.
2. Query shipping (as it's called) requires that identical versions of the database schema are maintained on every node. Dropping or creating a trigger in one place but not all places will affect query semantics. Talking to DBAs, they don't want to do this, as one frequent use for replication is to maintain an analytic-queries-only copy of some operational store.
3. What happens if the data on the two nodes is different? Consider a many-to-one replication from a number of retail stores to a central warehouse. Queries on child-node A (which has a sub-set of the overall database) might affect a different (much larger) data set on the parent-node.

Folk who design replication schemes need to consider a broader range of deployment scenarios than the one you've described. Frankly, in your situation. query shipping would be a better solution (assuming you have no view, triggers, stored procedures etc). But log sniffing hits a broader set of the cases customers seem to care about. Received on Fri Sep 05 2008 - 20:52:53 CEST