INF: New Trace Flag to Enable Singleton Update for Transactional Replication
This article was previously published under Q302341
This article has been archived. It is offered "as is" and will no longer be updated.
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:
238254 INF: UPDATE Statements May be Replicated as DELETE/INSERT PairsOrganizations 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: NOTE: You must repeat this step every time you stop and restart SQL Server.
DBCC TRACEON (8207, -1)
- If you want to have the 8207 trace flag set ON permanently, then:
- Stop the MSSQLServer service.
- Add -T8207 to the startup parameters. To add -T8207, from SQL Enterprise Manager:
- Right-click the server name, and then click Properties.
- In the Properties dialog box, click the General tab, and then click Startup Parameters.
- Add the trace flag.
- Start the MSSQLServer service.
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.
REFERENCESFor more information, refer to the "Trace Flags" and the "Using Startup Options" topics in SQL Server Books Online.
Article ID: 302341 - Last Review: 12/06/2015 03:30:31 - Revision: 3.2
Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Enterprise Edition
- kbnosurvey kbarchive kbbug kbfix kbinfo kbsqlserv2000sp1fix KB302341