How do I go about pausing transactional replication on the subscriber end.
We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.
Some background, we have quite a busy BizTalk server where we replicate the data from the BAM parts to a separate database server which will be used for a customized web application for statistics, searching etc. Seeing as the tables in the BizTalk server are both quite large and missing indexes etc since they're basically just there to have data dumped into them we opted to have the second, replicated database in which we can apply various optimizations for reading the data.
One thing I've noticed though is that adding a basic, non-clustered, index takes forever and causes all kinds of locks (we're using SQL Server Standard so Online indexing isn't an option unfortunately) I was thinking that giving the database a bit of respite from all the write operations from the BizTalk data while creating the indexes would be a good idea (right or wrong?).
Question is, how do I pause the replication (without losing any data from the publisher) on the subscriber end while creating the indexes?