locked
Changed Articles seem to include FKs and defaults despite settings RRS feed

  • Question

  • I was just reviewing the Differences in our reporting database (subscriber) and it seems like the changes that I have made to the publisher (over several releases)  have resulted in some Defaults and FKs being added despite the @schema_option settings. 

    My @schema_option = 0x00000004080350DF  so has no Defaults or FKs since neither 0x200 (FKs) or 0x800 (Default) are set.  Right?
    Looks like despite the settings for the Publication that new columns are propagated with FKs and default constraints.  A feature?

    Q: Is there another setting somewhere to suppress FK, DF to match my selected Schema_Options (no FK, no DF), while still allowing wanted DDL changes like new columns to make it through?  This is how I hoped it would have worked.  

    Is this or is it a known bug/limitation in transactional replication?  

    Is there a good reason behind this or is it just a limitation/bug?  

    Is it addressed in 2012?

    SQL Server 2008R2

    Thanks for your help.

    ...Ray


    ...Ray


    • Edited by RayG Wednesday, March 27, 2013 7:05 PM
    Wednesday, March 27, 2013 6:55 PM

Answers

  • If @replicate_ddl is set to true, which is the default.

    1. Add column(s).  Must allow NULL or include a default constraint.  Let it replicate out.
    2. Set @replicate_ddl to false to prevent foreign key constraint for replicating.  Add foreign key constraint.
    3. Set @replicate_ddl back to true.

    Brandon Williams (blog | linkedin)

    • Marked as answer by RayG Thursday, March 28, 2013 10:10 PM
    Thursday, March 28, 2013 8:55 PM

All replies

  • Schema changes are propagated regardless of how the schema option is set.  This is covered in the section General Considerations in Make Schema Changes on Publication Databases.

    If a publication is set to allow the propagation of schema changes, schema changes are propagated regardless of how the related schema option is set for an article in the publication. For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. To prevent this, disable the propagation of schema changes before issuing the ALTER TABLE command.

    Is this what you're running into?


    Brandon Williams (blog | linkedin)

    Wednesday, March 27, 2013 7:11 PM
  • I understand the limitation from BOL.  I'm asking if there is a nice way around it so that changes like adding or Altering a column or are propagated and FKs and Defaults don't.  This is, after all the spirit of the Schema_option and the expected behavior.  I'm also asking if its seen as a problem and if its been fixed in 2012.

    I'm not clear on what at the command level the work around would be.

      Suspend

      CREATE FK or Default

      Stop suspend

    Thanks

    ...Ray




    ...Ray

    Wednesday, March 27, 2013 10:16 PM
  • If @replicate_ddl is set to true, which is the default.

    1. Add column(s).  Must allow NULL or include a default constraint.  Let it replicate out.
    2. Set @replicate_ddl to false to prevent foreign key constraint for replicating.  Add foreign key constraint.
    3. Set @replicate_ddl back to true.

    Brandon Williams (blog | linkedin)

    • Marked as answer by RayG Thursday, March 28, 2013 10:10 PM
    Thursday, March 28, 2013 8:55 PM