Interesting Transactional Replication issue

Hello,

We have moved from SQL 2000 to SQL 2005 for our main server, and our reporting server, which uses transactional replication.

Now, in SQL 2000 when I originally setup replication, it replicated all of the table indexes.

I have recreated the publications in SQL 2005, but they are no longer there. Do you have any idea what would cause some of our table indexes to be missing?

What can be done to ensure this doesn't happen?

Thank you.

Justin, the default article schema options when creating a transactional publication through the SQL2005 workbench is to not replicate any non-clustered indexes (unique key constraints, clustered index, and primary key are replicated though). Is it possible that the indexes that you are missing at the subscriber are simply non-clustered indexes? Hope that helps.

-Raymond

|||Thanks for the reply. You're correct in that they are non-clustered indexes. How would I modify replication to include these secondary indexes?
|||

Hi Justin,

You can use sp_changearticle to enable the NonClusteredIndexes (0x40) schema option, or you can change the 'Copy non-clustered indexes' option to true on the article property sheet (right-click publication node->properties->select Articles on left plane->Click Article Properties button.

Hope that helps,

-Raymond