Data Storage - Running SQL database off of SAN -- is it feasible ?

Author

Running SQL database off of SAN -- is it feasible ?

Larry David

2005-03-14, 8:45 pm

Hi,

I'm trying to create a home for a database. The database will ultimately
reach approximately 200Gb in size. Usage patterns will consist of
approximately 80% read, 20% write. The vast majority of the reads will
require random access to small chunks of data (less than 64k).

Two SQL Server machines need to be connected to the same database. The
primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
Server will be a machine of lesser capabilities and will only come online if
the primary data server fails. This concept, as it has been explained to me,
is known as "failover."

My job is to investigate various solutions for housing the data. I am
considering the following storage device:

http://www-1.ibm.com/servers/storag...4100/index.html

This device holds 14 drives and transmits data via a mechanism called
Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
Here are my questions and concerns:

1) Is this device suitable for hosting a database meeting the
characteristics and requirements that I've described?

2) Assuming that I placed 14 drives in this storage device, how would I
partition it? I've read that the transaction log should be on a separate
RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
and 3-14 as RAID 1+0 ? What do you recommend?

3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
relevant concern given that most I/O will be random, not sequential, and
therefore I may not even be hitting the 256MBps cap anyway?

I won't actually be the one installing the storage system. I'll
obviously need a professional for that. I just want to do enough research to
determine whether or not the slick salesman who sells us the solution is
giving me accurate information regarding the suitability of various storage
solutions.

Thanks for the help,

David

Aaron [SQL Server MVP]

2005-03-14, 8:45 pm

> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
> Server will be a machine of lesser capabilities and will only come online if
> the primary data server fails. This concept, as it has been explained to me,
> is known as "failover."
>
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
>
> http://www-1.ibm.com/servers/storag...4100/index.html

We use EMC SAN devices. Pricy but well worth it.

> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?

You probably shouldn't have any problems in that configuration.

> 2) Assuming that I placed 14 drives in this storage device, how would I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?

I recommend you go over this with the people who configure your SAN. They
know its specific implementations, where it is fast, where it can lag, etc.
Hopefully they have SQL Server experts on board (likely mostly DB2 people),
if not, see above... Have EMC come in and talk to you before you decide on a
specific device (and even if you don't go with them, they'll likely give you
relevant information that at worst you can use as confirmation to what the
other guys tell you).

> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?

You probably won't hit it much, and if you do, you will probably be more
constrained at that point by the network bandwidth sending it over the wire
than by reading it from disk.

> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various storage
> solutions.

I can't speak for other vendors, because I was involved too late in the
process, but EMC was very up front with us, stands by their product, and
aren't out to screw you. I would bet I speak for most SAN vendors when I
say that they want to make money but they want to do so by providing you
with the best solution for your scenario, as you present it to them.

NAS, on the other hand, is a completely different ballgame. :-)

AbUser

2005-03-15, 1:54 am

quote:

I'm trying to create a home for a database. The database will ultimately
reach approximately 200Gb in size. Usage patterns will consist of
approximately 80% read, 20% write. The vast majority of the reads will
require random access to small chunks of data (less than 64k).

quote:

Two SQL Server machines need to be
connected to the same database. The
primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
Server will be a machine of lesser capabilities and will only come online if
the primary data server fails. This concept, as it has been explained to me,
is known as "failover."

You can use Microsoft Cluster Service (included in Advanced Server but requires Enterprise SQL Server I think - check the sys reqs).
There are vendors that provide failover on Standard Server but as you'll have 4 CPU on the bigger server you may as well get Enterprise Server...

quote:

My job is to investigate various solutions for housing the data. I am
considering the following storage device:

That IBM box is probably the lowest-end FC disk array they have. For only two servers (and only one active at that) you can get that IBM box you mentioned or an HP MSA 1500 cs or an EMC AX100.
Prices for HP MSA 15000 cs and EMC AX100 (same as Dell AX100) can be found online. This PDF is quite informative when it comes to planning and layout:
http://www.dell.com/downloads/globa...40151-Singh.pdf
If you have a bit more money, consider EMC CX300, HP EVA3000.

quote:

3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
equates to only 256MBps, as opposed to SCSI which is 320MBps.

And I'm concerned you didn't get your numbers right.

Get two (or more) quotes, don't talk to only one provider.

Faeandar

2005-03-15, 7:45 am

On Mon, 14 Mar 2005 16:22:56 -0800, "Larry David" <invalid@bogus.bum>
wrote:

>Hi,
>
> I'm trying to create a home for a database. The database will ultimately
>reach approximately 200Gb in size. Usage patterns will consist of
>approximately 80% read, 20% write. The vast majority of the reads will
>require random access to small chunks of data (less than 64k).
>
> Two SQL Server machines need to be connected to the same database. The
>primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
>Server will be a machine of lesser capabilities and will only come online if
>the primary data server fails. This concept, as it has been explained to me,
>is known as "failover."
>
> My job is to investigate various solutions for housing the data. I am
>considering the following storage device:
>
>http://www-1.ibm.com/servers/storag...4100/index.html
>
> This device holds 14 drives and transmits data via a mechanism called
>Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
>Here are my questions and concerns:
>
> 1) Is this device suitable for hosting a database meeting the
>characteristics and requirements that I've described?

It is currently the premiere method for hosting databases. Not the
IBM specifically but this type of config.

>
> 2) Assuming that I placed 14 drives in this storage device, how would I
>partition it? I've read that the transaction log should be on a separate
>RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
>and 3-14 as RAID 1+0 ? What do you recommend?

It really depends on performance requirements. That config would work
but the transaction logs would slow you down considerably. You would
basically be writing to only 1 disk, and that one disk won't be fast
enough (likely) to keep up with what you want. The concept is correct
though.

>
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
>equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
>relevant concern given that most I/O will be random, not sequential, and
>therefore I may not even be hitting the 256MBps cap anyway?

It maxes out at 2gbs *per channel*. A decent sized host can easily
push 3 of those giving you over 6gbs. Not shabby really. The more
interesting question is whether or not the 14 drives you mention can
push 2gbs. If streaming yes, random I doubt it.
SCSI is great, make no mistake, but it has limitations FC does not.
If you can only have a single connection to the disks then SCSI would
be your best bet. But if you build it for multiple connections (2 or
more HBA's, a FC switch, multiple storage ports, etc) FC is really the
better choice. The ability to do all of that is what makes FC
stronger than SCSI. That and distance....

>
> I won't actually be the one installing the storage system. I'll
>obviously need a professional for that. I just want to do enough research to
>determine whether or not the slick salesman who sells us the solution is
>giving me accurate information regarding the suitability of various storage
>solutions.

If you are going to be involved with this setup after the fact then
you better damn well be the one to install it. Get help from the
vendor, hire consultants, whatever. But make sure *you* are the one
installing it, with their help. There's no better way to understand
it.

And there was a previous post about EMC, I would stay away from those
guys if at all possible. Contrary to the posters belief, EMC really
is out to screw you. They are conniving bastards that will go to your
boss, your bosses boss, and even the CIO/CEO if they feel you are a
worthwhile reference customer. I've seen it happen.
I even had an SE stand in front of our group and say they "had to
offer raid 5 in their arrays now because they couldn't keep charging
customers for double the disk." I asked him if he understood what he
just said and there was intense back-peddling and shovelling.

Now to be less venemous, their DMX product really is solid. Fast,
reliable, good iron. Totally not worth dealing with them though. HDS
has comparable hardware, even the mid-range vendors are closing the
gap on features and availability.

The ds4000 is mid-range but may suit your needs perfectly, if you know
all the requirements.

~F

Larry David

2005-03-15, 7:45 am

Faeandar,

Thanks for the great response. I have a couple of follow-up questions:

> It really depends on performance requirements. That config would work
> but the transaction logs would slow you down considerably.
> You would
> basically be writing to only 1 disk, and that one disk won't be fast
> enough (likely) to keep up with what you want.

Perhaps I should consider increasing the number of drives allocated to
the transaction logs? Someone in another thread suggested using 4 drives,
arranged as RAID 1+0.

> It maxes out at 2gbs *per channel*. A decent sized host can easily
> push 3 of those giving you over 6gbs.

Just to make sure that I understand your example, are you saying that
the hypothetical SQL server machine would have 3 Fibre Channel host bus
adapter (HBA) cards? Do these cards run on the PCI bus? Is there any chance
that I'd saturate the PCI bus? I understand your assertion that it's
probably a moot point since 14 drives can't push 2gbps anyway. I'm just
asking for educational reasons.

> If you are going to be involved with this setup after the fact then
> you better damn well be the one to install it. Get help from the
> vendor, hire consultants, whatever. But make sure *you* are the one
> installing it, with their help. There's no better way to understand
> it.

Thanks for the encouragement. I'm a software developer by training and I
think people would prefer that I not ask questions and just leave the setup
to the pros, but I really love learning and am very interested in
understanding this stuff. I'd love to be involved in the installation
process if at all possible.

Thanks again for the feedback!

David

Malcolm Weir

2005-03-15, 7:45 am

On Mon, 14 Mar 2005 16:22:56 -0800, "Larry David" <invalid@bogus.bum>
wrote:

[ Snip ]

> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
>equates to only 256MBps,

No, it doesn't. It equates to 212MB/sec.

> as opposed to SCSI which is 320MBps. Is this even a
>relevant concern given that most I/O will be random, not sequential, and
>therefore I may not even be hitting the 256MBps cap anyway?

PARALLEL SCSI (emphasis added, since FC is SCSI, too, in this
instance) peaks at 320MB/sec. You won't get 320MB/sec out of it.

And no, you won't care about the difference, and if you did, it's
easier to add another FC link than another U320 one, and 4Gbps Fibre
is on its way.

>David

Malc.

Dennis Ingram

2005-03-15, 5:45 pm

Just as a matter of interest, we are currently completely a project
which migrated a large Oracle database from 7-year old AIX system to
the new generation of machines. For storage we chose NAS, specifically
NetApp Filers, the connection between the host and the NAS device
being a 1Gbps ethernet.

I really did not believe a skinny 1Gbps could provide the throughput
we needed.

To start with, we developed using 4 x 146GB internal SCSI disks, and
the new system was a satisfying 9x faster than the old one (using a
smaller copy of the database).

So then we borrowed a Filer with 8 x 144GB drives from the local
supplier. This was the bottom-of-the-range FAS250 model. The result
was that it was 10x faster.
I still do not completely understand why. One thing of course is that
the I/O characteristics of an RDBMS is not like ftp'ing a file... and
in case you are wondering, our application was keeping it very busy
with some heavy-duty batch processing, so it was not like random light
user transactions.

Anyway, just thought you might like some reassurance that a 2Gbps SAN
network can hack it. I'm not promoting NAS over SAN, but the reason we
went for NAS was simply that we have a small team here and really
valued the ease of management.

Cheers
Dennis

"Larry David" <invalid@bogus.bum> wrote in message news:<v76dndQtatJ8t6vfRVn-vg@giganews.com>...
>
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
>

>
> Thanks for the help,
>
> David

Aaron [SQL Server MVP]

2005-03-15, 5:45 pm

> network can hack it. I'm not promoting NAS over SAN, but the reason we
> went for NAS was simply that we have a small team here and really
> valued the ease of management.

I don't think you'll find many people recommending NAS over SAN for
real-time RDBMS. The selling point of NAS is cheap storage, definitely not
speed. We use our NAS device for archiving our log files and we barely
trust it for that. We certainly don't trust it for anything client-facing.

Brian Inglis

2005-03-15, 8:47 pm

On Tue, 15 Mar 2005 15:48:20 -0500 in comp.arch.storage, "Aaron [SQL
Server MVP]" <ten.xoc@dnartreb.noraa> wrote:

>I don't think you'll find many people recommending NAS over SAN for
>real-time RDBMS.
^^^^^^^^^ what are the timing guarantees offered?

Don't use the term *real-time* if you don't understand it or mean it!

--
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada

Brian.Inglis@CSi.com (Brian[dot]Inglis{at}SystematicSW[dot]ab[dot]ca)
fake address use address above to reply

Faeandar

2005-03-15, 8:47 pm

On Mon, 14 Mar 2005 20:57:15 -0800, "Larry David" <invalid@bogus.bum>
wrote:

>Faeandar,
>
> Thanks for the great response. I have a couple of follow-up questions:
>
>
> Perhaps I should consider increasing the number of drives allocated to
>the transaction logs? Someone in another thread suggested using 4 drives,
>arranged as RAID 1+0.

Probably. 4 would be better than 2 for sure but I've heard of people
needing as many as 14 drives just for the transaction log performance.
I would think that is pretty extreme though.

>
>
> Just to make sure that I understand your example, are you saying that
>the hypothetical SQL server machine would have 3 Fibre Channel host bus
>adapter (HBA) cards? Do these cards run on the PCI bus? Is there any chance
>that I'd saturate the PCI bus? I understand your assertion that it's
>probably a moot point since 14 drives can't push 2gbps anyway. I'm just
>asking for educational reasons.

Yes. Yes. Yes. A dual port card these days these is actually 2
channels and can gain the same performance as 2 single port cards.
More than 4 ports and you're likely not gaining anything.

>
>
> Thanks for the encouragement. I'm a software developer by training and I
>think people would prefer that I not ask questions and just leave the setup
>to the pros, but I really love learning and am very interested in
>understanding this stuff. I'd love to be involved in the installation
>process if at all possible.

Are you a software developer or a san administrator? In some shops
dba's are also admins (or the other way around if you want). If you
are not one of those then why are they asking you to research this?
If you are then you better be the one installing.

~F

Faeandar

2005-03-16, 2:45 am

On Tue, 15 Mar 2005 15:48:20 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

>
>I don't think you'll find many people recommending NAS over SAN for
>real-time RDBMS. The selling point of NAS is cheap storage, definitely not
>speed. We use our NAS device for archiving our log files and we barely
>trust it for that. We certainly don't trust it for anything client-facing.
>

That's funny, Oracle uses NAS (NetApp specifically) for several
mission critical applications. They even recommend NAS for Oracle
databases.

Cheap disk is hardly the benefit of NAS. Try ease of failover and
multiple instances with near zero overhead. It's fast too, not as
fast as 2gb FC for the most part but then again, not a lot of
databases need that anyway. It's about response time, not throughput.
And NAS servers, not all of course, can do that just fine.

For certain applications NAS will outperform SAN, it all depends on
the IO patterns.

Given your first post you almost sound like an EMC employee. Bad
form.

~F

Aaron [SQL Server MVP]

2005-03-16, 2:45 am

> Don't use the term *real-time* if you don't understand it or mean it!

Uh, thanks for the grammar lesson, I guess. I meant production vs. storing
backup files, archived tran logs, warm/cold standby, etc. I've never seen
live databases on a NAS and our organization simply wouldn't allow it
because that's not what a NAS is for.

Dennis Ingram

2005-03-16, 2:45 am

Someone better tell Oracle that, not only do they promote NAS heavily,
they run their entire company on it :-)

Seriously though, my impression of NAS is directly the opposite - it
is a serious option for both transactional and analytical RDBMS.
Performance-wise, they are suprisingly good, and the features for
management and backup are outstanding. They also seem very reliable.
We have had a production system (non-RDBMS) that does 3 million
transactions per week running on NetApp Filers for about 4 years now
without problems. I have no real worries about putting an RDMBS system
on them. I guess it depends on which NAS devices we are talking about
- the NetApp gear does not rate as "cheap" that's for sure.

Dennis

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:<ODFXcBaKFHA.3788@tk2msftngp13.phx.gbl>...
>
> I don't think you'll find many people recommending NAS over SAN for
> real-time RDBMS. The selling point of NAS is cheap storage, definitely not
> speed. We use our NAS device for archiving our log files and we barely
> trust it for that. We certainly don't trust it for anything client-facing.

Aaron [SQL Server MVP]

2005-03-16, 2:45 am

Well the devices we evaluated three years ago were all crap. Even the one
we bought was written off for log file storage about 30 minutes after it was
configured.

Perhaps they have come a long way since then; I'm happy to stand corrected.

But you have to agree that at a much cheaper $/gb, there must still be some
drawback to using NAS vs. SAN. Maybe that's not all performance anymore but
I'm sure that delta is still there, to some degree, in many scenarios.

Having said that, while it's true that you often get what you pay for,
that's not always the case. Take IBM servers or workstations, for example.
Or even Compaq servers, back in the day. All pretty expensive next to the
competition, and they certainly weren't worth the premium (again, in my
experience).

A

On 3/15/05 11:18 PM, in article
66edc890.0503152018.6f45e4f8@posting.google.com, "Dennis Ingram"
<dennis.ingram@gmail.com> wrote:

> Someone better tell Oracle that, not only do they promote NAS heavily,
> they run their entire company on it :-)
>
> Seriously though, my impression of NAS is directly the opposite - it
> is a serious option for both transactional and analytical RDBMS.
> Performance-wise, they are suprisingly good, and the features for
> management and backup are outstanding. They also seem very reliable.
> We have had a production system (non-RDBMS) that does 3 million
> transactions per week running on NetApp Filers for about 4 years now
> without problems. I have no real worries about putting an RDMBS system
> on them. I guess it depends on which NAS devices we are talking about
> - the NetApp gear does not rate as "cheap" that's for sure.

Faeandar

2005-03-16, 8:45 pm

On Tue, 15 Mar 2005 23:41:27 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

>Well the devices we evaluated three years ago were all crap. Even the one
>we bought was written off for log file storage about 30 minutes after it was
>configured.

I happen to know that NASA JPL was running Oracle on NetApp 760's
about 6 years ago. So my guess is whoever was doing your evaluation
was not very informed or skilled.

>
>Perhaps they have come a long way since then; I'm happy to stand corrected.

So have SAN array's. 3 years ago SAN architecture was difficult to
make work, whereas NAS just plugged in.

>
>But you have to agree that at a much cheaper $/gb, there must still be some
>drawback to using NAS vs. SAN. Maybe that's not all performance anymore but
>I'm sure that delta is still there, to some degree, in many scenarios.

The difference in price is generally perception. Since there are alot
of people like you out there that think SAN is the *only* way to go
for alot of applications they can price it higher due to perceived
value. However, in most cases that is simply not true so people pay
for something they don't need even if they don't know it.
As I said in a previous post, in some cases SAN is the way to go. But
it depends on IO patterns, and there are patterns where NAS beats SAN
not just parallels.
[vbcol=seagreen]
>
>On 3/15/05 11:18 PM, in article
>66edc890.0503152018.6f45e4f8@posting.google.com, "Dennis Ingram"
><dennis.ingram@gmail.com> wrote:
>

Not quite, but alot of internal applications deemed mission critical.
From what I understand their ERP system runs on NAS.
[vbcol=seagreen]

For serious transactional db's I'm thinking SAN would be better.
Actually, DAS would be best but that's about dead. NAS does have
manageability and features over SAN hands down.

~F

Yura Pismerov

2005-03-16, 8:45 pm

Yura Pismerov

2005-03-16, 8:45 pm

One thing you should keep in mind when compare NAS and DAS (direct attached storage)
is that NAS offloads I/O operations that you normally would have to process locally ("wasting" precious CPU cycles on your local
box). It also caches data remotely and it does it very efficiently so using direct I/O option (which bypasses local buffers and
cache) usually gives you better results.
NAS may be a better option in some circumstances, but, of course, it depends on many factors...

Dennis Ingram wrote:
[vbcol=seagreen]
> Just as a matter of interest, we are currently completely a project
> which migrated a large Oracle database from 7-year old AIX system to
> the new generation of machines. For storage we chose NAS, specifically
> NetApp Filers, the connection between the host and the NAS device
> being a 1Gbps ethernet.
>
> I really did not believe a skinny 1Gbps could provide the throughput
> we needed.
>
> To start with, we developed using 4 x 146GB internal SCSI disks, and
> the new system was a satisfying 9x faster than the old one (using a
> smaller copy of the database).
>
> So then we borrowed a Filer with 8 x 144GB drives from the local
> supplier. This was the bottom-of-the-range FAS250 model. The result
> was that it was 10x faster.
> I still do not completely understand why. One thing of course is that
> the I/O characteristics of an RDBMS is not like ftp'ing a file... and
> in case you are wondering, our application was keeping it very busy
> with some heavy-duty batch processing, so it was not like random light
> user transactions.
>
> Anyway, just thought you might like some reassurance that a 2Gbps SAN
> network can hack it. I'm not promoting NAS over SAN, but the reason we
> went for NAS was simply that we have a small team here and really
> valued the ease of management.
>
> Cheers
> Dennis
>
> "Larry David" <invalid@bogus.bum> wrote in message news:<v76dndQtatJ8t6vfRVn-vg@giganews.com>...
>
>
>

Dennis Ingram

2005-03-16, 8:45 pm

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:<BE5D2327.36AE%ten.xoc@dnartreb.noraa>...
> Well the devices we evaluated three years ago were all crap. Even the one
> we bought was written off for log file storage about 30 minutes after it was
> configured.

Interesting - what devices were these?

> Perhaps they have come a long way since then; I'm happy to stand corrected.
>
> But you have to agree that at a much cheaper $/gb, there must still be some
> drawback to using NAS vs. SAN. Maybe that's not all performance anymore but
> I'm sure that delta is still there, to some degree, in many scenarios.

Can't see any drawback to be honest. Even the cost when considering
the mythical total cost of ownership. Having said that, I've never
used FC, I've come from an IBM SSA background. Moving from SSA to
NetApp SAN is surreal, the NAS is *so* easy. I've spend enough hours
reconfiguring SSA loops to know a good thing when I see it, although I
needed to get past the initial disbelief with performance with an
RDBMS.

Anyhow, if you haven't looked at NAS recently, go have a browse on
NetApp's website, you might find it interesting to see how much they
have progressed.

Malcolm Weir

2005-03-17, 7:45 am

On Wed, 16 Mar 2005 16:54:50 GMT, Faeandar <mr_castalot@yahoo.com>
wrote:

>On Tue, 15 Mar 2005 23:41:27 -0500, "Aaron [SQL Server MVP]"
><ten.xoc@dnartreb.noraa> wrote:
>
>
>I happen to know that NASA JPL was running Oracle on NetApp 760's
>about 6 years ago. So my guess is whoever was doing your evaluation
>was not very informed or skilled.

Sadly, when it comes to systems, NASA = Cheap in all the wrong ways
(like having scientists build systems, since "it's cheaper" than
buying them from e.g. SGI or Sun). The cost of labor and support were
not considered.

So personally I'd consider that to be a negative recommendation.
Which is probably not what you meant.

Malc.

themeanies

2005-03-18, 2:45 am

Larry David wrote:
> Hi,
>
> I'm trying to create a home for a database. The database will ultimately
> reach approximately 200Gb in size. Usage patterns will consist of
> approximately 80% read, 20% write. The vast majority of the reads will
> require random access to small chunks of data (less than 64k).
>
> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
> Server will be a machine of lesser capabilities and will only come online if
> the primary data server fails. This concept, as it has been explained to me,
> is known as "failover."
>

This is called clustering. I didn't catch which OS you were
implementing. Different OS's handle it differently. Clustering is
definitely what you are after though. Some OS's have active-passive and
some have active-active clustering.

> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
>
> http://www-1.ibm.com/servers/storag...4100/index.html

Please also consider other vendors like EMC for mid tier storage.
Sounds to me like your scenario doesn't call for high end Toshiba or the
like storage systems.

Someone mentioned avoiding EMC, but the situation they described is late
nineties mentality. Most all vendors during the boom were after as much
as they could get all the time. Most had a chip on their shoulder too.
EMC's current CEO has changed much of that mentality. If for no other
reason play IBM vs. EMC at the negotiating table. You are after all
talking about 10's of thousands of dollars here. Ultimately the vendors
are fighting for footprint. Getting their foot in the door(especially
in a shop without any storage) will greatly increase their chances of
selling more storage to you in the future.

>
> This device holds 14 drives and transmits data via a mechanism called
> Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
> Here are my questions and concerns:
>
> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?
>
> 2) Assuming that I placed 14 drives in this storage device, how would I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?
>
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?

These numbers are way higher than what you will see. 'theoretical' <>
'actual' especially when reading sales literature. Your database IO
will ultimately determine this. You'll want to analyze latencies and
performance constantly after first installing a solution.

>
> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various storage
> solutions.
>

It's not necessary for you to do the installation, but do participate
heavily in the design. Also get a training course/certification of some
kind folded into your project cost.

> Thanks for the help,
>
> David
>
>

For reference, this is what I have:

EMC CX600 which has 2 separate storage processors 4GB cache per storage
processor.
6 DAE's 11.5 TB total.
2 16port Brocade 2Gb FC switches
5 Windows 2003 servers each with 2 2Gb Qlogic HBA's
Our setup is not clustered. We use cold data/log shipping to a hot
standby MSSQL server also SAN attached.
Our production MSSQL server hosts 35 databases. 1 database is 95GB and
is spread across 3 physical LUNs. Data is spread across a 6 disk
RAID1+0 and a 4 disk RAID1+0, Logs are on 6 disk RAID 1+0. tempdb is on
a 4 disk RAID5. The other db's are much smaller and their data is on
one 4 disk RAID1+0 and their logs are on another 4 disk RAID1+0. The
server is a Quad Xeon 3.0 with 32GB memory. We typically see 80-90
Mbsec sustained data transfers on these SAN disks. This solution was
designed and implemented in the summer of 2003.

I know nothing of NAS, but this SAN setup is completely redundant. Not
sure that you can say that about a NAS head. Bad memory a blown power
supply, etc could yank the disks out from under a SQL Server. As you
can imagine this is *very* bad. The CX 600 can trespass LUNS in the
event of a disruption at any connectivity level without affecting the
data. Slight slowdown yes, data loss no. The CX 600 also has a
non-disruptive software upgrade path. In nearly 2 years since
installation we have rebooted the SAN only once due to a datacenter
power re-config. We've upgraded the software 4 times.

We considered the equivalent IBM solution, even going so far as to spend
a day in their Dallas lab to bring our own data and applications to
test. But ultimately went with EMC.

HtH
-tM-

----== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==----
http://www.webservertalk.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Jackson Houndmugger

2005-03-18, 5:51 pm

Thanks for the excellent post. I appreciate the detailed info!! This helps
a lot!