Changing Merge Article Properties

VDT-7677 121 Reputation points
2022-08-31T22:01:07.073+00:00

Hi,

Running SQL Server 2008 R2 in a merge replication topology. Distributor and publisher are on same machine, subscribers synchronize using pull subscriptions.

I'd like to create a non-clustered index on one of the tables included in the merge publication, but I don't want it replicated to the subscribers. For triggers and such I would use the NOT FOR REPLICATION command but that doesn't appear to be an option when creating an index. My understanding is that in order to do this I need to set the 'Copy nonclustered indexes' option to False on the Article Properties window for the Merge Article in question. To this end I have a couple of questions:

1) Is my understanding of how this can be accomplished correct? If not what am I missing?

2) Can modifying this option be achieved via TSQL? If so how? The sp_changemergearticle procedure doesn't appear to do what I need (unless I have missed the property).

3) Will changing this particular property force the re-initialization of subscriptions?

4) Will changing this particular property force the invalidation of the snapshot?

Any assistance is greatly appreciated!

Best Regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
Accepted answer
  1. Seeya Xi-MSFT 16,446 Reputation points
    2022-09-05T02:14:00.953+00:00

    Hi @VDT-7677 ,

    I can only post it as an answer because of too much text. Please refer to this explained documentation: To change schema options for an existing article in a merge publication

    At the Publisher on the publication database, execute sp_helpmergearticle. Specify the name of the publication to which the article belongs for @publication and the name of the article for @article. Note the value of the schema_option column in the result set.

    1. Execute a & (Bitwise AND) operation using the value from step 1 and the desired schema option value to determine if the option is set.
    2. If the result is 0, the option is not set.
      If the result is the option value, the option is already set.
    3. If the option is not set, execute a | (Bitwise OR) operation using the value from step 1 and the desired schema option value.
    4. At the Publisher on the publication database, execute sp_changemergearticle. Specify the name of the publication to which the article belongs for @publication, the name of the article for @article, a value of schema_option for @property, and the hexadecimal result from step 3 for @value.
    5. Run the Snapshot Agent to generate a new snapshot. For more information, see Create and Apply the Initial Snapshot.

    Now let me add some of my understanding:
    First, you need to convert the hexadecimal to binary to see if each bit is a 1 or a 0. See the following example:
    0x00----------------0000 0000 b
    0x01----------------0000 0001 b (This is added temporarily by me, you don't have it in your assumption, I added it to the options in that document.)
    0x10----------------0001 0000 b
    0x20----------------0010 0000 b
    0x40----------------0100 0000 b
    0x80----------------1000 0000 b
    0x100---------0001 0000 0000 b (0x represents hexadecimal, b represents binary)
    There are many options below that are the same conversion method, so I won't list them one by one. You can see that each option corresponds to a different bit.
    If you have assigned a value to this parameter and you want to get what options are set, you need to AND each bit to see the corresponding options.
    If you haven't set this parameter you can use the OR operation to compose the value you want. In your assumption, all you need to do is list the binary of each option inside, and the result is 0001 1111 0001 b. The corresponding hexadecimal is 0x1f1.
    Hope this could help you get better understanding.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,446 Reputation points
    2022-09-01T09:27:25.043+00:00

    Hi @VDT-7677 ,

    Welcome to Microsoft Q&A!

    Can modifying this option be achieved via TSQL? If so how? The sp_changemergearticle procedure doesn't appear to do what I need (unless I have missed the property).

    Because sp_changemergearticle is used to change article properties that were initially specified by using sp_addmergearticle, refer to sp_addmergearticle for additional information about these properties.
    [ @schema_option = ] schema_option Is a bitmap of the schema generation option for the given article. schema_option is binary and can be the | (Bitwise OR) product of one or more of these values.

    Will changing this particular property force the re-initialization of subscriptions? Will changing this particular property force the invalidation of the snapshot?

    Please read this official document about sp_changemergearticle and jump to the part of Remarks.
    236818-image.png
    ![254623-image.png
    ]5So, Changing the property schema option requires that existing subscriptions be reinitialized, and you must specify a value of 1 for the force_reinit_subscription parameter.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.