Just a preface that I'm working at the moment as an accidental DBA so sorry for any ignorance.
I need to completely rebuild one of our transactional replications. The indexing strategy is different on the subscriber database than it is on the publisher, the primary key is the same but the clustered index on NC indexes are different. I am aware that the tables by default are completely dropped on the subscriber. I believe I can use scripts pre and post snapshot application to drop and create indexes.
Both the clustered indexes on the subscriber and publisher are UniqueIdentifier but different columns. The table has nearly 1 billion rows and is sadly pretty wide. Just thought that I'd provide that information in case it makes a difference.
My questions are:
- Is using the post execution scripts the best way to go about this?
- If the clustered index is different on the subscriber than the publisher am I best to allow the bulk insert to happen with the publisher clustered index or would it be better to set the alternate clustered index before the bulk insert?
Thanks in advance for any help.