Hello,

I set up replication on our servers at work to streamline some procedures we run daily/weekly on them.

This copies around 15 articles from two databases on the "Master" server to another server used for execution purposes. For the most part it was a pretty straight forward task and it seemed to work nicely; but I realised after some investigation that the non-clustered indexes weren't copying over to the child server.

I set the non-clustered indexes property in the properties of the publishing articles to "True" and generated a new snapshot, this seemed to work, but I've come into work this morning to find the property has reset to "False" and I have no indexes on the table again. Why is this happening and is there any way I can resolve the matter so the indexes are copied over concurrently?

Thanks in advance for your advice.

JB

These should be set to false - to copy the non-clustered indexes.

I am wondering if Management Studio is lying to you.

Can you issue the following in your publication database

sp_helparticle PublicationName, TableName

you should get something like:

0x00000000080350DF

Hello Hilary,

Thanks for the reply. 

I tried executing the procedure on the server where the publication is held. Upon execution it returns "Commands Completed Successfully". Is there a special way I should be referencing the publication's name?

I also thought SSMS might be lying but I have tested some of the automated procedures I usually carry out on the child server but instead I referenced the master tables from the master server, resulting in the procedure completing almost 50% quicker than using tables on the subscribing server (if that makes sense).

I actually solved this.

You can use a post-replication SQL script to create the indexes. Whatever articles you're publishing open up the indexes drop down list of the article in object explorer, right-click on an index and hover over Script Index as, then Create-to, then click New Query Window editor.

Up will pop up a new query window with the resulting index. Work your way through all the indexes on all the articles of the publication, copy and pasting just the create index line and below of each script, pull them all together into one query window.

Once you're done find a safe folder somewhere on your harddrive and save the SQL query as an .sql file with a sensible name.

Right click on the publication and goto properties. Click on the "Snapshot" tab, in there; there should be a section saying "Run additional scripts". Choose the browse button next to "After applying the Snapshot; execute this script:"

Navigate to your script file and choose it. Once done click ok and it'll prompt you that something has changed and if you'd like to generate a new snapshot, make sure you do or it won't work.

That's it, you'll find once the publication has bulk copied over the the subscriptions successfully there are non clustered indexes on the tables. Pretty simple!

• Marked as answer by Johnny Bell Jnr Thursday, December 13, 2012 9:18 AM

Glad it is working for you Johnny - but it should have worked by selecting replicate non-clustered indexes. What version and edition was your publisher and subscriber?

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 
Microsoft Internet Explorer 6.0.2900.5512
Microsoft .NET Framework 2.0.50727.3053
Operating System 5.1.2600

And it's SQL Server 2008 R2

Has there been any traction on this issue?  I'm seeing the exact same thing with my Publication Properties resetting  both "Copy nonclustered indexes" and "Copy full text indexes" to False after setting to True.  I've tried this multiple times with multiple publications.  I'm running replication from SQL 2005 to both SQL 2005 and SQL 2008 servers.  Would love to get an answer on this as it's causing us some problems and I'd rather not execute a script as Johnny Bell Jnr suggests.

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0 
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.3643
Operating System 5.2.3790

Thanks in advance,

Jimmy

I just found this additional info: http://sqllearnings.com/2012/04/04/subscription-re-initialization-and-non-clustered-indexes/  Apparently it gets set back to false by default and there isn't a lot to be done about it.  I find that to be a little ridiculous, so I'd love to hear some clarification on it if anyone is able.

Thanks,
Jimmy

• Proposed as answer by JimmyTipit Friday, March 08, 2013 5:42 PM
• Unproposed as answer by JimmyTipit Friday, March 08, 2013 5:42 PM

I believe I just figured this issue out.  When setting the Article Property, if you select "Set Properties of All Table Articles" and set "Copy nonclustered index" to TRUE, then save the setting, the next time you look at properties for all articles, it will be set to False.  However, if you look at the properties of individual Table Articles, by selecting "Set Properties of Highlighted Table Article", you'll see that "Copy nonclustered index" is correctly set to "TRUE" IF your table had an index to begin with.  So it seems that the setting for All Table Articles defaults to False if any of your tables don't contain nonclustered indexes.

Definitely confusing and probably a bug.  

• Proposed as answer by JimmyTipit Friday, March 08, 2013 5:47 PM

Johnny, you are just scripting out indexes manually and then running them over on the subscriber. But if a new index is created on any of the exisiting tables, it would not replicate.

Faced same problem.But in my situation there are about two thousand indexes...

This property would be really useful if I could save settings.