Download Database Replication Techniques: Microsoft SQL Server 2000 and Oracle 9i and more Slides Fundamentals of E-Commerce in PDF only on Docsity!
Other Approaches (cont’d)
-^ Read-one replica, write-all-available replicas^ –
Requires careful management of failures and recoveries
-^ E.g., Virtual partition algorithm^ –
Each node knows the nodes it can communicate with,^ called its view – Transaction T can execute if its home node has aview including a quorum of T’s readset and writeset – If a node fails or recovers, run a view formation protocol^ (much like an election protocol) – For each data item with a read quorum, read the latestversion and update the others with smaller version #.
Summary
-^ State-of-the-art products have richfunctionality.^ –
It’s a complicated world for app designers – Lots of options to choose from
-^ Most failover stories are weak^ –
Fine for data warehousing – For 24
×7 TP, need better integration with cluster node failover
Microsoft SQL Server 2000
-^ Publication - a collection of articles to subscribe to •^ Article – a horiz/vertical table slice or stored proc^ –
Customizable table filter (WHERE clause or stored proc) – Stored proc may be transaction protected (replicate on commit).Replicates the requests instead of each update.
-^ Snapshot replication
makes a copy
-^ Transactional replication
maintains the copy by propagating
updates from publisher to subscribers^ –^ Post-processes log to store updates in Distribution DB^ –^ Distribution DB may be separate from the publisher DB^ –^ Updates can be pushed to or pulled from subscriber^ –^ Can customize propagated updates using stored procedures
SQL Server 2000 (cont’d)
-^ Immediate updating subscriber –
Can update replicas
-^ Queued updates are synchronized with publisher via 2PC. –^ Triggers capture
local
updates and forward them to the Subscriber
(trigger must not fire for replicated updates from the publisher). – Subscriber’s forwarded update has before-value of row version-id. – Publisher checks that its copy of row has the same version-id. – If so, it performs the update and asyncrhonously forwards it toother subscribers – If not, it aborts the transaction (subscriber updated the row lately)
-^ Access control lists protect publishers from unauthorizedsubscribers •^ Merge replication-
described later (multi-master)
Oracle 9i
-^ Materialized view replica is driven by one master •^ Multi-master replication^ –
Masters replicate entire tables – Push updates from master to masters (synch or asynch) – Updates include before values (you can disable if conflicts areimpossible) – They recommend masters should always be connected
-^ Conflict detection^ –
Before-value at replica is different than in update – Uniqueness constraint is violated – Row with the update’s key doesn’t exist
