Share via

Peer-to-Peer Transactional Replication

  • Article

Peer-to-peer transactional replication is designed for applications that might read or modify the data at any of the databases participating in replication. For example, an online shopping application is well suited to peer-to-peer replication: application performance can be improved by spreading out queries that read data across multiple databases. Additionally, if any of the servers hosting the databases are unavailable, an application can be programmed to route traffic to the remaining servers, which contain identical copies of the data. Read performance is improved because activity can be spread across all nodes. Aggregate update, insert, and delete performance for the topology is similar to a single node, because ultimately all changes are propagated to all nodes.

All nodes in a peer-to-peer topology are peers: each node publishes and subscribes to the same schema and data. Changes (inserts, updates, and deletes) can be made at all nodes. Replication recognizes when a change has been applied to a given node, preventing changes from cycling through the nodes more than one time.

Note

Custom applications that access and change data must ensure that inserts, updates, and deletes are partitioned, so modifications to a given row originating at one node are synchronized with all other databases in the topology before the row is modified by a different node. If an application performs concurrent conflicting modifications to a given row at multiple nodes, use merge replication, which is well suited for handling conflicts. For more information about merge replication, see Merge Replication Overview.

Standard transactional replication assumes read-only Subscribers and is hierarchical in structure: typically a single Publisher publishes data to one or more Subscribers. Standard transactional replication also supports a republishing hierarchy: updates are delivered from a Publisher to a set of republishing Subscribers, who in turn deliver updates to a final set of leaf-node Subscribers. Updating subscriptions offer the ability for Subscribers to push changes back to the Publisher, but the arrangement is still hierarchical because changes follow the hierarchical structure when moving between Subscribers and Publishers. In contrast to read-only transactional replication and transactional replication with updating subscriptions, the relationships between nodes in a peer-to-peer replication topology are peer relationships rather than hierarchical ones, with each node containing identical schema and data.

Note

Although updates can be made at multiple participating databases, it is important to understand that peer-to-peer topologies do not require or allow the immediate or queued updating publication options. For more information about immediate and queued updating, see Updatable Subscriptions for Transactional Replication.

Peer-to-Peer Topologies

The following scenarios illustrate typical uses for peer-to-peer replication.

Topology with two participating databases

Peer-to-peer replication, two nodes

The illustrations above each show two participating databases, with user traffic directed to the databases through an application server. This configuration can be used for a variety of applications, from Web sites to workgroup applications, providing the following benefits:

  • Improved read performance, because reads are spread out over two servers.
  • Higher availability if maintenance is required or in case of failure at one node.

In both illustrations, read activity is load-balanced between the participating databases, but updates are handled differently:

  • On the left, updates are partitioned between the two servers; if the database contained a product catalog, you could, for example, have a custom application direct updates to node "A" for product names beginning with A-M, and direct updates to node "B" for product names beginning with N-Z. Updates are then replicated to the other node.
  • On the right, all updates are directed to node "B". From there, updates are replicated to node "A". If "B" is offline (for example, for maintenance), the application server can direct all activity to "A". When "B" is back online, updates can flow to it, and the application server can move all updates back to "B" or keep directing them to "A".

Peer-to-peer replication can support either approach, but the central update example on the right is also often used with standard transactional replication.

Topologies with three or more participating databases

Peer-to-peer replication to dispersed locations

The illustration above shows three participating databases that provide the backend for a worldwide software support organization, with offices in Los Angeles, London, and Taipei. The support engineers at each office take customer calls and enter and update information about each customer call. The time zones for the three offices are eight hours apart, so there is no overlap in the workday: as the Taipei office closes, the London office is opening for the day. If a call is still in progress as one office is closing, the call is transferred to a representative at the next office to open.

Each location has a database and an application server, which are used by the support engineers as they enter and update information about customer calls. The topology is partitioned by time, so updates happen only at the node that is currently open for business; the updates then flow to the other participating databases. This topology provides the following benefits:

  • Independence without isolation: each office can insert, update, or delete data independently but can also share the data because it is replicated to all other participating databases.
  • Higher availability in case of failure or to allow maintenance at any of the participating databases.
    Peer-to-peer replication, three and four nodes

The illustration above shows the addition of a node to the three-node topology. A node could be added in this scenario:

  • Because another office is opened.
  • To provide higher availability to support maintenance or increase fault tolerance in the event of catastrophic failure.
  • Notice that in both the three and four node topologies, all databases publish and subscribe to all other databases, providing maximum availability in case of maintenance needs or failure of one or more nodes. As nodes are added, you must balance availability and scalability needs against performance and the complexity of deployment and administration.

Configuring Peer-to-Peer Replication

Configuring a peer-to-peer replication topology is very similar to configuring a series of standard transactional publications and subscriptions. The steps described in the following topic shows the configuration of a three-node system, similar to the configuration shown in the diagram above on the left.

To configure peer-to-peer transactional replication

Considerations for Using Peer-to-Peer Replication

Keep the following considerations in mind when using peer-to-peer replication:

General Considerations

  • Peer-to-peer replication is available only in SQL Server 2005 Enterprise Edition.
  • All participating databases should contain identical schema and data:
    • Object names, object schema, and publication names should be identical among the participating databases.
    • Publications must allow schema changes to be replicated (a setting of 1 for the publication property replicate_ddl, which is the default). For more information, see Making Schema Changes on Publication Databases.
    • Row and column filtering are not supported.
  • It is recommended that each node use its own distribution database. This eliminates the potential of having a single point of failure.
  • Tables and other objects cannot be included in multiple peer-to-peer publications within a single publication database.
  • A publication must be enabled for peer-to-peer replication before any subscriptions are created.
  • Subscriptions must be initialized using a backup or with the 'replication support only' option. For more information, see Initializing a Transactional Subscription Without a Snapshot.
  • Conflict detection and resolution are not provided. Updates for a given row should be made only at one database until it has synchronized with its peers. This can be accomplished, for example, by the application directing updates for a set of rows to a particular node.
  • Use of identity columns is not recommended. When using identities, you must manually manage the ranges assigned to the tables at each participating database. For more information, see the section "Assigning Ranges for Manual Identity Range Management" in the topic Replicating Identity Columns.

Feature Restrictions

Peer-to-peer replication supports the core features of transactional replication. It does not support the following options:

  • Initialization and reinitialization with a snapshot.
  • Row and column filters.
  • Timestamp columns.
  • Non-SQL Server Publishers and Subscribers.
  • Immediate updating and queued updating subscriptions.
  • Anonymous subscriptions.
  • Partial subscriptions.
  • Attachable subscriptions and transformable subscriptions (both deprecated in SQL Server 2005).
  • Shared Distribution Agents.
  • The Distribution Agent parameter -SubscriptionStreams and the Log Reader Agent parameter -MaxCmdsInTran.
  • The article properties @destination_owner and @destination_table.

The following properties have special considerations:

  • The publication property @allow_initialize_from_backup requires a value of 'true'.
  • The article property @replicate_ddl requires a value of 'true'; @identityrangemanagementoption requires a value of 'manual'; and @status requires that option '24' is set.
  • The value for article properties @ins_cmd, @del_cmd, and @upd_cmd cannot be set to 'SQL'.
  • The subscription property @sync_type requires a value of 'none' or 'automatic'.

Maintenance Considerations

Change History

Release History

12 December 2006
Changed content:
  • Added an explicit statement that this feature is available only in SQL Server 2005 Enterprise Edition.

17 July 2006
Changed content:
  • Updated information about which transactional replication features can be used with peer-to-peer replication.

See Also

Concepts

Strategies for Backing Up and Restoring Snapshot and Transactional Replication
Publication Types for Transactional Replication

Other Resources

How to: Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance