Need urgent help for creating index on subscriber replication server

Question

Need urgent help for creating index on subscriber replication server

Database admin(DBA)

SSCertifiable

Points: 5373
More actions
March 2, 2017 at 12:09 pm

#328073

Hi All,
I have set up Transactional replication on our servers,need a clarification on below point
.I have 100+ articles that are replicated to subscriber server but when I observed that there are lot of missing indexes on subscriber server, now development team getting the performance issue on Reporting db server(subscriber),I found 10 articles need to create the missing indexes on subscriber server to improve the performance. which is causing the issue currently.
Here can I create manually those missing indexes on subscriber server? I don’t want to take new snapshot or without re initialization, how we can replicate those missing indexes on subscriber server.
Also can you please let me know if there is a way that I can copy the indexes from the publication database. Please note I don’t want to break the existing replication and no impact in existing replication
Please share your suggestions on this issue.
Thanks in advance!!
Best Regards,
SQL server DBA

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

In the properties of the article, you need to set the properties to include the indexes. It does not do this by default.
You can also create what ever indexes you need on the subscriber, without affecting the replication.

This is not uncommon. The indexes that work well for the application (publisher) typically do not work so well for reporting (subscriber).

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 2

Thank you Michael for your quick response,
you need to set the properties to include the indexes. It does not do this by default. --> is it required any new snapshot or reinitialize,i believe not required but to be safe side can you confirm once. if so that i will go-ahead and create the missing non cluster indexes manually on subscriber server which is on 10 articles.
and there is no snapshot and no reinitialize is required.

Please correct me if i'm mistaken.

Thanks in advance!!

Best Regards,

SQL server DBA

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

If you change the properties of the article, it is not going to add the indexes. You will need to reinitialize the subscription.

Test it yourself. Create a new table, fill it with some data, add a primary key, a clustered index, and some non-clustered indexes.
Create a new publication with this table, initialize a new subscription. Change the articles proerties, and see what happens.

Create the indexes manually.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 4

THank you Michael for your quick response.

I have created indexes manually on subscriber server as you suggested,but is it require reinitialize the particluar article or we need to create a new snapshor for all articles?
if the reintialzie the particlular article,can you please provide the steps so that i will follow the sane process as per your suggestions.

if its not required then i can leave as it is.

Please share suttesions on the same...bit urgent

Thanks in advance!!

Best Regards,

SQL server DBA

Michael L John One Orange Chip Points: 27038 More actions · Answer 5

You do not need to re-initialize. Just add the indexes manually, and everything should be fine.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 6

Thank you Michael for your response.

I need one more help ,if we need to change the setting copy nonclustered indexes property to true for particular article or if any articles record doesn't match to subscriber(Ex: Publisher Table have 1000 records and subscriber server one table have 980 records..20 records mismatch)..

In this situation can we run Publisher Properties --> View Snapshot agent status -->start snapshot...

This is not impact on prod replication ,please correct me if i'm msitaken.

Thanks in advance!!

Best Regards,

SQL server DBA

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 7

Hello All,

Anyone can clarify above my thread.

Thanks in advance!!

Best regards,

SQL server DBA