question

MAXKA avatar image
MAXKA asked

Do indexes also get replicated via Transactional Replication

We have our env set up such that, an OLTP DB X server has a database around 400 GB in size and is getting replicated on reporting server Y via Transactional replication. We have seen quite a slowness on this reporting server when similar reports are ran on OLTP publisher. While monitoring i have figured that on subscriber database:- a) Lot of triggers have been disabled. (Not sure if that impacts) b) There are lot of manual indexes created on reporting server, which i can understand would have been created as there 100 reports fetching data on daily basis from this server. But my concern here is that there are lot of important tables on Publisher which have quite a few non clustered indexes created which are not there on subscriber. So, does replication not transferring those indexes from X to Y or were they deleted on reporting server manually and does the indexes on X should always be there on Y(reporting server) in terms of good performance? Please suggest! Thanks..
sql-server-2005replicationperformance
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MAXKA avatar image MAXKA commented ·
@DenisT, thanks! Good article. But now if i turn back that option, would it be safe or would that break the replication between two. Just being curious. Thanks
0 Likes 0 ·
DenisT avatar image DenisT commented ·
It WILL break the replication! I've done it before :) You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have nonclustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them.
0 Likes 0 ·
MAXKA avatar image MAXKA commented ·
@DenisT , As of now, we can not afford a break in replication. Impact would be there and henceforth lot of mails flowing :).. So to avoid, will that be a good option if get the list of non clustered indexes from X compare them on Y, and thus create it manually for now. I know its quite tedious and manual, but does not seem any other way out. Please suggest. Thanks!
0 Likes 0 ·
DenisT avatar image DenisT commented ·
Yes, it will. But do it during your maintenance window (low load time!) Keep an eye on the subscriber's transaction log file!
0 Likes 0 ·
MAXKA avatar image MAXKA commented ·
@DenisT, yes thank you for alerting, Good point. will take care of log file. Also, may be just little more curious, ONCE INDEX has been created manually on server Y, what about the stats, do stats aslso need to be transferred, sorry but got confused.
0 Likes 0 ·
Show more comments

1 Answer

·
DenisT avatar image
DenisT answered
They are not replicated by default, please see this article for details -- http://blogs.msdn.com/b/repltalk/archive/2012/04/03/replicating-non-clustered-indexes-improves-subscriber-query-performance.aspx! It makes sense since it's a case by case decision.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.