Configuring Transactional Replication for an OLTP Database

Question

We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader deadlocks.

Now we want to apply transactional replication (to obtain more real time data). However, would the same issue occur?

If the reporting subscription database is being written into, updated/deleted, and we have read queries, would we still get the locking, blocking issue?

Although at least it would not be on a live OLTP system.

Answer 1

Using RCSI on the subscriber(s) is a common way to design this type of a scenario to avoid blocking. RCSI will allow readers and writes to play nice together but won't solve writers blocking writers. Since the report queries are readers and the transactional replication is a writer, this feature is a good fit for this.

You just need to make sure that your TempDB is configured to support your workload for the versioning. Also remember that enabling RCSI adds 14 bytes to each inserted/updated row which might cause internal fragmentation.