INF: New Trace Flag to Enable Singleton Update for Transactional Replication

If you update any column that is part of a unique or clustered index, SQL Server implements the update as a deferred update. A deferred update means that the UPDATE is sent to the Subscriber as a pair of DELETE and INSERT operations. A deferred update is described in more detail in the following Microsoft Knowledge Base article: Organizations may find that sending updates to subscribers as DELETE and INSERT operations does not meet their business needs. For example, business rules might require update triggers to fire at the Subscriber. To accommodate these situations a new trace flag, 8207, is introduced in SQL Server 2000 Service Pack 1, which enables singleton updates for Transactional Replication. An update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.

You can enable trace flag 8207 on the publishing server by using either of the following methods:

• Run the following code from the SQL Server Query Analyzer:

  DBCC TRACEON (8207, -1)

  NOTE: You must repeat this step every time you stop and restart SQL Server.
  
  -or-
  
  
• If you want to have the 8207 trace flag set ON permanently, then:
  
  
  1. Stop the MSSQLServer service.
  2. Add -T8207 to the startup parameters. To add -T8207, from SQL Enterprise Manager:
     1. Right-click the server name, and then click Properties.
     2. In the Properties dialog box, click the General tab, and then click Startup Parameters.
     3. Add the trace flag.
  3. Start the MSSQLServer service.

From this point forward, an update to a unique column affects only one row (a singleton update) and is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.

Important: Typically, you use trace flag 8207 with read-only transactional replication. Do not use trace flag 8207 with updatable subscriptions if:

• A primary key update can occur at the subscriber.
  
  
• An update to a column that is included in a unique constraint can occur at the subscriber.
  
  
• An update to a column that is included in a unique index can occur at the subscriber.

REFERENCES

For more information, refer to the "Trace Flags" and the "Using Startup Options" topics in SQL Server Books Online.