SQL Server Publish subscription release type

MicrosoftSQL Server provides three types of replication. Each type of replication is appropriate for the requirements of different applications. Depending on your application needs, you can use one or more types of replication in your topology:

• snapshot replication

• Transactional replication

• Merge replication

To help you select the appropriate replication type, this topic provides information about the following:

• Replication Scenarios

  This section provides a brief description of the many common scenarios of replication, as well as links to more detailed descriptions.

• Type of replication

  This section describes the application requirements that are appropriate for each replication type.

• Updating data at the Subscriber

  This section describes the available options for applications that need to update data at the Subscriber.

We recommend that you read through the scenario description first, identify the scenario that best matches your application requirements, and then click the link to see the details. If you cannot find a scenario that matches your business requirements, or if you want additional information about the type of replication, read copy type. If your application needs to be updated at one or more subscribers, read "Updating data at the Subscriber" to determine the appropriate technology to use.

Replication Scenarios

Replication scenarios can be divided into two main categories: replicating data in a server-to-server environment and replicating data between servers and clients. Server-to-server scenarios are implemented using transactional replication (sometimes with snapshot replication), and server and client scenarios using merge replication implementations.

Server-to-server scenarios

Data is typically replicated between servers to support the following applications and requirements:

Scheme	Description
Increased scalability and availability	By maintaining a constantly updated copy of the data, you can extend the read activity to multiple servers. During the execution of planned system maintenance and unplanned system maintenance, it is critical that multiple copies of the same data be maintained for data redundancy. For more information, see Improving scalability and availability.
Data warehouses and reports	Data warehouses and report servers typically use data from online transaction processing (OLTP) servers. Use replication to move data between an OLTP server and a reporting and decision support system. For more information, see Data warehousing and reporting.
Integration of data from multiple sites	Data is typically "remitted" from various remote offices and consolidated at Headquarters. Similarly, data can be replicated from Headquarters to remote offices. For more information, see Integrating data from multiple sites (servers).
Integrating heterogeneous data	Some applications rely on data sent to or from databases that are not MicrosoftSQL Server. Use replication to integrate data from non-SQL Server databases. For more information, see Integrating heterogeneous data.
Uninstall Batch Processing	Batch operations cannot run on an OLTP server because they typically consume too much resources. Use replication to offload the batch task to the dedicated batch server. For more information, see Uninstalling Batches.

Server and Client Scenarios

Data is typically replicated between the server and the client (including workstations, laptops, tablets, and settings) to support the following applications:

Scheme	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) applications, sales automation (SFA) applications, and field automation (FFA) applications. For more information, see exchanging data with mobile users.
Consumer Point-of-sale (POS) applications	POS applications, such as billing terminals and ATM machines, require that data be copied from the remote site to the central site. For more information, see Consumer Point of Sale (POS) applications.
Integration of data from multiple sites	Applications typically integrate data from multiple sites. For example, applications that support regional offices may require data to flow in one direction or two directions between regional offices and headquarters. For more information, see Integrate data from multiple sites (clients).

Type of replicationsnapshot replication

Snapshot processing is typically used to provide initial datasets and database objects for transactional and merge publications, but snapshot replication can also be used for itself. Using snapshot replication itself is most appropriate when one or more of the following conditions are met:

• Data is rarely changed.

• Allows for a period of time a copy of data that has a relative publisher that is obsolete.

• Copy small amounts of data.

• A large number of changes occur in the short term.

Snapshot replication is most appropriate when the amount of data changes is large, but changes are rare. For example, if a sales organization maintains a list of product prices and these prices are to be fully updated one or two times a year at a fixed time, it is recommended that the full data snapshot be replicated after the data changes.

Transactional replication

Transactional replication is typically used in server-to-server environments where transactional replication is appropriate in the following scenarios:

• You want the incremental changes to propagate to subscribers when they occur.

• When changes are made from the Publisher to the Subscriber, the application needs a short lag between the two.

• The application needs to access the intermediate data state. For example, if a row is changed five times, transactional replication will allow the application to respond to each change (for example, firing a trigger) rather than responding to the row's final data change.

• The publisher has a large number of INSERT, update, and delete activities.

• The Publisher or subscriber is not a SQL Server database (for example, Oracle).

By default, transactional publishing subscribers should be read-only, because changes are not propagated back to the publisher. However, transactional replication does provide the option to allow updates at the Subscriber. For more information, see the "Update data at the Subscriber" section in this topic.

Merge replication

Merge replication is typically used in server-to-client environments. Merge replication applies to the following scenarios:

• Multiple Subscribers may update the same data at different times and propagate these changes to the Publisher and other Subscribers.

• Subscribers need to receive data, make changes offline, and then synchronize changes with the Publisher and other Subscribers.

• Each subscriber requires data from different partitions.

• Conflicts can occur and you need to have the ability to detect and resolve conflicts when a conflict occurs.

• The application requires the result of the final data change, rather than accessing the intermediate data state. For example, if a row at the Subscriber changes five times before the subscriber synchronizes with the publisher, the line is changed only once at the publisher to reflect the final data change (that is, to the fifth value).

Merge replication allows different sites to work autonomously and merge updates into one consolidated result at a later time. Because updates are made on multiple nodes, the same data may be updated by the Publisher and multiple subscribers. As a result, conflicts may arise when merging updates, and merge replication provides multiple ways to handle conflicts.

updating data at the Subscriber

The following types of replication and replication options allow changes at the subscriber and flow these changes to the publisher:

Type of replication	When to use ...
Merge replication	There are a large number of subscribers. The data is copied to the mobile user. The replicated data is frequently updated at the Subscriber. Data filtering is required so that subscribers can receive data from different partitions. For more information, see Merge Replication overview and how merge replication works.
Peer Transactional replication	Replication is used to improve scalability and availability. Requires the shortest latency time. The data is not partitioned between subscribers. Conflicts usually do not occur, but if they do, they will be detected. For more information, see Peer Transactional replication.
Transactional replication with Update subscriptions	There are a few subscribers. Replicated data is typically read-only at the Subscriber. In most cases, subscribers, distributors, and publishers are connected to each other (for immediate updating subscriptions). For more information, see Updatable subscriptions for transactional replication.

SQL Server Publish subscription release type