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

在发布服务器上修改更新设置为Singleton update，这个问题没有再重现，sql如下：

dbcc traceon (8207,-1)

DBCC TRACESTATUS(-1)

View products that this article applies to. This article was previously published under Q302341

On This Page

• 

  SUMMARY

• 

  MORE INFORMATION

  • 

    REFERENCES

Expand all | Collapse all

SUMMARY If you update any column that is part of a unique or clustered index, SQL Server...

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  (http://support.microsoft.com/kb/238254/EN-US/ ) INF: UPDATE Statements May be Replicated as DELETE/INSERT Pairs 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.

Back to the top

MORE INFORMATION You can enable trace flag 8207 on the publishing server by using either of the f...

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 clickStartup 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.

Back to the top

REFERENCES

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

Back to the top

APPLIES TO

• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 Enterprise Edition

Back to the top

Keywords:

kbbug kbfix kbinfo kbsqlserv2000sp1fix KB302341

Back to the top           SQL 2000 -> 2005 transactional replication, LOG Reader behavior changed?

•   Wednesday, October 10, 2007 10:20 AM

  

  Miro_BG

  

  Miro_BG

  0 Points 1 0 0 Recent Achievements First Forums Reply Miro_BG's threads View Profile 0 Points    

  

  0 Sign In to Vote

  Hi all,

  I had transactional replication model relying on SQL Server 2000 , and when i had had missing records on subscriber i just did UPATE on primary key on that table and that transactions moved from publisher to subscriber again, because update to primary key in SQL 2000 generates DELETE command and then INSERT command

  ,anyway,

  Now I am using SQL Server 2005 and that hint doesn't work, i think that some king of optimization has been made to Log reader agent in SQL 2005, i don't know .

  Can Someone tell me how to repeat transactions in the same or similar way as SQL 2000.

  • 

    Reply

  • 

    Quote

Answers

• Monday, January 07, 2008 4:28 PM

  

  Hilary Cotter

  

  Hilary Cotter

  MVP

  26,945 Points 10 4 2 Recent Achievements Forums Replies V Proposed Answerer I Forums Answerer IV Hilary Cotter's threads View Profile (MVP) 26,945 Points    

  

  

  0 Sign In to Vote

  That is correct, in SQL 2005 the update will not be deferred (ie split into a delete/insert pair).

  • 

    Reply

  • 

    Quote

All Replies

• Friday, October 12, 2007 6:29 PM

  

  Hilary Cotter

  

  Hilary Cotter

  MVP

  26,945 Points 10 4 2 Recent Achievements Forums Replies V Proposed Answerer I Forums Answerer IV Hilary Cotter's threads View Profile (MVP) 26,945 Points    

  

  0 Sign In to Vote use trace flag 8202

  -- here is a repro

  dbcc traceon(8202,-1)

  create database testupdatepk

  GO

  create database testupdatepkSub

  GO

  use testupdatepk

  GO

  sp_replicationdboption 'testupdatepk','publish','true'

  GO

  create table updatepk(pk int not null primary key, charcol char(20))

  GO

  sp_addpublication 'testupdatepk',@status='active'

  GO

  sp_addpublication_snapshot 'testupdatepk'

  GO

  sp_addarticle 'testupdatepk','updatepk','updatepk'

  GO

  sp_addsubscription 'testupdatepk','updatepk',@@servername,'testupdatepksub'

  GO

  exec sys.sp_startpublication_snapshot @publication = 'testupdatepk'

  GO

  select *From testupdatepksub.dbo.updatepk

  --its there, add a row

  insert into updatepk values(1, getdate())

  --its there, add a row

  select *From testupdatepksub.dbo.updatepk

  update updatepk set pk=2

  --commands issued are below - captured via profiler

  --exec [sp_MSdel_dboupdatepk] 1

  --exec [sp_MSins_dboupdatepk] 2,'Oct 12 2007  2:22PM '

  • 

    Reply

  • 

    Quote
• Monday, January 07, 2008 4:22 PM

  

  bb13

  

  bb13

  0 Points 1 0 0 Recent Achievements First Forums Reply bb13's threads View Profile 0 Points    

  

  0 Sign In to Vote

  We are currently using SQL Server 2000 transactional replication but the db will be migrated to 2005 in the near future.

  Is it correct that an update on a primary key in a SQL Server 2000 table being replicated (transactional) results in a Delete/Insert pair of transactions?  But an update on a primary key in a SQL Server 2005 table being replicated (transactional) is simply an Update transaction?

  Thank you.

  • 

    Reply

  • 

    Quote
• Monday, January 07, 2008 4:28 PM

  

  Hilary Cotter

  

  Hilary Cotter

  MVP

  26,945 Points 10 4 2 Recent Achievements Forums Replies V Proposed Answerer I Forums Answerer IV Hilary Cotter's threads View Profile (MVP) 26,945 Points    

  

  

  0 Sign In to Vote

  That is correct, in SQL 2005 the update will not be deferred (ie split into a delete/insert pair).

  • 

    Reply

  • 

    Quote
• Friday, January 25, 2008 1:49 PM

  

  bb13

  

  bb13

  0 Points 1 0 0 Recent Achievements First Forums Reply bb13's threads View Profile 0 Points    

  

  0 Sign In to Vote

  I think I misunderstood the answer earlier.  I thought what was being said is that in SQL Server 2005 deferred updates were not possible.

  I've now read that in SQL Server 2005 a deferred update is made into a singleton update by turning on the trace flag, using the DBCC traceon (8201, -1) command on the publication database.

  That indicates that in 2005 you may make a decision to either turn the deferred update into a singleton OR leave as deferred update. 

  dbcc traceon(8202,-1) is for deferred updates

  dbcc traceon(8201,-1) is for singleton updates

  Is that correct?

  If set for deferred updates, would all updates (either to PK column or any other columns) be paired insert/deletes or only updates to PK column?

  Any knowledge or information on this topic, is appreciated.

  • 

    Reply

  • 

    Quote
• Friday, January 25, 2008 6:04 PM

  

  gopal ashok

  

  gopal ashok

  972 Points 8 0 0 Recent Achievements New Blog Commentator New Blogger First Marked Answer gopal ashok's threads View Profile 972 Points    

  

  0 Sign In to Vote

  In SQL Server 2005, the default behavior is deferred updates will be send as updates except in couple of cases where it will be send as del\ins pair and there is no way to force this into singleton updates.

  1: Update PK of a single from a join

  2: Update PK of more than one row in a single statement

  In SQL Server 2005, 8201 really do not have any impact, 8202 on the other hand will replicate ALL updates as deferred updates.

  • 

    Reply

  • 

    Quote
• Friday, January 25, 2008 6:07 PM

  

  gopal ashok

  

  gopal ashok

  972 Points 8 0 0 Recent Achievements New Blog Commentator New Blogger First Marked Answer gopal ashok's threads View Profile 972 Points    

  

  0 Sign In to Vote

  Meant to say...

  In SQL Server 2005, 8201 really do not have any impact, 8202 on the other hand will replicate ALL updates as del\ins pairs

  • 

    Reply

  • 

    Quote
• Friday, January 25, 2008 6:50 PM

  

  bb13

  

  bb13

  0 Points 1 0 0 Recent Achievements First Forums Reply bb13's threads View Profile 0 Points    

  

  0 Sign In to Vote

  I think I understand now.

  In SQL 2000, updates to columns that do not participate in PK are treated as singleton (update) unless trace changed to 8202 after which they are treated as deferred (paired ins/delete), while updates to columns that participate in PK are ALWAYS treated as deferred (paired ins/del).

  In SQL 2005, its either all updates are treated as defered (paired ins/del) or all are treated as singleton (update) regardless of if column(s) being updated participate in PK or not.

  • 

    Reply

  • 

    Quote
• Friday, January 25, 2008 7:02 PM

  

  gopal ashok

  

  gopal ashok

  972 Points 8 0 0 Recent Achievements New Blog Commentator New Blogger First Marked Answer gopal ashok's threads View Profile 972 Points    

  

  0 Sign In to Vote

  In SQL 2000, updates to columns that do not participate in PK are treated as singleton (update) unless trace changed to 8202 after which they are treated as deferred (paired ins/delete), while updates to columns that participate in PK are ALWAYS treated as deferred (paired ins/del).

  -- Correct

  In SQL 2005, its either all updates are treated as defered (paired ins/del)    

  -- only if you use 8202

  or all are treated as singleton (update) regardless of if column(s) being updated participate in PK or not.

  -- yes, this is the default behavior except for the cases i mentioned above.

  • 

    Reply

  • 

    Quote
• Thursday, January 31, 2008 9:58 PM

  

  SES - SES

  

  SES - SES

  10 Points 1 0 0 Recent Achievements First Forums Reply SES - SES's threads View Profile 10 Points    

  

  0 Sign In to Vote

  Hello!

  I'm running two kind of replications one that is peer-to-peer merge replication and the latter 's subscribers are as well publishers via transactional replication to a central database.

  The transaction replication uses XCALL facility (which is overriden to provide particular information for the datawarehouse)

  However, the log reader is applying del/ins to some tables (8 tables out of 150).  updates to primary keys are occuring but they are singleton updates. 

  Anyone has a clue of why this weird (unwanted) behavior is taking place?

  I've tried:

  -dbcc opentran to check for open logs and did not find any.

  -dbcc traceon(8207,-1) which is a solution for SQL 2000 and as stated above SQL 2005 default behavior allows for singleton updates.

  - "enforce for replication" on integrity relationship is disabled.

  - no text or any other LOB data type is being replicated. 

  what is it that i'm missing?

  publishers/distributors are running on SQL 2005, some with SP1

  subscriber is running SQL 2005 Enterprise Edition with SP2 installed in a cluster environment

  note: merge publishers drop existing objects then replicates thru transactional replication!

具体参考：

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/7d1b8e01-f372-4d67-b213-5576345e71ec