We are in the process of procuring a new IBM SAN for our most business
critical SQL database. The database right now runs great on a 10 disk
RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
of the new hardware implementation, we want to go to RAID10 and as per
a vendor recommendation, break the database out into file groups on
separate physical disks in order to boost performance.
I keep getting hung up on spindle count. I have always subscribed to
"the more spinning heads the better". The flaw in this theory appears
to be as the smallest disk you can buy for a given solution gets larger
and larger , where is the trade off? Do you still stripe across as
many disks as possible and end up burning a lot of disk space? Does
the large amount of cache (4GB on our new system) decrease the number
of spindles needed?
Is there an "easy" way or rule of thumb that would allow me to
translate 10 disk wide RAID5 performance into X disk wide Raid 10
performance. In a nutshell, how can I estimate what the following gets
me with regard to reducing the number of spindles necessary:
1. Going to RAID10 from RAID 5
2. New solution has 4GB of cache / old solution had 512MB
If I go with the vendor recommendations for table placement in file
groups I will end up with something in the range of four (4) - two (2)
disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
disks, but only 2 will be useable for space and my understanding is
that only two (2) will be counted for parallel I/O performance. I am
nervous about how narrow these RAID 10 sets are but maybe it does not
matter as much?
As a side note, i have no good way other than what I can get through
Profiler to load test the new solution with actual production data.
Any insight/thoughts would be appreciated.
Thanks!
PaulPaul;
4GB of cache is unlikely to significantly decrease the number of spindles
you need to stripe across to get the performance. Very large cache size does
have an tendency to mask the need for more spindles. But that really depends
on the workloads (on whether the workloads can take advantage of that cache).
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance.
There is commonly known RAID formula for doing this estimate. Assume that
the read:write ratio of the workload is 3:1--a very typical ratio, and assume
that you are using 15,000rpm disks, which can roughly do 180 IOs per second
(IOps).
RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O,
the I/O penalty is 4/(3+4) = 0.57
RAID 10: Since each write needs two disk I/Os and each read needs one,
the I/O penalty is 4/(3+2) = 0.80
For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
Of course, this is just a theoretical rough estimate with many factors
absent. For instance, it doesn't consider the effect of the SAN cache. But
it's safe to say that the vendor suggestion of 4 disk RAID 10 is unlikely to
cut it (unless there is anyting specific to the workload of your app).
Running application-specific benechmark is important to verify whether the
disk config is adequate. However, you can also use disk performance tools
such as IOMeter or sqlio.exe to specifically examine the performance of your
disk subsystem outside of SQL Server. I would run sqlio.exe whenever I get a
new storage so that I know what storage performance to expect, and have data
for later comparison when needed.
In general, it's far better and simpler to just stripe across as many disks
as you can (within the disk subsystem limits such as controller throughput).
Linchi
"Paul" wrote:
> We are in the process of procuring a new IBM SAN for our most business
> critical SQL database. The database right now runs great on a 10 disk
> RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
> of the new hardware implementation, we want to go to RAID10 and as per
> a vendor recommendation, break the database out into file groups on
> separate physical disks in order to boost performance.
> I keep getting hung up on spindle count. I have always subscribed to
> "the more spinning heads the better". The flaw in this theory appears
> to be as the smallest disk you can buy for a given solution gets larger
> and larger , where is the trade off? Do you still stripe across as
> many disks as possible and end up burning a lot of disk space? Does
> the large amount of cache (4GB on our new system) decrease the number
> of spindles needed?
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance. In a nutshell, how can I estimate what the following gets
> me with regard to reducing the number of spindles necessary:
> 1. Going to RAID10 from RAID 5
> 2. New solution has 4GB of cache / old solution had 512MB
> If I go with the vendor recommendations for table placement in file
> groups I will end up with something in the range of four (4) - two (2)
> disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
> disks, but only 2 will be useable for space and my understanding is
> that only two (2) will be counted for parallel I/O performance. I am
> nervous about how narrow these RAID 10 sets are but maybe it does not
> matter as much?
> As a side note, i have no good way other than what I can get through
> Profiler to load test the new solution with actual production data.
> Any insight/thoughts would be appreciated.
> Thanks!
> Paul
>|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||If your database was running great on the Raid5 then what basis does the
vendor have to warrant splitting it up? While there are times when
splitting up tables, indexes etc. onto separate arrays will boost
performance you need to know you are doing the right thing. Have you done
any analysis that indicates this will help performance? You are probably
better off making a large RAID 10 and put all the files you now have ont he
Raid 5 on it. Keeping the Logs and Tempdb on their arrays either raid 1 or
10. If you don't know the capacity of the 4 disk Raid 10's and how much I/O
you will have for each you are more likely to make a bottleneck.
--
Andrew J. Kelly SQL MVP
"Paul" <ptimmerm@.gmail.com> wrote in message
news:1149023773.865667.104550@.g10g2000cwb.googlegroups.com...
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>|||Andrew,
These all are very good points. Thank you for raising them. The
recommendation for breaking the database up is based solely on their
poor design. 7 tables (including indexes) in their database make up
75% of the size of the DB. The DB is approximately 200GB. These
tables and a few others (not so large) contain 50 - 200 million records
a piece. It is not until a later release (a couple of years down the
road) that these tables are broken out into 6 or 7 "rolled up" tables.
Given the growth rate, many customers with minimum spec hardware (of
which we are not one) begin to see serious I/O problems that are
"resolved" by placing these tables, and others, on their own dedicated
RAID sets. As I continue to discuss this with others, I am leaning
more towards the large RAID10 array. I could still implement a
multiple data file structure in case we needed to make some moves down
the road, but for now stripe all of the files across the one large
RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
performance bump from going to RAID10, which on top of the abundance of
cache and brand new hardware, should make for a very high performance
system. I am just looking for some way to quantify all of this.
Management likes numbers and statistics.
Another good point of your leads me to a specific question..... Is
there any way for me to measure I/O per database object? Is there any
way for me to estimate how much I/O would be hitting a given RAID set
if I know what tables will be located there?
Thoughts/ideas?
Paul|||Paul wrote:
> Andrew,
> These all are very good points. Thank you for raising them. The
> recommendation for breaking the database up is based solely on their
> poor design. 7 tables (including indexes) in their database make up
> 75% of the size of the DB. The DB is approximately 200GB. These
> tables and a few others (not so large) contain 50 - 200 million records
> a piece. It is not until a later release (a couple of years down the
> road) that these tables are broken out into 6 or 7 "rolled up" tables.
> Given the growth rate, many customers with minimum spec hardware (of
> which we are not one) begin to see serious I/O problems that are
> "resolved" by placing these tables, and others, on their own dedicated
> RAID sets. As I continue to discuss this with others, I am leaning
> more towards the large RAID10 array. I could still implement a
> multiple data file structure in case we needed to make some moves down
> the road, but for now stripe all of the files across the one large
> RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
> performance bump from going to RAID10, which on top of the abundance of
> cache and brand new hardware, should make for a very high performance
> system. I am just looking for some way to quantify all of this.
> Management likes numbers and statistics.
> Another good point of your leads me to a specific question..... Is
> there any way for me to measure I/O per database object? Is there any
> way for me to estimate how much I/O would be hitting a given RAID set
> if I know what tables will be located there?
> Thoughts/ideas?
> Paul
>
Hi Paul
You don't mention anything about your READ/WRITE ratio. If you have far
more reads than writes, it could be that you'd be better off with a RAID
5 array. We have been through the same thoughts for some time ago and
ended up with a 24 disk RAID 5 array. I can't remember the exact
figures, but in our database we have something like 90 % read time so we
where really after a solution that could give a good read performance.
We talked with various storage experts and they more or less all agreed
that a RAID 5 array would give better read performance than a RAID10
assumed we used the same number of disks. That leaves us with a 1,5 TB
array where we're only using approx. 150 GB so there're a lot of "waste"
of space but that doesn't really matter.
We haven't tested the various solutions so actually we don't know if our
RAID 5 array performs better of worse than if we had a RAID 10 array
with the same number of disks.
Regards
Steen|||Linchi Shea wrote:
> Paul;
> 4GB of cache is unlikely to significantly decrease the number of spindles
> you need to stripe across to get the performance. Very large cache size does
> have an tendency to mask the need for more spindles. But that really depends
> on the workloads (on whether the workloads can take advantage of that cache).
>> Is there an "easy" way or rule of thumb that would allow me to
>> translate 10 disk wide RAID5 performance into X disk wide Raid 10
>> performance.
> There is commonly known RAID formula for doing this estimate. Assume that
> the read:write ratio of the workload is 3:1--a very typical ratio, and assume
> that you are using 15,000rpm disks, which can roughly do 180 IOs per second
> (IOps).
> RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O,
> the I/O penalty is 4/(3+4) = 0.57
> RAID 10: Since each write needs two disk I/Os and each read needs one,
> the I/O penalty is 4/(3+2) = 0.80
> For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
> For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
>
Hi Linchi
Do you have any links to this formula/calculation?
--
Regards
Steen Schlüter Persson
DBA|||There is no need to find any link to see how this formula works. Here's how.
Again assume the read:write ratio is 3:1 (or any other ratio you may fancy),
and assume that the total number of I/Os per second submitted to the RAID
device is Total_raid_iops. Hence,
The total number of writes per second = Total_raid_iops * 1/4, and
The total number of reads per second = Total_raid_iops * 3/4
For RAID 10, at the individual disk level, the total number of I/Os per
second for all the disks inside the RAID device are as follows:
The total number of writes per second = Total_raid_iops * 1/4 * 2
The total number of reads per second = Total_raid_iops *3/4 * 1
And
The grand total number of I/Os per second at the disk level
= (Total_raid_iops * 1/4 * 2 + Total_raid * 3/4 * 1)
= Total_raid_iops * (2/4 + 3/4)
= Total_raid_iops * 5/4
Given that a 15,000rmp disk can do about 180 I/Os per second, we have the
following:
Total_raid_iops * 5/4 = 180 * Number_of_disks
So if you know how many I/Os per second you want the RAID 10 device to do,
you can determine how many disks you need in this RAID 10 device, or if you
know the number of disks already in a RAID 10 device, you can determine how
many I/Os per second this device can do given the assumptions on write:read
ratio and the disk rotation speed.
Linchi
"Steen Persson (DK)" wrote:
> Linchi Shea wrote:
> > Paul;
> >
> > 4GB of cache is unlikely to significantly decrease the number of spindles
> > you need to stripe across to get the performance. Very large cache size does
> > have an tendency to mask the need for more spindles. But that really depends
> > on the workloads (on whether the workloads can take advantage of that cache).
> >
> >> Is there an "easy" way or rule of thumb that would allow me to
> >> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> >> performance.
> >
> > There is commonly known RAID formula for doing this estimate. Assume that
> > the read:write ratio of the workload is 3:1--a very typical ratio, and assume
> > that you are using 15,000rpm disks, which can roughly do 180 IOs per second
> > (IOps).
> >
> > RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O,
> > the I/O penalty is 4/(3+4) = 0.57
> > RAID 10: Since each write needs two disk I/Os and each read needs one,
> > the I/O penalty is 4/(3+2) = 0.80
> >
> > For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
> > For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
> >
> Hi Linchi
> Do you have any links to this formula/calculation?
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
Yes, but I wouldn't want to bet my house the results. I trust the results
from controlled tests much better. As mentioned before, I'd use a tool such
as sqlio.exe or IOmeter to profile each RAID configuration. It is not an
extraordinary request to ask your SA or storage folks to give you the two
configurations for you to test. If the system is important, make a case to
your management to allow time for these tests. That would be time much better
spent than dwelling on theoretical estimates.
Once you have the solid test results, you can much better confront (okay
discuss with) your vendor with those numbers and ask them to justify their
recommendations in light of the test results.
Linchi
"Paul" wrote:
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Wednesday, March 28, 2012
RAID Configuration - R10 v. R5 - Spindle Count
We are in the process of procuring a new IBM SAN for our most business
critical SQL database. The database right now runs great on a 10 disk
RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
of the new hardware implementation, we want to go to RAID10 and as per
a vendor recommendation, break the database out into file groups on
separate physical disks in order to boost performance.
I keep getting hung up on spindle count. I have always subscribed to
"the more spinning heads the better". The flaw in this theory appears
to be as the smallest disk you can buy for a given solution gets larger
and larger , where is the trade off? Do you still stripe across as
many disks as possible and end up burning a lot of disk space? Does
the large amount of cache (4GB on our new system) decrease the number
of spindles needed?
Is there an "easy" way or rule of thumb that would allow me to
translate 10 disk wide RAID5 performance into X disk wide Raid 10
performance. In a nutshell, how can I estimate what the following gets
me with regard to reducing the number of spindles necessary:
1. Going to RAID10 from RAID 5
2. New solution has 4GB of cache / old solution had 512MB
If I go with the vendor recommendations for table placement in file
groups I will end up with something in the range of four (4) - two (2)
disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
disks, but only 2 will be useable for space and my understanding is
that only two (2) will be counted for parallel I/O performance. I am
nervous about how narrow these RAID 10 sets are but maybe it does not
matter as much?
As a side note, i have no good way other than what I can get through
Profiler to load test the new solution with actual production data.
Any insight/thoughts would be appreciated.
Thanks!
PaulPaul;
4GB of cache is unlikely to significantly decrease the number of spindles
you need to stripe across to get the performance. Very large cache size does
have an tendency to mask the need for more spindles. But that really depends
on the workloads (on whether the workloads can take advantage of that cache)
.
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance.
There is commonly known RAID formula for doing this estimate. Assume that
the read:write ratio of the workload is 3:1--a very typical ratio, and assum
e
that you are using 15,000rpm disks, which can roughly do 180 IOs per second
(IOps).
RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O
,
the I/O penalty is 4/(3+4) = 0.57
RAID 10: Since each write needs two disk I/Os and each read needs one,
the I/O penalty is 4/(3+2) = 0.80
For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
Of course, this is just a theoretical rough estimate with many factors
absent. For instance, it doesn't consider the effect of the SAN cache. But
it's safe to say that the vendor suggestion of 4 disk RAID 10 is unlikely to
cut it (unless there is anyting specific to the workload of your app).
Running application-specific benechmark is important to verify whether the
disk config is adequate. However, you can also use disk performance tools
such as IOMeter or sqlio.exe to specifically examine the performance of your
disk subsystem outside of SQL Server. I would run sqlio.exe whenever I get a
new storage so that I know what storage performance to expect, and have data
for later comparison when needed.
In general, it's far better and simpler to just stripe across as many disks
as you can (within the disk subsystem limits such as controller throughput).
Linchi
"Paul" wrote:
> We are in the process of procuring a new IBM SAN for our most business
> critical SQL database. The database right now runs great on a 10 disk
> RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
> of the new hardware implementation, we want to go to RAID10 and as per
> a vendor recommendation, break the database out into file groups on
> separate physical disks in order to boost performance.
> I keep getting hung up on spindle count. I have always subscribed to
> "the more spinning heads the better". The flaw in this theory appears
> to be as the smallest disk you can buy for a given solution gets larger
> and larger , where is the trade off? Do you still stripe across as
> many disks as possible and end up burning a lot of disk space? Does
> the large amount of cache (4GB on our new system) decrease the number
> of spindles needed?
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance. In a nutshell, how can I estimate what the following gets
> me with regard to reducing the number of spindles necessary:
> 1. Going to RAID10 from RAID 5
> 2. New solution has 4GB of cache / old solution had 512MB
> If I go with the vendor recommendations for table placement in file
> groups I will end up with something in the range of four (4) - two (2)
> disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
> disks, but only 2 will be useable for space and my understanding is
> that only two (2) will be counted for parallel I/O performance. I am
> nervous about how narrow these RAID 10 sets are but maybe it does not
> matter as much?
> As a side note, i have no good way other than what I can get through
> Profiler to load test the new solution with actual production data.
> Any insight/thoughts would be appreciated.
> Thanks!
> Paul
>|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||If your database was running great on the Raid5 then what basis does the
vendor have to warrant splitting it up? While there are times when
splitting up tables, indexes etc. onto separate arrays will boost
performance you need to know you are doing the right thing. Have you done
any analysis that indicates this will help performance? You are probably
better off making a large RAID 10 and put all the files you now have ont he
Raid 5 on it. Keeping the Logs and Tempdb on their arrays either raid 1 or
10. If you don't know the capacity of the 4 disk Raid 10's and how much I/O
you will have for each you are more likely to make a bottleneck.
Andrew J. Kelly SQL MVP
"Paul" <ptimmerm@.gmail.com> wrote in message
news:1149023773.865667.104550@.g10g2000cwb.googlegroups.com...
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>|||Andrew,
These all are very good points. Thank you for raising them. The
recommendation for breaking the database up is based solely on their
poor design. 7 tables (including indexes) in their database make up
75% of the size of the DB. The DB is approximately 200GB. These
tables and a few others (not so large) contain 50 - 200 million records
a piece. It is not until a later release (a couple of years down the
road) that these tables are broken out into 6 or 7 "rolled up" tables.
Given the growth rate, many customers with minimum spec hardware (of
which we are not one) begin to see serious I/O problems that are
"resolved" by placing these tables, and others, on their own dedicated
RAID sets. As I continue to discuss this with others, I am leaning
more towards the large RAID10 array. I could still implement a
multiple data file structure in case we needed to make some moves down
the road, but for now stripe all of the files across the one large
RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
performance bump from going to RAID10, which on top of the abundance of
cache and brand new hardware, should make for a very high performance
system. I am just looking for some way to quantify all of this.
Management likes numbers and statistics.
Another good point of your leads me to a specific question..... Is
there any way for me to measure I/O per database object? Is there any
way for me to estimate how much I/O would be hitting a given RAID set
if I know what tables will be located there?
Thoughts/ideas?
Paul|||Paul wrote:
> Andrew,
> These all are very good points. Thank you for raising them. The
> recommendation for breaking the database up is based solely on their
> poor design. 7 tables (including indexes) in their database make up
> 75% of the size of the DB. The DB is approximately 200GB. These
> tables and a few others (not so large) contain 50 - 200 million records
> a piece. It is not until a later release (a couple of years down the
> road) that these tables are broken out into 6 or 7 "rolled up" tables.
> Given the growth rate, many customers with minimum spec hardware (of
> which we are not one) begin to see serious I/O problems that are
> "resolved" by placing these tables, and others, on their own dedicated
> RAID sets. As I continue to discuss this with others, I am leaning
> more towards the large RAID10 array. I could still implement a
> multiple data file structure in case we needed to make some moves down
> the road, but for now stripe all of the files across the one large
> RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
> performance bump from going to RAID10, which on top of the abundance of
> cache and brand new hardware, should make for a very high performance
> system. I am just looking for some way to quantify all of this.
> Management likes numbers and statistics.
> Another good point of your leads me to a specific question..... Is
> there any way for me to measure I/O per database object? Is there any
> way for me to estimate how much I/O would be hitting a given RAID set
> if I know what tables will be located there?
> Thoughts/ideas?
> Paul
>
Hi Paul
You don't mention anything about your READ/WRITE ratio. If you have far
more reads than writes, it could be that you'd be better off with a RAID
5 array. We have been through the same thoughts for some time ago and
ended up with a 24 disk RAID 5 array. I can't remember the exact
figures, but in our database we have something like 90 % read time so we
where really after a solution that could give a good read performance.
We talked with various storage experts and they more or less all agreed
that a RAID 5 array would give better read performance than a RAID10
assumed we used the same number of disks. That leaves us with a 1,5 TB
array where we're only using approx. 150 GB so there're a lot of "waste"
of space but that doesn't really matter.
We haven't tested the various solutions so actually we don't know if our
RAID 5 array performs better of worse than if we had a RAID 10 array
with the same number of disks.
Regards
Steen|||Linchi Shea wrote:
> Paul;
> 4GB of cache is unlikely to significantly decrease the number of spindles
> you need to stripe across to get the performance. Very large cache size do
es
> have an tendency to mask the need for more spindles. But that really depen
ds
> on the workloads (on whether the workloads can take advantage of that cach
e).
>
> There is commonly known RAID formula for doing this estimate. Assume that
> the read:write ratio of the workload is 3:1--a very typical ratio, and XXX
ume
> that you are using 15,000rpm disks, which can roughly do 180 IOs per secon
d
> (IOps).
> RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I
/O,
> the I/O penalty is 4/(3+4) = 0.57
> RAID 10: Since each write needs two disk I/Os and each read needs one,
> the I/O penalty is 4/(3+2) = 0.80
> For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
> For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
>
Hi Linchi
Do you have any links to this formula/calculation?
Regards
Steen Schlüter Persson
DBA|||There is no need to find any link to see how this formula works. Here's how.
Again assume the read:write ratio is 3:1 (or any other ratio you may fancy),
and assume that the total number of I/Os per second submitted to the RAID
device is Total_raid_iops. Hence,
The total number of writes per second = Total_raid_iops * 1/4, and
The total number of reads per second = Total_raid_iops * 3/4
For RAID 10, at the individual disk level, the total number of I/Os per
second for all the disks inside the RAID device are as follows:
The total number of writes per second = Total_raid_iops * 1/4 * 2
The total number of reads per second = Total_raid_iops *3/4 * 1
And
The grand total number of I/Os per second at the disk level
= (Total_raid_iops * 1/4 * 2 + Total_raid * 3/4 * 1)
= Total_raid_iops * (2/4 + 3/4)
= Total_raid_iops * 5/4
Given that a 15,000rmp disk can do about 180 I/Os per second, we have the
following:
Total_raid_iops * 5/4 = 180 * Number_of_disks
So if you know how many I/Os per second you want the RAID 10 device to do,
you can determine how many disks you need in this RAID 10 device, or if you
know the number of disks already in a RAID 10 device, you can determine how
many I/Os per second this device can do given the assumptions on write:read
ratio and the disk rotation speed.
Linchi
"Steen Persson (DK)" wrote:
> Linchi Shea wrote:
> Hi Linchi
> Do you have any links to this formula/calculation?
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
Yes, but I wouldn't want to bet my house the results. I trust the results
from controlled tests much better. As mentioned before, I'd use a tool such
as sqlio.exe or IOmeter to profile each RAID configuration. It is not an
extraordinary request to ask your SA or storage folks to give you the two
configurations for you to test. If the system is important, make a case to
your management to allow time for these tests. That would be time much bette
r
spent than dwelling on theoretical estimates.
Once you have the solid test results, you can much better confront (okay
discuss with) your vendor with those numbers and ask them to justify their
recommendations in light of the test results.
Linchi
"Paul" wrote:
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>sql
critical SQL database. The database right now runs great on a 10 disk
RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
of the new hardware implementation, we want to go to RAID10 and as per
a vendor recommendation, break the database out into file groups on
separate physical disks in order to boost performance.
I keep getting hung up on spindle count. I have always subscribed to
"the more spinning heads the better". The flaw in this theory appears
to be as the smallest disk you can buy for a given solution gets larger
and larger , where is the trade off? Do you still stripe across as
many disks as possible and end up burning a lot of disk space? Does
the large amount of cache (4GB on our new system) decrease the number
of spindles needed?
Is there an "easy" way or rule of thumb that would allow me to
translate 10 disk wide RAID5 performance into X disk wide Raid 10
performance. In a nutshell, how can I estimate what the following gets
me with regard to reducing the number of spindles necessary:
1. Going to RAID10 from RAID 5
2. New solution has 4GB of cache / old solution had 512MB
If I go with the vendor recommendations for table placement in file
groups I will end up with something in the range of four (4) - two (2)
disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
disks, but only 2 will be useable for space and my understanding is
that only two (2) will be counted for parallel I/O performance. I am
nervous about how narrow these RAID 10 sets are but maybe it does not
matter as much?
As a side note, i have no good way other than what I can get through
Profiler to load test the new solution with actual production data.
Any insight/thoughts would be appreciated.
Thanks!
PaulPaul;
4GB of cache is unlikely to significantly decrease the number of spindles
you need to stripe across to get the performance. Very large cache size does
have an tendency to mask the need for more spindles. But that really depends
on the workloads (on whether the workloads can take advantage of that cache)
.
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance.
There is commonly known RAID formula for doing this estimate. Assume that
the read:write ratio of the workload is 3:1--a very typical ratio, and assum
e
that you are using 15,000rpm disks, which can roughly do 180 IOs per second
(IOps).
RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O
,
the I/O penalty is 4/(3+4) = 0.57
RAID 10: Since each write needs two disk I/Os and each read needs one,
the I/O penalty is 4/(3+2) = 0.80
For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
Of course, this is just a theoretical rough estimate with many factors
absent. For instance, it doesn't consider the effect of the SAN cache. But
it's safe to say that the vendor suggestion of 4 disk RAID 10 is unlikely to
cut it (unless there is anyting specific to the workload of your app).
Running application-specific benechmark is important to verify whether the
disk config is adequate. However, you can also use disk performance tools
such as IOMeter or sqlio.exe to specifically examine the performance of your
disk subsystem outside of SQL Server. I would run sqlio.exe whenever I get a
new storage so that I know what storage performance to expect, and have data
for later comparison when needed.
In general, it's far better and simpler to just stripe across as many disks
as you can (within the disk subsystem limits such as controller throughput).
Linchi
"Paul" wrote:
> We are in the process of procuring a new IBM SAN for our most business
> critical SQL database. The database right now runs great on a 10 disk
> RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
> of the new hardware implementation, we want to go to RAID10 and as per
> a vendor recommendation, break the database out into file groups on
> separate physical disks in order to boost performance.
> I keep getting hung up on spindle count. I have always subscribed to
> "the more spinning heads the better". The flaw in this theory appears
> to be as the smallest disk you can buy for a given solution gets larger
> and larger , where is the trade off? Do you still stripe across as
> many disks as possible and end up burning a lot of disk space? Does
> the large amount of cache (4GB on our new system) decrease the number
> of spindles needed?
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance. In a nutshell, how can I estimate what the following gets
> me with regard to reducing the number of spindles necessary:
> 1. Going to RAID10 from RAID 5
> 2. New solution has 4GB of cache / old solution had 512MB
> If I go with the vendor recommendations for table placement in file
> groups I will end up with something in the range of four (4) - two (2)
> disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
> disks, but only 2 will be useable for space and my understanding is
> that only two (2) will be counted for parallel I/O performance. I am
> nervous about how narrow these RAID 10 sets are but maybe it does not
> matter as much?
> As a side note, i have no good way other than what I can get through
> Profiler to load test the new solution with actual production data.
> Any insight/thoughts would be appreciated.
> Thanks!
> Paul
>|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||If your database was running great on the Raid5 then what basis does the
vendor have to warrant splitting it up? While there are times when
splitting up tables, indexes etc. onto separate arrays will boost
performance you need to know you are doing the right thing. Have you done
any analysis that indicates this will help performance? You are probably
better off making a large RAID 10 and put all the files you now have ont he
Raid 5 on it. Keeping the Logs and Tempdb on their arrays either raid 1 or
10. If you don't know the capacity of the 4 disk Raid 10's and how much I/O
you will have for each you are more likely to make a bottleneck.
Andrew J. Kelly SQL MVP
"Paul" <ptimmerm@.gmail.com> wrote in message
news:1149023773.865667.104550@.g10g2000cwb.googlegroups.com...
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>|||Andrew,
These all are very good points. Thank you for raising them. The
recommendation for breaking the database up is based solely on their
poor design. 7 tables (including indexes) in their database make up
75% of the size of the DB. The DB is approximately 200GB. These
tables and a few others (not so large) contain 50 - 200 million records
a piece. It is not until a later release (a couple of years down the
road) that these tables are broken out into 6 or 7 "rolled up" tables.
Given the growth rate, many customers with minimum spec hardware (of
which we are not one) begin to see serious I/O problems that are
"resolved" by placing these tables, and others, on their own dedicated
RAID sets. As I continue to discuss this with others, I am leaning
more towards the large RAID10 array. I could still implement a
multiple data file structure in case we needed to make some moves down
the road, but for now stripe all of the files across the one large
RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
performance bump from going to RAID10, which on top of the abundance of
cache and brand new hardware, should make for a very high performance
system. I am just looking for some way to quantify all of this.
Management likes numbers and statistics.
Another good point of your leads me to a specific question..... Is
there any way for me to measure I/O per database object? Is there any
way for me to estimate how much I/O would be hitting a given RAID set
if I know what tables will be located there?
Thoughts/ideas?
Paul|||Paul wrote:
> Andrew,
> These all are very good points. Thank you for raising them. The
> recommendation for breaking the database up is based solely on their
> poor design. 7 tables (including indexes) in their database make up
> 75% of the size of the DB. The DB is approximately 200GB. These
> tables and a few others (not so large) contain 50 - 200 million records
> a piece. It is not until a later release (a couple of years down the
> road) that these tables are broken out into 6 or 7 "rolled up" tables.
> Given the growth rate, many customers with minimum spec hardware (of
> which we are not one) begin to see serious I/O problems that are
> "resolved" by placing these tables, and others, on their own dedicated
> RAID sets. As I continue to discuss this with others, I am leaning
> more towards the large RAID10 array. I could still implement a
> multiple data file structure in case we needed to make some moves down
> the road, but for now stripe all of the files across the one large
> RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
> performance bump from going to RAID10, which on top of the abundance of
> cache and brand new hardware, should make for a very high performance
> system. I am just looking for some way to quantify all of this.
> Management likes numbers and statistics.
> Another good point of your leads me to a specific question..... Is
> there any way for me to measure I/O per database object? Is there any
> way for me to estimate how much I/O would be hitting a given RAID set
> if I know what tables will be located there?
> Thoughts/ideas?
> Paul
>
Hi Paul
You don't mention anything about your READ/WRITE ratio. If you have far
more reads than writes, it could be that you'd be better off with a RAID
5 array. We have been through the same thoughts for some time ago and
ended up with a 24 disk RAID 5 array. I can't remember the exact
figures, but in our database we have something like 90 % read time so we
where really after a solution that could give a good read performance.
We talked with various storage experts and they more or less all agreed
that a RAID 5 array would give better read performance than a RAID10
assumed we used the same number of disks. That leaves us with a 1,5 TB
array where we're only using approx. 150 GB so there're a lot of "waste"
of space but that doesn't really matter.
We haven't tested the various solutions so actually we don't know if our
RAID 5 array performs better of worse than if we had a RAID 10 array
with the same number of disks.
Regards
Steen|||Linchi Shea wrote:
> Paul;
> 4GB of cache is unlikely to significantly decrease the number of spindles
> you need to stripe across to get the performance. Very large cache size do
es
> have an tendency to mask the need for more spindles. But that really depen
ds
> on the workloads (on whether the workloads can take advantage of that cach
e).
>
> There is commonly known RAID formula for doing this estimate. Assume that
> the read:write ratio of the workload is 3:1--a very typical ratio, and XXX
ume
> that you are using 15,000rpm disks, which can roughly do 180 IOs per secon
d
> (IOps).
> RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I
/O,
> the I/O penalty is 4/(3+4) = 0.57
> RAID 10: Since each write needs two disk I/Os and each read needs one,
> the I/O penalty is 4/(3+2) = 0.80
> For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
> For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
>
Hi Linchi
Do you have any links to this formula/calculation?
Regards
Steen Schlüter Persson
DBA|||There is no need to find any link to see how this formula works. Here's how.
Again assume the read:write ratio is 3:1 (or any other ratio you may fancy),
and assume that the total number of I/Os per second submitted to the RAID
device is Total_raid_iops. Hence,
The total number of writes per second = Total_raid_iops * 1/4, and
The total number of reads per second = Total_raid_iops * 3/4
For RAID 10, at the individual disk level, the total number of I/Os per
second for all the disks inside the RAID device are as follows:
The total number of writes per second = Total_raid_iops * 1/4 * 2
The total number of reads per second = Total_raid_iops *3/4 * 1
And
The grand total number of I/Os per second at the disk level
= (Total_raid_iops * 1/4 * 2 + Total_raid * 3/4 * 1)
= Total_raid_iops * (2/4 + 3/4)
= Total_raid_iops * 5/4
Given that a 15,000rmp disk can do about 180 I/Os per second, we have the
following:
Total_raid_iops * 5/4 = 180 * Number_of_disks
So if you know how many I/Os per second you want the RAID 10 device to do,
you can determine how many disks you need in this RAID 10 device, or if you
know the number of disks already in a RAID 10 device, you can determine how
many I/Os per second this device can do given the assumptions on write:read
ratio and the disk rotation speed.
Linchi
"Steen Persson (DK)" wrote:
> Linchi Shea wrote:
> Hi Linchi
> Do you have any links to this formula/calculation?
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
Yes, but I wouldn't want to bet my house the results. I trust the results
from controlled tests much better. As mentioned before, I'd use a tool such
as sqlio.exe or IOmeter to profile each RAID configuration. It is not an
extraordinary request to ask your SA or storage folks to give you the two
configurations for you to test. If the system is important, make a case to
your management to allow time for these tests. That would be time much bette
r
spent than dwelling on theoretical estimates.
Once you have the solid test results, you can much better confront (okay
discuss with) your vendor with those numbers and ask them to justify their
recommendations in light of the test results.
Linchi
"Paul" wrote:
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>sql
raid 5 v raid 10
We are looking to run a web based CRM package that runs on SQL 2005.
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/views
If you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegr oups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegr oups.com...
>
|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disks
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>
|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>
|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>
|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/views
If you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegr oups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegr oups.com...
>
|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disks
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>
|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>
|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>
|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
raid 5 v raid 10
We are looking to run a web based CRM package that runs on SQL 2005.
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/viewsIf you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disk
s
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
C=E9dric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a =E9crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
>=20
> What are other peoples experiences/views|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-driv
e
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answe
r.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimat
e.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet you
r
> requirements.
> Linchi
> "jp" wrote:
>|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-driv
e
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answe
r.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimat
e.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet you
r
> requirements.
> Linchi
> "jp" wrote:
>
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/viewsIf you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disk
s
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
C=E9dric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a =E9crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
>=20
> What are other peoples experiences/views|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>
>|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-driv
e
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answe
r.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimat
e.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet you
r
> requirements.
> Linchi
> "jp" wrote:
>|||Thanks for the info
Linchi Shea wrote:[vbcol=seagreen]
> Theoretical pros and cons aside, whether or not you need a three-plus-driv
e
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answe
r.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimat
e.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet you
r
> requirements.
> Linchi
> "jp" wrote:
>
raid 5 v raid 10
We are looking to run a web based CRM package that runs on SQL 2005.
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/viewsIf you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disks
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
C=E9dric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a =E9crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> > What are other peoples experiences/views|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/viewsIf you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disks
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
C=E9dric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a =E9crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> > What are other peoples experiences/views|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >
Saturday, February 25, 2012
Queue Reader fails
I'm running a clustered distributor on SQL 2000 sp3a.
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
Monday, February 20, 2012
Queue reader agent aborting
Hi,
I'm running transactional replication (SQL Server 2000) with queued updating
between two locations (New Jersey & Arizona). Generally it runs fine, but
about every other week the Queue Reader agent fails with an error message
"Queue reader aborting. The step failed". I can't find any information
regarding the source or nature of the error, and if I try to restart the
agent if fails immediately with the same error. The only way I've been able
to get around the error is to drop the subscription and redo the snapshot.
Does anyone have any suggestions how to determine the source of this error?
Thanks for any help...
Ed
Can you do logging for your queue reader and post the error message you get back here?
Follow the instructions in this kb for more information on how to enable it.
http://support.microsoft.com/default...b;EN-US;312292
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> I'm running transactional replication (SQL Server 2000) with queued updating
> between two locations (New Jersey & Arizona). Generally it runs fine, but
> about every other week the Queue Reader agent fails with an error message
> "Queue reader aborting. The step failed". I can't find any information
> regarding the source or nature of the error, and if I try to restart the
> agent if fails immediately with the same error. The only way I've been able
> to get around the error is to drop the subscription and redo the snapshot.
> Does anyone have any suggestions how to determine the source of this error?
> Thanks for any help...
> Ed
>
>
|||Thanks, I'll try adding that logging parameter. What's the best way to stop
the queue reader agent? In the past, I stopped it from enterprise manager by
right clicking and selecting "Stop Agent", but once I do that it often won't
start again...
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> Can you do logging for your queue reader and post the error message you
get back here?
> Follow the instructions in this kb for more information on how to enable
it.[vbcol=seagreen]
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
updating[vbcol=seagreen]
but[vbcol=seagreen]
message[vbcol=seagreen]
able[vbcol=seagreen]
snapshot.[vbcol=seagreen]
error?[vbcol=seagreen]
|||For me the only reliable way to stop the queue reader and log reader agents is to stop the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Thanks, I'll try adding that logging parameter. What's the best way to stop
> the queue reader agent? In the past, I stopped it from enterprise manager by
> right clicking and selecting "Stop Agent", but once I do that it often won't
> start again...
> Ed
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> get back here?
> it.
> updating
> but
> message
> able
> snapshot.
> error?
>
>
|||Thanks again. Now I'm just waiting for it to fail...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:40F318EA-ACF6-4B3A-865D-7B33B00F305E@.microsoft.com...
> For me the only reliable way to stop the queue reader and log reader
agents is to stop the SQL Server agent.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
stop[vbcol=seagreen]
manager by[vbcol=seagreen]
won't[vbcol=seagreen]
you[vbcol=seagreen]
enable[vbcol=seagreen]
fine,[vbcol=seagreen]
information[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
|||Hi,
The queue reader failed. Below is the output from the log. Any insight you
have would be greatly appreciated.
Ed
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [SLIM].12
Connecting to QueueReader 'SLIM.Billing'
Server: SLIM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
[6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
MSqreader_agents
Server: SLIM, Database Billing : ODBC Error:Invalid object name
'MSqreader_agents'.
Unable to connect to Local Distributor
Queue Reader aborting
|||can you check to see if this table exists in your local distribution database?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> The queue reader failed. Below is the output from the log. Any insight you
> have would be greatly appreciated.
> Ed
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [SLIM].12
> Connecting to QueueReader 'SLIM.Billing'
> Server: SLIM
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
> [6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
> MSqreader_agents
> Server: SLIM, Database Billing : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> Unable to connect to Local Distributor
> Queue Reader aborting
>
>
|||The table does exist - but I thought it was odd that the statements
contained references to the "Billing" database. That's a database on the
server, but it's not part of replication. Digging into the job under SQL
Server Agent, I saw that the queue reader step was set to run in the
"Billing" database. I'm guessing someone changed that setting
unintentionally. I changed it to run under the "distribution" database, and
the step restarted fine.
Thanks for the tip on logging the output - it would have been impossible to
figure this out without it
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:810FBB09-2770-4D9C-A07B-ED917F4B9E32@.microsoft.com...
> can you check to see if this table exists in your local distribution
database?[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
you[vbcol=seagreen]
(Billing)'[vbcol=seagreen]
I'm running transactional replication (SQL Server 2000) with queued updating
between two locations (New Jersey & Arizona). Generally it runs fine, but
about every other week the Queue Reader agent fails with an error message
"Queue reader aborting. The step failed". I can't find any information
regarding the source or nature of the error, and if I try to restart the
agent if fails immediately with the same error. The only way I've been able
to get around the error is to drop the subscription and redo the snapshot.
Does anyone have any suggestions how to determine the source of this error?
Thanks for any help...
Ed
Can you do logging for your queue reader and post the error message you get back here?
Follow the instructions in this kb for more information on how to enable it.
http://support.microsoft.com/default...b;EN-US;312292
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> I'm running transactional replication (SQL Server 2000) with queued updating
> between two locations (New Jersey & Arizona). Generally it runs fine, but
> about every other week the Queue Reader agent fails with an error message
> "Queue reader aborting. The step failed". I can't find any information
> regarding the source or nature of the error, and if I try to restart the
> agent if fails immediately with the same error. The only way I've been able
> to get around the error is to drop the subscription and redo the snapshot.
> Does anyone have any suggestions how to determine the source of this error?
> Thanks for any help...
> Ed
>
>
|||Thanks, I'll try adding that logging parameter. What's the best way to stop
the queue reader agent? In the past, I stopped it from enterprise manager by
right clicking and selecting "Stop Agent", but once I do that it often won't
start again...
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> Can you do logging for your queue reader and post the error message you
get back here?
> Follow the instructions in this kb for more information on how to enable
it.[vbcol=seagreen]
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
updating[vbcol=seagreen]
but[vbcol=seagreen]
message[vbcol=seagreen]
able[vbcol=seagreen]
snapshot.[vbcol=seagreen]
error?[vbcol=seagreen]
|||For me the only reliable way to stop the queue reader and log reader agents is to stop the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Thanks, I'll try adding that logging parameter. What's the best way to stop
> the queue reader agent? In the past, I stopped it from enterprise manager by
> right clicking and selecting "Stop Agent", but once I do that it often won't
> start again...
> Ed
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> get back here?
> it.
> updating
> but
> message
> able
> snapshot.
> error?
>
>
|||Thanks again. Now I'm just waiting for it to fail...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:40F318EA-ACF6-4B3A-865D-7B33B00F305E@.microsoft.com...
> For me the only reliable way to stop the queue reader and log reader
agents is to stop the SQL Server agent.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
stop[vbcol=seagreen]
manager by[vbcol=seagreen]
won't[vbcol=seagreen]
you[vbcol=seagreen]
enable[vbcol=seagreen]
fine,[vbcol=seagreen]
information[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
|||Hi,
The queue reader failed. Below is the output from the log. Any insight you
have would be greatly appreciated.
Ed
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [SLIM].12
Connecting to QueueReader 'SLIM.Billing'
Server: SLIM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
[6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
MSqreader_agents
Server: SLIM, Database Billing : ODBC Error:Invalid object name
'MSqreader_agents'.
Unable to connect to Local Distributor
Queue Reader aborting
|||can you check to see if this table exists in your local distribution database?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> The queue reader failed. Below is the output from the log. Any insight you
> have would be greatly appreciated.
> Ed
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [SLIM].12
> Connecting to QueueReader 'SLIM.Billing'
> Server: SLIM
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
> [6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
> MSqreader_agents
> Server: SLIM, Database Billing : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> Unable to connect to Local Distributor
> Queue Reader aborting
>
>
|||The table does exist - but I thought it was odd that the statements
contained references to the "Billing" database. That's a database on the
server, but it's not part of replication. Digging into the job under SQL
Server Agent, I saw that the queue reader step was set to run in the
"Billing" database. I'm guessing someone changed that setting
unintentionally. I changed it to run under the "distribution" database, and
the step restarted fine.
Thanks for the tip on logging the output - it would have been impossible to
figure this out without it
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:810FBB09-2770-4D9C-A07B-ED917F4B9E32@.microsoft.com...
> can you check to see if this table exists in your local distribution
database?[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
you[vbcol=seagreen]
(Billing)'[vbcol=seagreen]
Subscribe to:
Posts (Atom)