Master-Subordinate Replication

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. Please see the patterns & practices guidance for the most current information.


Version 1.0.0

GotDotNet community for collaboration on this pattern

Complete List of patterns & practices


You are about to design a replication building block with a source and a target that meet the following requirements:

Replication set at the source is being updated by applications.

Replication set at the target is either read-only or updateable; however, any changes made to the target replication set will not be transmitted back to the source and may be overwritten by a subsequent replication.


How do you design the replication building block to transmit data from the source to the target and apply appropriate overwrite rules on the target?


Any of the following compelling forces would justify using the solution described in this pattern:

Simplicity. You have no reason to use a more complex solution, and you avoid any potential referential integrity problems.

The following enabling forces facilitate the move to the solution, and their absence may hinder such a move:

Tolerance of overwrites. If some data has been updated by an application on the target, it might eventually be replaced by data from the source.

Stability. The target applications require data that is stable over a predictable period, and may only change at defined points in time.


Copy data from the source to the target without regard to updates that may have occurred to the replication set at the target since the last replication.

Master-Subordinate Replication uses a single replication building block, as shown in Figure 1.


Figure 1: Master-Subordinate Replication

The replication link reads the data or its changes from the source, manipulates them for the target structure, and then writes them to the target.


The source database contains the replication set to be copied.


The Acquire service reads the contents of the replication set from the source to get the data to be transmitted. The source is either the data itself or a log of its changes. A replication that transmits only the changes is called an incremental replication. A replication that transmits the entire replication set on every transmission is called a snapshot replication. For a detailed description, see the corresponding patterns.

Hint: Acquiring only the changes is preferred when the following conditions are true:

The average number of changes during a transmission interval is relatively small compared to the size of the replication set.

Appropriate means to detect or record the changes on the source are present or can be implemented.

If these conditions are not true, transmission of the entire replication set is preferred.


The Manipulate service receives the data or its changes from Acquire. It can manipulate the data by performing operations on the fields of the current row, and then assigning the result to an output field.

Typical uses of such operations are:

Data type conversions

Concatenation of fields, for example first name and last name, into a single name field

Splitting fields, for example extracting first name and last name from a name field


The resulting rows of Manipulate are used by the Write services to update the target database in one of the following manners:

In a one-way replication with overwrite, any existing data on the target can be replaced by the new data.

In a one-way replication with no overwrite, the existing data on the target are compared to the new data. If they differ, the new data is appended as a new version. If they are identical, no write is needed.

Hint:Master-Subordinate Cascading Replication is the typical deployment of Master-Subordinate Replication.


The target is the database, where the transmitted and possibly manipulated data or changes are written.

Hint: You must ensure that the data is written to the target without side effects, such as double updates due to referential integrity. Hence, the target database must not fire any follow-up operations, such as triggers or cascade deletes, during the replication (if the transmission from the source includes those changes as part of its change information). However, if you need referential integrity or triggers for application updates that affect the replication set, you must prevent the replication from triggering these operations. You can achieve this, for example, by using a dedicated user or role for the transmission and by implementing the triggered operations so that they do not perform follow-up operations for the given user or role.


The following paragraphs show two examples of Master-Subordinate Replication. The first example deals with slow network connections and the second one shows how to divide operational and reporting databases.

Remote Read-Only Access Across a Low Bandwidth Connection

Some of your applications need read-only access to the data, but the network connection does not have enough bandwidth capacity to meet the required response times for user queries.

The solution is to replicate the database to a server in a network segment that can provide a higher bandwidth service for these applications. Because the applications only read the data, a master-subordinate replication will keep the target up-to-date. The replication should be designed as an incremental replication that transmits the changes from the source to the target as soon as possible.

The Master-Subordinate Transactional Incremental Replication pattern presents the design of such a particular master-subordinate replication.

Operational Database and Reporting Database

You have an operational database with ongoing updates by applications, and you need a stable view on last day's state of the database for reporting purposes. You decided to have two separated databases, one for the ongoing operations and one for reporting.

Master-Subordinate Replication allows you to transmit the data from the operational database to the reporting database every night. On the following day, the reporting database reflects the state of the operational database of the previous day. It will not change until the next transmission.

The transmission from the source to the target can be done with snapshots if the time period reserved for the transmission is sufficient to extract the snapshot from the source and to transfer it and write it to the target. The design of Snapshot Replication is presented in its own pattern.

Resulting Context

Because of the nature of a master-subordinate replication, changes to the target that have been made by applications exist until they are overwritten by a later transmission. If the replication only transmits the changes from the source, local changes on the target might remain even after a transmission. This occurs if the corresponding data has not been changed on the source, and thus the data is not transmitted and does not replace any data on the target.

After you decide to apply the Master-Subordinate Replication pattern, the following considerations will lead you to a more detailed design:

Transmission frequency. Define the appropriate timing of the transmission to meet the requirements.

Transmission volume. Define the right amount of data to transmit. For example, you could send all individual changes for a particular record, or just the aggregated result of them over the period, or the entire replication set.

Replication Unit. Define the smallest updating operation that will be transmitted from the source to the target. In very strong requirements, this will be a transaction as it has been executed on the source. A weaker but easier to achieve requirement is to move a changed record. For environments with a high risk of conflicts, it might also be an individual change of a column within a record.

Initiator. Decide whether the source will push the transmission or the target will pull it.

The use of this pattern inherits the benefits and liabilities from Data Replication and has the following additional benefits and liabilities:


Simplicity.Master-Subordinate Replication is substantially easier to design and implement than Master-Master Replication, which allows applications to update the target, but which has to cope with conflicts.

Stable view on the database contents. For a stable view on the data, you can transmit the data to dedicated target database, which will not experience any updates, as long as you need the data to be frozen. This allows you to investigate the stable data in the target database, while ongoing operations change the contents of the source database. Without a master-subordinate replication, you would have to enrich the schema to access historical data.

Backup.Master-Subordinate Replication with an automatic transmission to the target can be used as a means of backup. If the source needs to be recovered, the content of the target can be moved back to the source. Only the changes since the last transmission will be lost.


Growing storage needs with one-way replication with no target overwrite. Because this variant creates new versions whenever the transmitted data differs from the existing data on the target, space requirements constantly grow. You must monitor the storage consumption and eventually delete old versions, possibly after archiving them.

For more information, see the following related patterns:

Patterns That May Have Led You Here

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

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

Patterns That You Can Use Next

Master-Subordinate Snapshot Replication. This pattern presents a solution that transmits the whole replication set from the source to the target on each transmission.

Master-Subordinate 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.

Other Patterns of Interest

Master-Master Replication. This pattern presents an alternative to Master-Subordinate Replication, where the target may be written by applications, and such updates are transmitted back to the source.

Master-Subordinate Cascading Replication. This pattern shows how Master-Subordinate Replication can be applied iteratively in more complex topologies.

patterns & practices Developer Center

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.