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

Interesting question

hi,
I am thinking if Transactional backup is based on the data in the
Transaction Log. what if i set up a the Log Reader to be run every 1 hour,
but within that 1 hour, i do a transactional backup and issue the command
"CheckPoint"!!! Can the log reader still be able to read what i have changed
before the backup?
Thanks
Ed
Ed,
transactions are only removed from the transaction log once sp_repldone has
been issued. This is called by the log reader agent, so backing up the log,
using simple recovery mode, issuing checkpoints etc won't make any
difference.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||transactions are never removed from the log otherwise doing any form of
recovery using the log would be useless. Rather they are marked as
replicated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23X7hZXnKFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Ed,
> transactions are only removed from the transaction log once sp_repldone
has
> been issued. This is called by the log reader agent, so backing up the
log,
> using simple recovery mode, issuing checkpoints etc won't make any
> difference.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the correction! What I meant is that they can
be removed on backup of the log or truncation, although
my Englissh let me down
Rgds,
Paul
|||I thought that was what you meant, but it wasn't clear And you know me -
I am very easily confused.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:142101c52afc$b2e69bf0$a601280a@.phx.gbl...
> Thanks for the correction! What I meant is that they can
> be removed on backup of the log or truncation, although
> my Englissh let me down
> Rgds,
> Paul
>