Is it possible to add indexes to replicated database tables?

Question

Is it possible to add indexes to replicated database tables?

Tim Trombley

SSC Enthusiast

Points: 112
More actions
December 14, 2005 at 1:04 pm

#92431

I have replication pushing database tables that are inadequately indexed to another SQL server. Is it possible/allowable to add indexes to the tables in the replicated database without causing adverse affects?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vinod Pal Old Hand Points: 345 More actions · Answer 1

Hi,

I would unsubscribe and drop the articles (tables that you want add/modify the indexes for) and then add the table back and then re-subscribe.

sp_addsubscription @publication = N'Northwind', @article = N'all', @subscriber = N'SLON50T11091\DBA', @destination_db = N'Northwind'

sp_dropsubscription @publication = N'Northwind', @article = N'all', @subscriber = N'SLON50T11091\DBA', @destination_db = N'Northwind'

-- The above will add or drop a complete subscription (all articles), note @article = all.

exec sp_dropsubscription @publication = N'Northwind', @article = N'Shippers', @subscriber = N'SLON50T11091\DBA', @destination_db = N'Northwind'

exec sp_droparticle @publication = N'Northwind', @article = N'Shippers', @force_invalidate_snapshot = 1

-- The above will remove a selected article from the subscription and the publication it is part of. If schema changes such as changing a column

-- data type or adding an index to the table, this approach needs to be taken.

exec sp_addarticle @publication = N'Northwind', @article = N'Shippers', @source_owner = N'dbo', @source_object = N'Shippers', @destination_table = N'Shippers', @type = N'logbased'

exec sp_addsubscription @publication = N'Northwind', @article = N'Shippers', @subscriber = N'SLON50T11091\DBA', @destination_db = N'Northwind'

-- The above will add an article to the publication, the second statement will flag the new article to be picked up when the snapshot agent is next run.

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

You can get away with running the CREATE INDEX command(s) on the publisher and the subscriber. This might be a little quicker than dropping the article because you will not need to apply another snapshot. However, it then becomes your responsibility to ensure that the index is created correctly on each server.

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 3

If you look at the properties of the publication through SQL Enterprise Manager, go to the articles tab and click the (...) button for one of the tables selected.

Then go to the snapshot tab and you will see a section that is titled 'Copy objects to destination'

If the two index options are selected then all you should have to do is add the index to your publication database and replication should take care of the rest.

I say should because a couple of weeks ago someone on this list was experiencing a problem where the new index he created was not being propagated to the subscriber.

In that event, there is no issue with creating the indexes directly on the subscriber database.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

You just want a new index at the subscriber ? I did this without a problem to facilitate users who were running a lot of queries ordering by date. I don't want the index on the publisher (production), so I just added it to the subscriber (reporting DB)