Replication in databases

#41

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#42

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#43

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#44

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#45

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#46

On Sep 1, 11:56*am, M2Y <mailtoya... (AT) gmail (DOT) com> wrote:

Quote:

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.

#47

In the last exciting episode, M2Y <mailtoyahoo (AT) gmail (DOT) com> wrote:

Quote:

On Sep 2, 1:06*am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
M2Y wrote:
Hello,

I have a fundamental doubt regarding replication in databases.

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.

Thanks,
Srinivas

The statements might arrive at the slave in a different order and the
slave might serialize the transactions differently--for a start.

We can maintain an order of transactions, lets say, commit order and
let the backup replay the transactions in that order.

Well, yes, you can maintain an order, by some means.

That tends to impose requirements like log shipping, as you need some
means to impose the order.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/finances.html
When sign makers go on strike, is anything written on their signs?

#48

In the last exciting episode, M2Y <mailtoyahoo (AT) gmail (DOT) com> wrote:

Quote:

On Sep 2, 1:06*am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
M2Y wrote:
Hello,

I have a fundamental doubt regarding replication in databases.

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.

Thanks,
Srinivas

The statements might arrive at the slave in a different order and the
slave might serialize the transactions differently--for a start.

We can maintain an order of transactions, lets say, commit order and
let the backup replay the transactions in that order.

Well, yes, you can maintain an order, by some means.

That tends to impose requirements like log shipping, as you need some
means to impose the order.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/finances.html
When sign makers go on strike, is anything written on their signs?

#49

In the last exciting episode, M2Y <mailtoyahoo (AT) gmail (DOT) com> wrote:

Quote:

On Sep 2, 1:06*am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
M2Y wrote:
Hello,

I have a fundamental doubt regarding replication in databases.

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.

Thanks,
Srinivas

The statements might arrive at the slave in a different order and the
slave might serialize the transactions differently--for a start.

We can maintain an order of transactions, lets say, commit order and
let the backup replay the transactions in that order.

Well, yes, you can maintain an order, by some means.

That tends to impose requirements like log shipping, as you need some
means to impose the order.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/finances.html
When sign makers go on strike, is anything written on their signs?

#50

In the last exciting episode, M2Y <mailtoyahoo (AT) gmail (DOT) com> wrote:

Quote:

On Sep 2, 1:06*am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
M2Y wrote:
Hello,

I have a fundamental doubt regarding replication in databases.

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.

Thanks,
Srinivas

The statements might arrive at the slave in a different order and the
slave might serialize the transactions differently--for a start.

We can maintain an order of transactions, lets say, commit order and
let the backup replay the transactions in that order.

Well, yes, you can maintain an order, by some means.

That tends to impose requirements like log shipping, as you need some
means to impose the order.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/finances.html
When sign makers go on strike, is anything written on their signs?