Prev: Replication broken, need to delete records from replication. Help.
Next: Web replication
From: Corin on 11 Dec 2009 08:53
We have a production hotel reservation system database in SQL2005, and we publish a table subset (mainly price and room availability) to several SQL2000 subscriber databases via transactional replication. These subscriber servers are the back-end for our our customer hotel search API.

We replicate clustered and nonclustered indexes from the core database, which has many indexes tuned for the general usage of our reservation system. Most of these these indexes are redundant at the subscribers since their db usage revolves around hotel availability and price searches only. The unwanted indexes take up a lot of space and also cause performance issues when they are frequently reorganised/rebuilt. Also when replication fails (a question for another time) this makes the reinitialisation procedure lengthy.

My question is, if I disable copying of nonclustered indexes at the publisher, then create just the required indexes at the subscribers, next time I need to reinitialize, will replication drop/delete my indexes, or does it leave existing nonclustered indexes intact?

Many thanks in advance,
Corin.


Submitted via EggHeadCafe - Software Developer Portal of Choice
..NET Web Services On Mobile Devices
http://www.eggheadcafe.com/tutorials/aspnet/2e433f5c-229d-46bb-b822-c05e14105eb5/net-web-services-on-mobi.aspx
From: Hilary Cotter on 11 Dec 2009 09:03
By default (with the @Pre_creation_parameter of sp_addarticle set to drop)
they will be dropped.

You could recreate them use a post_snapshot command, or add them through
sp_addscriptexec.


"Corin Grieves" wrote in message
news:2009121185326corin.grieves(a)jacobonline.com...
> We have a production hotel reservation system database in SQL2005, and we
> publish a table subset (mainly price and room availability) to several
> SQL2000 subscriber databases via transactional replication. These
> subscriber servers are the back-end for our our customer hotel search API.
>
> We replicate clustered and nonclustered indexes from the core database,
> which has many indexes tuned for the general usage of our reservation
> system. Most of these these indexes are redundant at the subscribers since
> their db usage revolves around hotel availability and price searches only.
> The unwanted indexes take up a lot of space and also cause performance
> issues when they are frequently reorganised/rebuilt. Also when replication
> fails (a question for another time) this makes the reinitialisation
> procedure lengthy.
>
> My question is, if I disable copying of nonclustered indexes at the
> publisher, then create just the required indexes at the subscribers, next
> time I need to reinitialize, will replication drop/delete my indexes, or
> does it leave existing nonclustered indexes intact?
>
> Many thanks in advance,
> Corin.
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> .NET Web Services On Mobile Devices
> http://www.eggheadcafe.com/tutorials/aspnet/2e433f5c-229d-46bb-b822-c05e14105eb5/net-web-services-on-mobi.aspx

From: Paul Ibison on 12 Dec 2009 16:14
Yes - the default is to drop the tables on initialization. If you have
created the indexes manually on the subscriber then add the index creation
scripts together and use this as a post-snapshot script - part of the
publication properties.
HTH,
Paul Ibison

 | 
Pages: 1
Prev: Replication broken, need to delete records from replication. Help.
Next: Web replication