Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
what can we expect in transactional replication when a table is truncated etc etc?

Answered what can we expect in transactional replication when a table is truncated etc etc?

  • Friday, November 16, 2012 4:26 PM
     
     
    Sign In to Vote
    0

    Hi.  Can the community give me a preliminary taste of what I'm in for using transactional replication when tables are truncated/reloaded/dropped or existing table formats change?   The source system is pretty mature.  Unfortunately we do not use PKs on a large number of our tables.  What if a source db is dropped?  Is recovery full on source dbs expected as I suspect?

    I dont want to spend money on a deeper dive until some questions like this are answered at a high level.




    • Edited by db042188 Friday, November 16, 2012 4:35 PM added reloaded
    • Edited by db042188 Friday, November 16, 2012 4:37 PM added source db dropping
    • Edited by db042188 Friday, November 16, 2012 4:40 PM recovery full question
    • Moved by Tom Phillips Friday, November 16, 2012 8:46 PM Replication question (From:SQL Server Database Engine)
    •  
     

All Replies

  • Friday, November 16, 2012 5:25 PM
     
     
    Sign In to Vote
    0
    it already looks like recovery must be full or "bulk".   From what I gathered, bulk has less overhead but presents more risk.
     
  • Friday, November 16, 2012 8:58 PM
     
     Answered
    Sign In to Vote
    0

    You cannot drop a database with an active publication.

    You cannot run the TRUNCATE TABLE command on a replicated table, you will recieve the error:

    Msg 4711, Level 16, State 1, Line 1
    Cannot truncate table because it is published for replication or enabled for Change Data Capture.

    The recovery model does not affect replication, anything can be used.  The log reader copies the Replicated transactions to the distribution database and then markes as complete and ready for reuse.

    Schema changes are logged, so they transfer to the subscriber. Please see: http://technet.microsoft.com/en-us/library/ms151870.aspx  However, if you add a new column it must be NULLable.


    • Edited by Tom Phillips Friday, November 16, 2012 8:59 PM
    • Marked As Answer by db042188 Monday, November 19, 2012 5:08 PM
    •  
     
  • Saturday, November 17, 2012 10:42 PM
     
     Answered
    Sign In to Vote
    0

    Hi there,

    Replication is a very cool. if you have transactional replication set up what every you do on publisher will reflect on subscriber.

    If you delete records from a table successfully on publisher it will reflect on subscriber.

    You may find some issues if you have identity columns in your schema while inserting data. Please check my answer at the below post.

    http://social.technet.microsoft.com/Forums/en-US/sqlreplication/thread/1115ec1d-f26e-486e-8e0e-b32c79943a99

    thanks

    kumar

    • Marked As Answer by db042188 Monday, November 19, 2012 5:07 PM
    •  
     
  • Monday, November 19, 2012 2:37 PM
     
     
    Sign In to Vote
    0

    Thx Gentlemen.  Can I assume that when a table needs to be truncated or a db dropped, and they are involved in a publisher role, that replication needs to be turned off temporarily by me?   Are there any other commands for which I'll get an error (or problem) while replication is turned on (beside the potential identity issue mentioned by Kumar)?

    This sounds a bit risky, perhaps not as fool proff as I expected .  If its up to a person to turn it off and then back on because a truncate is necessary, then its possible certain updates will be missed if it isnt turned back on at the right time.   I wouldnt want to do a replication snapshot more than once to sync things up.   It sounds like if a truncate is done on the publisher side, it would make sense for me to also do one on the subscriber side just to keep things completely in sync.   Same with a drop db and subsequent create tables.   I'm guessing if I dont that the replication software, after turned back on and reload begins on the publisher, would attempt to simply insert the same records into my subscriber even if they already exist there.   

     

     
  • Monday, November 19, 2012 3:05 PM
     
     Answered
    Sign In to Vote
    0

    Please see.  It should answer all your questions.

    http://msdn.microsoft.com/en-us/library/ms151740.aspx

    You can run DELETE FROM table (which is logged), instead of TRUNCATE TABLE to remove all rows. 

    • Marked As Answer by db042188 Monday, November 19, 2012 5:07 PM
    •  
     
  • Monday, November 19, 2012 5:07 PM
     
     
    Sign In to Vote
    0

    Thx again.  Looks like PKs are critical to the process.  And that in a number of ways, replication is not a "lights out" technology.  

    Also, the guid that sql adds to the subscriber tables worries me a bit in that last I looked, guids are 30% slower than integers in certain kinds of sql searches.   But that was some time ago.   And I'm not sure how sql replication uses this guid. 

    Also, I'm no expert but I believe ssis can get into a weird meta data state when it detects a new column like this guid.  So I start wondering if we'd be faced with running our etl slightly different in our qa environment.   Even if it comes down to just opening and closing all oledb destinations to pacify ssis.   

    For now, our interests may be best served by running our etl in parallel.   One etls set's destination would be production.  The other would be a qa environment for which I'm contemplating this effort.