Notes on Database Pattern Relationships

These Patterns build on each to solve increasingly more difficult problems. Note that this background color indicates notes and annotations that I wrote.

Excerpt from the Master-Master Replication Pattern

Table 1: Conflict Resolution Methods

Conflict

Method

Description

Update, delete

Priority based

Every database has a priority assigned to it. The replication unit from the database with the higher priority prevails.

Update

Value based

Rules defined on the values decide the winning replication unit. For example, if the data contains a timestamp, the more recent timestamp prevails.

Update

Merge

The values of both source and target are merged by operations such as: min, max, sum, average

Update, delete

Overwrite

The data in the target database is overwritten in every case. Unlike the priority-based method, this method does not require conflict detection.

All

Manual

After a conflict is detected, the replication is suspended until the conflict is resolved manually.

Uniqueness

Discard

If you try to insert data that exists in the target database, the new data is ignored.

Uniqueness

Append sequence

If you try to insert data that exists in the target database, the key value is changed to a new value. Such data must be merged manually later.

 

 

 

The Master-Master Replication Pattern builds upon these more basic Patterns.

·  Move Copy of Data. This pattern is the root pattern of this cluster; it presents the overall architecture to maintain redundant data by asynchronous writing of copies of data eventually after the data has been updated.

·  Data Replication. This pattern presents the architecture of a replication.

These notes are written from the point of that you are here.

·  Master-Master Replication. This pattern discusses a situation in which changes occur to a common set of data at either source or target, and the other party wants such changes replicated to it.

 

Other Patterns of Interest (These Patterns are basically the same but in a different scenario) or just skip ahead to Patterns that you can use next .

·  Master-Slave Replication. This pattern presents the solution for a replication where the changes are replicated to the target without taking changes of the target into account. It will eventually overwrite any changes on the target.

·  Master-Slave Cascading Replication. This pattern discusses a replication deployment where many targets want to subscribe to the replication set that is being replicated

  • Master-Slave Snapshot Replication. This pattern presents a solution that transmits the whole replication set from the source to the target on each transmission.
  • Master-Slave Transactional Incremental Replication. This pattern presents a solution that transmits only the changes from the source to the target on a transaction-by-transaction basis.

    Patterns That You Can Use Next

    ·  Master-Master Row-Level Synchronization. This pattern presents a design for Master-Master Replication, which detects and resolves any conflicts at the row level.

    These "Implementing..." articles get right down to it.

  • Implementing Master-Slave Snapshot Replication Using SQL Server. This pattern shows how to implement Master-Slave Snapshot Replication by using Microsoft SQL Server.

    · Implementing Row-Level Synchronization Using SQL Server presents the pattern for implementing a row-level synchronization by using SQL Server.

  • Implementing Master-Slave Transactional Incremental Replication Using SQL Server. This pattern shows how to implement Master-Slave Transactional Incremental Replication by using SQL Server.

    Here are a few more from my MS Patterns collection (These go great with WebServices):

    Implementing Data Transfer Object in .NET with a DataSet

    Implementing Data Transfer Object in .NET with a Typed DataSet

    Acknowledgments

    [Fowler03] Fowler, Martin. Patterns of Enterprise Application Architecture. Addison-Wesley, 2003

    'The New Methodology' An interesting article about XP and agile methods by Martin Fowler