Transactional Replication

The way our system is designed, We use transactional replication for quite a while now. Though once in a while it gets confused with snapshot replication, since transactional replication creates a log reader job, a snapshot job and a distributor job.

The snapshot job is required only the very first time when the articles in the database need to be published from the publisher to the subscriber. We found that this job is no more required and is best turned off at other times. We realized this when we started having blocking in the middle of the day. Investigation revealed that it was because of replication. But replication ran at other times too. The blocking did not happen at those other times. So we looked closely and found another job running at the same time as the snapshot creation and distribution and since snapshot job blocked the tables, the other job also blocked, thus causing blocking and deadlocks. We shifted the other jobs to run 5 minutes after the replication job and disabled the snapshot job since it was not necessary.

As we experimented we realized that, the log reader continuously runs and reads any new changes to the articles to be published in the transaction log and the distributer which can be run either continuously or say every hour or once a day according to the requirement, will pick up all the data and schema changes and distribute it to other servers.
Our initial assumption that distributer can distribute only data changes and not schema changes was wrong.
Thus we disabled the snapshot job.

The snapshot is only required when a new article (table) is added to the published set and that needs to be newly created at the subscriber. Since we have streamlined releases and client downtime during releases. When we notice such a scenario (which is rare) we plan to run the snapshot job during releases manually and hence reduce the blocking caused by the snapshot job.