Planning for Replication Options
The replication options of immediate updating, queued updating, immediate updating with queued updating as a failover, and transforming replicated data require additional considerations during replication planning. If users do not need to update data at the Subscriber, consider using snapshot replication or transactional replication without immediate updating or queued updating options, and then replication will be easier to configure and administer.
Considerations for Immediate Updating or Queued Updating Subscriptions
Here are planning considerations for immediate updating or queued updating subscriptions:
- INSERT statements used to add rows of data to a table must include a column list.
- Subscribers using immediate updating or queued updating options cannot republish replicated data at the Subscriber.
- The Subscriber cannot update or insert text or image values. For more information, see Updatable Subscriptions.
- After a publication is enabled for either immediate updating subscriptions or queued updating subscriptions, the option cannot be disabled for the publication (although subscriptions do not need to use it); to delete the option, the publication must be deleted and a new one created.
- Snapshot replication does not require the use of primary keys in a table. However, transactional replication by itself or snapshot replication with any updatable subscriptions does require the use of primary keys.
- If you enable immediate updating and/or queued updating on a publication, you cannot also use transformable subscriptions. The Transform Published Data page will not show in the Create Publication Wizard if you have already chosen to use immediate updating and/or queued updating.
Additional Considerations for Immediate Updating Subscriptions
Immediate updating allows snapshot replication and transactional replication Subscribers to update the replicated data at the Subscriber and propagate those changes to the Publisher, which then propagates to all other Subscribers.
Consider the following when planning to use snapshot replication or transactional replication with immediate updating:
- A uniqueidentifier column is used to track updates. The uniqueidentifier column is added automatically to any tables used in the publication. The addition of this column requires INSERT statements to have column lists. If you used immediate updating in Microsoft® SQL Server™ version 7.0 and are upgrading to SQL Server 2000, you will need to subscribe to the publication again. For more information, see Replication and Upgrading.
- Using this option, the update is distributed and performed at both the Publisher and Subscriber using two-phase commit protocol (2PC): one locally at the Subscriber and one at the Publisher. This requires that the Publisher and the Subscriber making the change be available and connected.
- The immediate updating subscription connection to the Publisher (controlled by sp_link_publication) can use security mode 0 for SQL Server Authentication or 2 for linked server definition to create login mappings. The publication access list (PAL) must include at least one SQL Server Authentication account unless you use security mode 2 and configure delegation (it is possible to set up Windows Authentication in mode 2 by configuring delegation). You can make connections to the Publisher under Windows user accounts invoking the INSERT, UPDATE, and DELETE triggers at the Subscriber using delegation. To set up delegation, see sp_addlinkedsrvlogin.
Additional Considerations for Queued Updating Subscriptions
Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring a continuous connection to the Publisher.
When you create a publication with the queued updating option enabled and a Subscriber that is enabled for queued updating performs inserts, updates, or deletes on published data, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored.
Consider the following when planning to use snapshot replication or transactional replication with queued updating:
- Because the updates are propagated asynchronously to the Publisher, the same data may have been updated by the Publisher or by another Subscriber and conflicts can occur when applying the updates. You will need to choose an appropriate conflict resolution policy when creating the publication.
- For snapshot replication, tables should have, at least, a unique index and preferably a primary key. For transactional replication, tables must have a primary key.
- If the Subscriber database is partitioned horizontally and there are rows in the partition that exist at the Subscriber, but not at the Publisher, the Subscriber cannot update the preexisting rows. Attempting to update these rows returns an error. The rows should be deleted from the table and then added again.
- Manage identity values with identity ranges to ensure that different Subscribers have different identity values. For more information, see Replication Data Considerations.
Considerations for Transforming Published Data
You can transform data during the replication process by leveraging the capabilities of Data Transformation Services (DTS). Examples of transforming published data are creating custom horizontal and vertical data partitions and creating data transformations such as data type mappings, column manipulations, and string manipulations.
Consider the following when planning to transform replicated data:
- Snapshot data for a transformable subscription is limited to character mode only; native format (which is usually faster to apply) cannot be used with DTS.
- After a publication is enabled for transformable subscriptions, the option cannot be disabled; the existing publication must be deleted and a new one created, but if the option is enabled, subscriptions do not need to use it.
- You cannot use immediate updating or queued updating options with transformable subscriptions (transformations are mapped in one direction, from Publisher to Subscriber).
- Although using the Transform Published Data Wizard creates a DTS package, this type of DTS package is not available for execution outside of replication (from DTS Designer or at the command prompt). However, you can use a package created with DTS tools during replication of snapshot and transactional publications that allow transforming of published data.
- Introducing DTS transformations into replication adds overhead and reduces the distribution performance. The amount depends on the complexity of the transformation. It does not affect Log Reader Agent performance.