none
Replicate Non-Clustered Indexes RRS feed

  • Question

  • Hi,

    I have a PRD Server with several DBs, each having 1000+ Tables.

    Hundreds of Tables have Non-Clustered Indexes.

    All Tables are successfully Replicated to our Staging, but without the Non-Clustered Indexes.

    Once I tried to ENABLE the "Copy nonclustered indexes" in the Local Publication Properties > Articles, the wizard either never completes (e.g. left it for about 12 hours) or crashes. 

    Scripting each non-clustered index manually is not an option (as it would probably take about a year :) ).

    I tried to come up with a script to automatically read all such indexes and create a script to create all such indexes and then just fire it up on the Staging side, but so far did not manage to complete it.

    Do you have any ideas/solutions, please?

    With thanks and kind regards,

    Bogdan


    • Edited by BogdanU Friday, July 26, 2019 8:50 AM
    Friday, July 26, 2019 8:49 AM

Answers

  • To answer your comments

    1) this could be a display issue. What version of SQL are you running?

    2) You are correct, this means there are a lot of log records for SQL Server to scan to extract replicated commands. 

    To answer your questions

    1) no, because tables differ widely. Some have identity columns, some don't, this will result in a different set of schema options. You can use a schema option which does everything and SQL Server will generate a correct one.

    2) No replicated commands is nothing to worry about. Run sp_replcounters to validate everything is working correctly.

    3) Its hard to peak into the tlog internals to see what it is reading and writing.  fn_dblog Might help you but it is hard to interpret.

     
    • Marked as answer by BogdanU Monday, August 5, 2019 10:42 AM
    Tuesday, July 30, 2019 2:14 PM
    Moderator

All replies

  • You will experience all sorts of locking when you use the wizard/gui. You are best off to stop your log reader and distribution agents and make the change, or better yet drop the article and add it back with the change in the schema_options parameter. This will involve dropping and recreating the subscriptions.
    Sunday, July 28, 2019 10:02 PM
    Moderator
  • Hello, Hilary.

    Thank you very much for your fast reply, and thank you for the past replies to other folks (since I used a number of them to try to solve various issues I encountered during the course of my work with SQL Server).

    On the current topic:

    After exhausting several ideas, I decided to indeed drop the whole thing and re-create it with "Copy nonclustered indexes" set to TRUE.

    The result so far is that:

    1) I go to the PUB Server > Publication > Properties and check the Article Properties > the option is set to FALSE (and this is true for both the "all" and "individual table" options - so as if the system just ignored that I wanted this to be set to TRUE)

    (However, the important thing is that the SUB Database has the unclustered indexes now :) )

    2) The Subscription is there, the SUB Database is being populated in front of my eyes, so all looks o.k., but when I go to the Replication Monitor Details, I see that it starts giving me the notifications of the type "Approximately 1500000 log records have been scanned in pass #4, 0 of which were marked for replication"

    My current thinking is that I let the Log Reader run for a while, since it has just started some minutes ago, but I am also aware that these messages can indicate that there are too many records for the Log Reader to catch up to.

    With the above being said, please allow me three follow-up questions:

    1) Is there a comprehensive "create-a-publication" and "create-a-subscription" script, with Parameters such as "Copy nonclustered indexes" so that I am positively sure that the Parameters will take up the state that I want them to (I know there is an option to let the system create a script for you when you use the Wizard, but I meant other than that)?

    2) Is the message that 0 records have been taken for replication something I should always be weary about or I just let the Agent take its time?

    3) Where are these 1.5m records the Monitor just mentioned found in (i.e. where is the Agent taking a look for them and how does it know which ones to consider for replication) - what is the query to observe all and every single one of them (or are they spread across several places)?

    With many thanks and kind regards,

    Bogdan




    • Edited by BogdanU Tuesday, July 30, 2019 12:51 PM
    Tuesday, July 30, 2019 12:26 PM
  • To answer your comments

    1) this could be a display issue. What version of SQL are you running?

    2) You are correct, this means there are a lot of log records for SQL Server to scan to extract replicated commands. 

    To answer your questions

    1) no, because tables differ widely. Some have identity columns, some don't, this will result in a different set of schema options. You can use a schema option which does everything and SQL Server will generate a correct one.

    2) No replicated commands is nothing to worry about. Run sp_replcounters to validate everything is working correctly.

    3) Its hard to peak into the tlog internals to see what it is reading and writing.  fn_dblog Might help you but it is hard to interpret.

     
    • Marked as answer by BogdanU Monday, August 5, 2019 10:42 AM
    Tuesday, July 30, 2019 2:14 PM
    Moderator
  • Hi, Hilary.

    Thank you for yet another helpful reply.

    Here are my comments:

    1) 2017 Standard Edition (version 14.0.1000.169)

    2) Yes - it seems to have been the case, since all cleared out after a while

    As for the answers, again, many thanks - they are of great help.

    (now that I have everything up and running, other questions are popping up, but I will open up a different thread)

    With many thanks and kind regards,

    Bogdan

    Monday, August 5, 2019 10:42 AM