Prev: Horizontal Filtering
Next: Delete an old subscription from the subscriber?
From: satyendra on 13 Apr 2010 12:39
Hello,
I have a transactional replication in SQL 2000.
If I understand correctly, all the indexes get replicated to the
Subscriber database.

What happens if I drop an index on publisher database?
Will my index on Subscriber Database also get dropped?
Will my replication error out?
Thanks
From: Ooogy on 14 Apr 2010 11:32
You're half correct in your initial statement Satyendra :-)

Indexes for primary keys (usually CI's) are always copied. You have
the option of modifying the article properties to include or exclude
other CI's or NCI's when the publication is initially set up. Those
options are on the "Snapshot" tab of the article property ellipsis and
are fairly self-explanatory. The tricky part is to try at all costs
to be 100% sure of what you want to accomplish prior to implementing
the replication. As in everything else we DBAs do daily, proper
planning is key in any successful process we move forward with, eh?

Making index changes on the publisher after replication has already
initiated is actually physically allowed and no, it shouldn't break
replication. The important thing to remember is that even though you
can modify these properties on the publisher, just about all changes
you can make WILL NOT replicate. This includes dropping a constraint,
creating a new constraint, as well as dropping or creating new
indexes.

One of the nice things about transactional replication is that it
allows us to create different indexes on the subscribers then exist at
the publisher. This allows you to tune the database at the subscriber
for their specific needs. For example if you use replication to move
a subset of an OLTP publishing database to a read-only reporting
subscribing database, you can certainly see how a completely different
set of indexes would need to be implemented. SQL Server
transactional replication allows the opportunity to do that very
thing. It's very handy.

Have a grateful day...Troy

 | 
Pages: 1
Prev: Horizontal Filtering
Next: Delete an old subscription from the subscriber?