SQL Server Performance

After schema update , Replication is breaking without raising error

Discussion in 'SQL Server 2005 Replication' started by Fais, Jul 13, 2009.

  1. Fais New Member

    This is SQL Server 2005 Transactional replication configuraiton.

    I am modifying the table schema at publisher by adding addtional columns on publisher table and the schema is replicated over to subscriver without any problems. But when I am inserting values into these new fields by using the below query:
    UPDATE dbo.event
    SET block_ = 1, fuel = f_used
    Values are not getting replicated over to subscriber. But the replication monitor does not report any problem even though after this update, replication is not functioning (i.e.) any modification not getting transmitted over to subscriber and breaks is in inconsistent state.

    Here is the table properties set for this article.
    EXEC sp_addarticle @publication = N'Publication_Db', @article = N'event', @source_owner = N'dbo', @source_object = N'event', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'event', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbo_event]', @del_cmd = N'CALL [sp_MSdel_dbo_event]', @upd_cmd = N'SCALL [sp_MSupd_dbo_event]'GO Can Some one please let me know, what is the way to do this kind of change.

  2. satya Moderator

    Did you refreshed the snapshot after these schema changes?
  3. Fais New Member

    As our publications size is huge , we cannot generate snapshot agent. So to avoid this , we configured our subscriptions to use "REPLICATION SUPPORT ONLY" option.
    My guess is , as we are not updating the table by referencing Primary key column, so the replication does not know, what to update.Thoughts ?
    UPDATE dbo.event
    SET block_ = 1, fuel = f_used

Share This Page