Selecting the Appropriate Type of Replication
Microsoft SQL Server offers three types of replication. Each type of replication is suited to different application requirements. Depending on the needs of your application, you can use one or more types of replication in a topology:
- Snapshot replication
- Transactional replication
- Merge replication
To help you select the appropriate type of replication, this topic provides information about:
- Replication scenarios
This section briefly describes a number of common use cases for replication, with links to more detailed descriptions. - Types of replication
This section describes the application requirements for which each type of replication is suited. - Updating data at Subscribers
This section describes the options available for applications that require updates to data at the Subscriber.
We recommend first reading through the scenario descriptions to find the scenario that best matches your application requirements, and then clicking the link for more information. If you cannot find a close match for your business requirements or you want additional information about the types of replication, read "Types of Replication." If your application requires updates at one or more Subscribers, read "Updating Data at Subscribers" to determine the appropriate technology to use.
Replication Scenarios
Replication scenarios can be divided into two broad categories: replicating data in a server to server environment and replicating data between server and clients. The server to server scenarios are implemented using transactional replication (and sometimes snapshot replication); the server and client scenarios are implemented using merge replication.
Server to Server Scenarios
Data is typically replicated between servers to support the following applications and requirements:
| Scenario | Description |
|---|---|
Improving scalability and availability |
Maintaining continuously-updated copies of data allows read activity to be scaled across multiple servers. The redundancy resulting from maintaining multiple copies of the same data is crucial during planned and unplanned system maintenance. For more information, see Improving Scalability and Availability. |
Data warehousing and reporting |
Data warehouse and reporting servers often use data from online transaction processing (OLTP) servers. Use replication to move data between OLTP servers and reporting and decision support systems. For more information, see Data Warehousing and Reporting. |
Integrating data from multiple sites |
Data is often "rolled up" from remote offices and consolidated at a central office. Similarly, data can be replicated out to remote offices. For more information, see Integrating Data from Multiple Sites (Server). |
Integrating heterogeneous data |
Some applications depend on data being sent to or from databases other than Microsoft SQL Server. Use replication to integrate data from non-SQL Server databases. For more information, see Integrating Heterogeneous Data. |
Offloading batch processing |
Batch operations are often too resource intensive to run on an OLTP server. Use replication to offload processing to a dedicated batch processing server. For more information, see Offloading Batch Processing. |
Server and Client Scenarios
Data is typically replicated between servers and clients (including workstations, laptops, tablets, and devices) to support the following applications:
| Scenario | Description |
|---|---|
Exchanging data with mobile users |
Many applications require data to be available to remote users, including sales people, delivery drivers, and so on. These applications include customer relationship management (CRM), sales force automation (SFA), and field force automation (FFA) applications. For more information, see Exchanging Data with Mobile Users. |
Consumer point of sale (POS) applications |
POS applications, such as checkout terminals and ATM machines, require data to be replicated from remote sites to a central site. For more information, see Consumer Point of Sale (POS) Applications. |
Integrating data from multiple sites |
Applications often integrate data from multiple sites. For example an application that supports regional offices might require data to flow in one or both directions between regional offices and a central office. For more information, see Integrating Data from Multiple Sites (Client). |
Types of Replication
Snapshot Replication
The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications, but snapshot replication can also be used by itself. Using snapshot replication by itself is most appropriate when one or more of the following is true:
- Data changes infrequently.
- It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
- Replicating small volumes of data.
- A large volume of changes occurs over a short period of time.
Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.
Transactional Replication
Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:
- You want incremental changes to be propagated to Subscribers as they occur.
- The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
- The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
- The Publisher has a very high volume of insert, update, and delete activity.
- The Publisher or Subscriber is a non-SQL Server database, such as Oracle.
By default, Subscribers to transactional publication should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber. For more information, see the section "Updating Data at Subscribers" in this topic.
Merge Replication
Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:
- Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
- Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
- Each Subscriber requires a different partition of data.
- Conflicts might occur and, when they do, you need the ability to detect and resolve them.
- The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row changes only once at the Publisher to reflect the net data change (that is, the fifth value).
Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts.
Updating Data at Subscribers
The following types of replication and replication options allow you to make changes at a Subscriber and have those changes flow to the Publisher:
| Type of replication | Use when… |
|---|---|
Merge replication |
For more information, see Merge Replication Overview and How Merge Replication Works. |
Peer-to-peer transactional replication |
For more information, see Peer-to-Peer Transactional Replication. |
Transactional replication with updating subscriptions |
For more information, see Updatable Subscriptions for Transactional Replication. |
See Also
Other Resources
Implementing Replication
Implementation Considerations for Replication