Transactional replication pre and post scripts 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.
At my last job this is exactly what we did for a very similar scenario (also on SQL Server 2008 R2 - Standard Edition), and I was the one to build it out. We had an off the shelf vendor application we heavily used and needed to report out of. It was indexed differently than how we were reporting out of it. 

We had one way transactional replication setup to sync from the vendor app to our report server. I setup pre and post scripts to drop and recreate the appropriate indexes (and some other relevant entities like indexed views), which of course checked for object existence first too (you can check this via the sys schema's tables and views) before executing.

The scripts themselves just pointed to a stored procedure where all the logic lived, on the subscriber server. This just felt more natural for maintaining the code than updating an external script file. The pre script to drop the custom indexes before the schema was synced was important because there was a bug we ran into where transactional replication would occasionally break and stop syncing otherwise. (If you need a code example of my pre and post scripts, let me know and I'll update this answer.)

In regards to your second question, in general it's faster to bulk insert your records into a table and then add your indexes after (this gives SQL server the full picture to arrange the data in the most efficient manner, at one time). To do this with transactional replication, you'll need to de-select "copy clustered indexes" (and non-clustered indexes for that matter) in the Publisher's Properties window under Article Properties for that table. (Letting it sync the data with the Publisher's clustered index is essentially just doubling the work your server will need to do when you re-index it on the Subscriber with your preferred clustered index.)