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?

Please let me know your thoughts.

Thanks, DaveThe 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.

Wish this helps! Good luck.|||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.

Thanks, Dave

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?
Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.com/sql_server_performance_audit3.asp
John
"Hassan" wrote:

> I know RAID 5 has a slight performance hit when it comes to writes but what
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.com/sql_server_performance_audit3.asp
John
"Hassan" wrote:
> I know RAID 5 has a slight performance hit when it comes to writes but what
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>sql

RAID 5 vs RAID 10 for reads

I know RAID 5 has a slight performance hit when it comes to writes but what
about when comparing RAID 5 to RAID 10 when it comes to reads ?Hi Hassan
The number of Read I/Os supported by RAID 5 and RAID 10 are the same
i.e The number of drives in the array X the number of I/Os per second that a
drive can handle.
You may want to read the comparisson of RAID levels in Microsoft SQL Server
2000 Performance Tuning Technical Reference, Chapter 3 by Ed Whalen et al.
ISBN 0735612706
Other reading
http://msdn2.microsoft.com/en-us/library/ms184252.aspx
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
http://msdn2.microsoft.com/en-us/library/ms178048.aspx
http://www.sql-server-performance.c...ance_audit3.asp
John
"Hassan" wrote:

> I know RAID 5 has a slight performance hit when it comes to writes but wha
t
> about when comparing RAID 5 to RAID 10 when it comes to reads ?
>
>

Raid 1 vs Raid 10

Raid 1 is faster than Raid 10. The official stats are
operations per disk = (reads + writes) / number of disks
where as Raid 10 is...
operations per disk = (reads + (2 * writes )) / 2
However on my system I have Raid 10 for my log files. The
reason is that it offers the best combination of speed and
fault talerance, however it is not cheap, which is the
reason why we have raid 5 for our data files.
Get the book SQL Server 2000 Administrator Companion, ir
covers it in more detail.
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>Hi,
>does anyone know where I can get soma stats on RAID
>efficiencies for SQL Server. Basically, I am interested
>in the difference between RAID 1 and RAID 10 for the
>transaction log. I realise that RAID 1 id the
>recommendation, but I need some data to convince the
>client that it is more appropriate.
>TIA,
>Jim
>.
>
I don't agree with Peter at all on the fact that Raid 10 is slower than Raid
1. The fact that a Raid 10 has twice the number of disks and can be smart
about how and when it reads and writes to each are a big advantage and can
make a difference in a very high volume system. The Raid 10 also gives much
better fault tolerance which can often be more of an advantage than sheer
speed anyway. Unless you are talking about many thousands of transactions
per second you probably won't notice a difference in speed between the two
any way. If you have the luxury of choosing between Raid 1 and 10 and it
doesn't take always disks / space etc. from any other array I would always
go for the 10. The reason why your hosting provider wants to go with Raid 1
over 10 is simply cost. I doubt that your doing enough trans per second to
warrant the extra cost of 10 and Raid 1 will suite most apps just fine.
Andrew J. Kelly SQL MVP
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:7f9601c495a1$f8550050$a601280a@.phx.gbl...[vbcol=seagreen]
> Raid 1 is faster than Raid 10. The official stats are
> operations per disk = (reads + writes) / number of disks
> where as Raid 10 is...
> operations per disk = (reads + (2 * writes )) / 2
> However on my system I have Raid 10 for my log files. The
> reason is that it offers the best combination of speed and
> fault talerance, however it is not cheap, which is the
> reason why we have raid 5 for our data files.
> Get the book SQL Server 2000 Administrator Companion, ir
> covers it in more detail.
> Peter
> "Action speaks louder than words but not nearly as often"
> Mark Twain
>
|||RAID 10 should be faster than RAID1. See
RAID1
http://www.acnc.com/04_01_01.html
RAID10
http://www.acnc.com/04_01_10.html
I have done extensive testing on my hardware and also found that RAID5
was better than RAID1 for transaction logs. Bizarre, I know, but the
truth came out in the testing. So *on our hardware* we went with RAID5
for the transaction logs.
The moral of the story is, you need to benchmark different RAID levels
to get an accurate picture. Things don't always turn out as you expect.
If there's no way you can test your hardware before deciding, then RAID1
should be fine for you, but you *may* get a performance boost by using
RAID10 at considerable extra expense.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter The Spate wrote:[vbcol=seagreen]
> Raid 1 is faster than Raid 10. The official stats are
> operations per disk = (reads + writes) / number of disks
> where as Raid 10 is...
> operations per disk = (reads + (2 * writes )) / 2
> However on my system I have Raid 10 for my log files. The
> reason is that it offers the best combination of speed and
> fault talerance, however it is not cheap, which is the
> reason why we have raid 5 for our data files.
> Get the book SQL Server 2000 Administrator Companion, ir
> covers it in more detail.
> Peter
> "Action speaks louder than words but not nearly as often"
> Mark Twain
>
>
|||Andrew is completely correct and I am completely wrong.
I even got the formulas mixed up.
Should of been
Raid 1
operations per disk = (reads + (2 * Writes)) / 2
Raid 10
operations per disk = (reads + (2 * writes)) / number of
disks.
I'm off to hide for a couple of days to get over the
shame, in thw meanwhile I will invent another person.
Peter
"The best argument against democracy is a five-minute
conversation with the average voter."
Winston Churchill

>--Original Message--
>I don't agree with Peter at all on the fact that Raid 10
is slower than Raid
>1. The fact that a Raid 10 has twice the number of disks
and can be smart
>about how and when it reads and writes to each are a big
advantage and can
>make a difference in a very high volume system. The Raid
10 also gives much
>better fault tolerance which can often be more of an
advantage than sheer
>speed anyway. Unless you are talking about many
thousands of transactions
>per second you probably won't notice a difference in
speed between the two
>any way. If you have the luxury of choosing between Raid
1 and 10 and it
>doesn't take always disks / space etc. from any other
array I would always
>go for the 10. The reason why your hosting provider
wants to go with Raid 1
>over 10 is simply cost. I doubt that your doing enough
trans per second to
>warrant the extra cost of 10 and Raid 1 will suite most
apps just fine.
>--
>Andrew J. Kelly SQL MVP
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:7f9601c495a1$f8550050$a601280a@.phx.gbl...
The[vbcol=seagreen]
and[vbcol=seagreen]
often"
>
>.
>
|||Hey, we all make mistakes! :-) Except Andrew, he's a star.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter The Spate wrote:
> Andrew is completely correct and I am completely wrong.
> I even got the formulas mixed up.
> Should of been
> Raid 1
> operations per disk = (reads + (2 * Writes)) / 2
> Raid 10
> operations per disk = (reads + (2 * writes)) / number of
> disks.
> I'm off to hide for a couple of days to get over the
> shame, in thw meanwhile I will invent another person.
> Peter
> "The best argument against democracy is a five-minute
> conversation with the average voter."
> Winston Churchill
>
|||be sure to get a Good RAID Controller with Cache and battery backup as well.
Also, the more disks, the better the performance so try to get as many disks
as possible.
example:
8 36GB Disks will perform better than 4 72GB Disks (Generally Speaking).
Greg Jackson
PDX, Oregon
|||Now we all know I have made a few mistakes here before as well. I think
Allan is the real star<g>...
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eOOvS6alEHA.2764@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hey, we all make mistakes! :-) Except Andrew, he's a star.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Peter The Spate wrote: