Administration of Immediate-updating Subscribers

The Immediate-updating Subscribers option is integrated seamlessly into both snapshot and transactional replication. These are optional properties of publications and subscriptions and can be enabled using SQL Server Enterprise Manager, or programmatically by using stored procedures or SQL-DMO.

Immediate-updating Subscribers Restrictions

The following restrictions exist with the Immediate-updating Subscribers option:

• Immediate-updating Subscribers is supported only at Microsoft® SQL Server™ version 7.0 Subscribers.
• Published tables must have timestamp columns. A timestamp column is added automatically if one does not exist.
• The timestamp column must be included in the vertical partition.
• 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 a single publication enabled for immediate-updating Subscribers, you cannot create an immediate-updating subscription to this publication.
  • If multiple articles based on the same table are in different publications and you want to create subscriptions to all publications in the same subscribing database, only one of the subscriptions can be immediate-updating.
• The publication access list must include at least one SQL Server Authentication account. The immediate-updating subscription connection to the Publisher (controlled by sp_link_publication) does not support Windows NT Authentication.

Data Modification at Subscriber Sites

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

• The Subscriber should not directly update timestamp or identity values. Those values are generated by the Publisher. 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. Use merge replication to synchronize these values. You can be assured there are no conflicts 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 originating Subscriber (thereby reducing overhead).
• Subscriber tables should have at least a unique index and preferably a primary key.
• Although snapshot replication doesn’t require the use of primary keys in a table, the Immediate-updating Subscribers option does require you to use primary keys.
• If the subscribing database is horizontally partitioned and there are rows in partition that exist at the Subscriber but 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 Subscribers 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 can be changed at the Subscriber using sp_link_publication.
• When setting up a pull subcription using the Pull Subscription Wizard in SQL Server Enterprise Manager, the user chooses the desired configuration mode. If you choose static RPC, the server name must already exists. 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 susbcription 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 Subscribers, 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 get 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, those triggers do not fire.

Note SQL Server replication does not support the automatic transferring of triggers from the table at the Publisher to the table at the Subscriber. All user-defined triggers must be created manually at the Publisher and Subscriber.

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 a immediate-updating trigger and, if so, terminate without firing. Add the following lines of code to the beginning of the your 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

See Also