Immediate Updating Considerations

SQL Replication

previous page next page
Replication

Immediate Updating Considerations

Immediate updating can be enabled using SQL Server Enterprise Manager, or programmatically by using Transact-SQL system stored procedures or SQL-DMO.

Immediate Updating Restrictions

The following restrictions exist with immediate updating:

  • Published tables must have a uniqueidentifier column. The uniqueidentifier column MSrepl_tran_version is added to the publishing table automatically. If the MSrepl_tran_version column already exists on the publishing table, it will be used.

  • INSERT statements used to add rows of data to a table must include a column list.

  • If you create two or more articles on the same table in a publication database and then create subscriptions to those articles in the same subscription database, the following additional restrictions apply:
    • If multiple articles based on the same table are in one publication enabled for immediate updating, you cannot create an immediate updating subscription to this publication. Warning message 21293 will be issued.

    • If multiple articles based on the same table are in different publications and you want to create subscriptions to all publications in the same subscription database, only one of the subscriptions can be immediate-updating.
  • 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.

  • If the snapshot or transactional publication allows immediate updating subscriptions and the publication has a column filter, you cannot filter non-nullable columns without defaults from the publication.

  • Subscribers using immediate updating subscriptions cannot republish data to other Subscribers.
Data Modifications at Subscribers

When modifying data at Subscriber sites using the immediate-updating Subscribers option, consider the following issues:

  • The Subscriber should not update timestamp or identity values directly. Those values are generated by the Publisher as part of the 2PC transaction between the Publisher and Subscriber. Default constraints are applied to these columns at the Subscriber.

  • The Subscriber cannot update or insert text or image values because they cannot be read from the inserted or deleted tables inside the trigger. Similarly, the Subscriber cannot update or insert text or image values using WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher. Instead, you could partition the text and image columns into a separate table and modify the two tables within a transaction. You could use merge replication to synchronize these values if updates to text or image columns are needed at the Subscriber. You can be assured there are no conflicts if all updates follow this guideline because the update of the text or image table cannot occur unless the main table was updated, which is protected by 2PC.

  • When loopback detection is in effect, modified rows are not sent back to the originating Subscriber (thereby reducing overhead).

  • It is recommended that Subscriber tables have at least a unique index and preferably a primary key for snapshot replication. This is required for transactional replication.

  • Although snapshot replication without immediate updating does not require the use of primary keys in a table, snapshot replication with immediate updating or transactional replication with immediate updating requires you to use primary keys on publishing tables. (Transactional replication always requires the use of primary keys on publishing tables).

  • If the subscription database is horizontally filtered and there are rows in the partition that existed at the Subscriber separate from the data propagated to the Subscriber by the Publisher, and that partition is not at the Publisher, the Subscriber cannot update the pre-existing rows. Attempting to update these rows returns an error. The rows should be deleted from the table and added again.
Configuration Modes

The immediate updating option supports either dynamic RPC mode or static RPC mode for the 2PC connection from the synchronization triggers back to the Publisher. In dynamic RPC mode, synchronization triggers connect dynamically to the Publisher, using a supplied server name, login, and password. This mode offers increased security for users who do not want a statically defined linked server/remote server connection from a Subscriber to Publisher. It is also easier to use when setting up push subscriptions because the Publisher does not have to be predefined at the Subscriber. In static RPC mode, synchronization triggers connect to the Publisher over a statically defined server name defined as a linked server or remote server in the sysservers table. This entry is added by an administrator at the Subscriber server.

The configuration mode is set automatically when creating push or pull subscriptions:

  • When setting up a push subscription using the Push Subscription Wizard in SQL Server Enterprise Manager or the sp_addsubscription stored procedure, the default configuration uses dynamic RPC at the Subscriber. The dynamic RPC defaults to using the sa login with no password. This is done to avoid sending logins or passwords over the network, and should be changed at the Subscriber using sp_link_publication.

  • When setting up a pull subscription using the Pull Subscription Wizard in SQL Server Enterprise Manager, the user chooses the desired configuration mode. If you choose static RPC, the Publisher must be configured as a linked server or remote server at the Subscriber. If you choose dynamic RPC, you must supply a login and password that the synchronization triggers will use to connect to the Publisher.

  • When setting up a pull subscription using stored procedures, you need to explicitly call sp_link_publication after calling sp_addpullsubscription at the Subscriber.
User-Defined Triggers

If you are adding user-defined, cascading triggers to tables that are published and allow immediate updating, you can place the triggers at either the Publisher or Subscriber. Adding the triggers at the Publisher requires no special programming considerations. For example, you may have two tables, customer and orders, where customerid is a primary key in the customers table and a foreign key in the orders table. You can use a user-defined trigger on the customers table to cascade changes to the customerid in the orders table. Updating the customerid in the customers table at the Subscriber causes the immediate updating trigger to propagate the update to the Publisher. When the update is applied to the Publisher, the user-defined trigger fires at the Publisher, and cascades the update to the orders table at the Publisher. When the Distribution Agent runs, the update to the orders table is propagated down to the Subscriber. The cascaded changes are reflected accurately at the Subscriber, but with some latency because the orders table is not immediately up to date.

If your application requires that the cascaded table at the Subscriber immediately reflect the change in the cascading table (that is, avoid the latency of the round-trip to the Publisher), you also can add the cascading triggers at the Subscriber. However, when you add user-defined triggers at both the Publisher and the Subscriber, both sets of triggers must be created using the NOT FOR REPLICATION option. With the NOT FOR REPLICATION option active, an update to one of the tables at the Subscriber is cascaded to the other table by the user-defined trigger and then propagated to the Publisher by the immediate-updating triggers on each table. Because the user-defined cascading triggers at the Publisher are marked NOT FOR REPLICATION, these triggers do not fire.

Note  SQL Server 2000 replication supports the automatic transferring of triggers from the table at the Publisher to the table at the Subscriber; however, they will not be marked automatically as NOT FOR REPLICATION on the Subscriber, which has to be done manually. The triggers will be marked as NOT FOR REPLICATION if that is how they are defined on the Publisher.

You can also add user-defined triggers to update columns in the row currently being modified. Programming insert and update triggers is challenging because the immediate updating triggers may also need to update the same row. For example, an immediate updating trigger must insert the new timestamp or identity value received from the Publisher as part of a two-phase-commit transaction.

If both the user-defined trigger and the immediate updating trigger apply an update to the same row and you have not included a subroutine for special case handling, the transaction could terminate. Without special handling, the update process continues in a loop with each trigger update firing the other trigger until the maximum nesting level (32) is reached and the transaction terminates.

To avoid this situation, you must allow immediate updating insert and update triggers to fire before any user-defined triggers. The user-defined trigger should determine if it is being fired in the context of an immediate updating trigger and, if so, terminate without firing. Add the following lines of code to the beginning of the trigger:

DECLARE @retcode int, @trigger_op char(10)
EXEC @retcode = sp_check_for_sync_trigger @table_id, @tablename sysname, @trigger_op OUTPUT
IF @retcode = 1 RETURN
previous page start next page