Unanswered: RAID and Transaction Logs Involved in Replication
I began documenting recommended RAID configurations for our environment. Typically I encourage using RAID 1 for T-Logs since log writes are sequential. But then I started to wonder about how replication impacts T-Logs. According to BOL:
"The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database."
This being the case, doesn't that indicate the disk spindles will be repositioned when the Log Reader Agent reads the logs and copies the data to the Distribution database? If so, is RAID 1 still the best option?
Re: RAID and Transaction Logs Involved in Replication
The impact of whatever RAID you use is very little as far as the efficency of transaction log, as long as you keep it simple and small enough. In a replication environment, transaction logs will not get dumped until the Log Reader loads the marked transactions onto the Distribution database. When you have large numbers of IOs, like over 35 millions commands within 5 hours, in our company. The Log Reader works extremely well in RAID 5. But the bottleneck is in Distribution database because all transactions get in there in a big hurry and cannot be distributed fast enough.
Someone in another forum pointed out that typically the Log Reader runs continuously. As a result the impact on the disk spindles should be small since only a couple of data pages are involved in the I/O. The only potential issue I see is when "Sync with Backup" is turned on. When on, the Log Reader Agent only sends T-Log data to the distribution database after a T-Log backup has been performed. In our environment this occurs every two hours. Depending upon the amount of transactions being processed, this has the potential to impact performance during the time the Log Reader is copying data from the T-Log and inserting it into the Distribution database. I suspect in most environments having the Log on a RAID 1, where the database is involved in replication, would not noticeably impact performance.