We are looking into implementing an Active:Passive SQL 2005 Cluster for a
reasonably large and important system (< 1Tb)
I'm not looking for advice re: particular brand etc., but on advice on type
/ type of connection to SAN that may be appropriate. The SQL 2005 instance(s)
will be x64.
Please, any wannabe MVPs do not provide sarcastic/useless answers just to up
your profile. To pre-empt some of these:-
Yes I have indexes on current database
Yes the queries have been tuned
Yes, I have detailed stats on usage, performance, but you do not need
these to answer the general thrust of the question
etc.
Now, I went through the Dell SQL configurator to see what they came up with
.... they suggested ￡50k would be a reasonable price ... maybe it is, maybe
it isn't. This had all manner fancy fibre connection things, disks at ￡1000
each, other buzzers and bells. I wonder if I really need all this?
Basic setup is 2 * servers (Windows 2003 R2, standard, x64), spec'd (each):-
1 * Quad-core CPU
2 * 15,000rpm disks (RAID 1) for O/S and SQL program files etc. (and
swap files)
8Gb RAM
Network cards, etc.
I would like, an external disk array to hold database drives (pretty
essential really for a cluster!), of roughly the following format (each
physical disk probably 146Gb capacity):-
2 * 15,000 rpm RAID 1 (tempdb)
2 * 15,000 rpm RAID 1 (transaction logs)
At least 6 (probably more) * 15,000 rpm Stripped and Mirrored for main
database files
What sort of storage kit/spec. (fibre? not fibre? etc. ?) should I be
looking for to support the cluster? ie. what will do, not what do HP/Dell
etc. wish to sell me.
Yes the system wil grow over time.
One last thing ...
There may be alternative solutions to an Active:Passive cluster, but some
big name players (financial/multinational etc.) are using the system, and we
need to do some serious bomb-proofing.
I would prefer to have a single high-performance cluster, to reduce number
of requried SQL licences (sorry Bill!), and also avoid having to maintain too
many live copies of the database (yes Mirroring is a cool technology, but
maybe not for me at this moment in time?)
Surely an Active:Passive Cluster (even if x64) with external array is such a
basic setup these days, that kit for this is fairly standard?
Thank you for reading this, and thanks in advance for any constructive
anwers/pointers.
Craig
Lets see.
x64 is definitely the best bang for the buck in the range you are looking
at. More memory will help. Put in as much as the box can hold.
Obligatory naming rant: Active/Passive really doesn't describe how things
work. You are building a two-node, single-instance cluster. That
nomenclature is much more accurate and it scales. (Try describing a cluster
with 6 nodes and 12 instances using the words "active" and "passive".)
One very important consideration when building a cluster is to use some type
of smart array or a true SAN. The key is that the disk cache must reside in
the array, not on an onboard controller. Controller write cache is always
disabled in a clustered setting to maintain data consistency.
If performance is key, then RAID 1+0 is the configuration you want. Don't
worry about swap files, a properly configured dedicated SQL server should
not have any significnt paging activity. Do split tempdb onto its own fast
disk set.
As for connections, I strongly prefer fibre channel where possible. First,
it is a full-duplex data path so you can read and write at the same time.
SCSI is a bus so a large checkpoint can hammer read performance. Second,
you can only have two nodes in the cluster with SCSI. I am also very
reluctant to unplug a SCSI cable on a downed node should I need to repair
something. Not so much with a fibre channel connector.
You can always bolt mirroring on top of clustering for some geographic
separation. Choosing one does not necessarily eliminate the other.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"craig_amtdatatechnologies@.discussions.mi"
<craigamtdatatechnologiesdiscussionsmi@.discussions .microsoft.com> wrote in
message news:130DBB73-7072-4E58-A36D-DFCD35CC6057@.microsoft.com...
> Hi,
> We are looking into implementing an Active:Passive SQL 2005 Cluster for a
> reasonably large and important system (< 1Tb)
> I'm not looking for advice re: particular brand etc., but on advice on
> type
> / type of connection to SAN that may be appropriate. The SQL 2005
> instance(s)
> will be x64.
> Please, any wannabe MVPs do not provide sarcastic/useless answers just to
> up
> your profile. To pre-empt some of these:-
> Yes I have indexes on current database
> Yes the queries have been tuned
> Yes, I have detailed stats on usage, performance, but you do not need
> these to answer the general thrust of the question
> etc.
> Now, I went through the Dell SQL configurator to see what they came up
> with
> ... they suggested ￡50k would be a reasonable price ... maybe it is,
> maybe
> it isn't. This had all manner fancy fibre connection things, disks at
> ￡1000
> each, other buzzers and bells. I wonder if I really need all this?
> Basic setup is 2 * servers (Windows 2003 R2, standard, x64), spec'd
> (each):-
> 1 * Quad-core CPU
> 2 * 15,000rpm disks (RAID 1) for O/S and SQL program files etc. (and
> swap files)
> 8Gb RAM
> Network cards, etc.
> I would like, an external disk array to hold database drives (pretty
> essential really for a cluster!), of roughly the following format (each
> physical disk probably 146Gb capacity):-
> 2 * 15,000 rpm RAID 1 (tempdb)
> 2 * 15,000 rpm RAID 1 (transaction logs)
> At least 6 (probably more) * 15,000 rpm Stripped and Mirrored for main
> database files
> What sort of storage kit/spec. (fibre? not fibre? etc. ?) should I be
> looking for to support the cluster? ie. what will do, not what do HP/Dell
> etc. wish to sell me.
> Yes the system wil grow over time.
> One last thing ...
> There may be alternative solutions to an Active:Passive cluster, but some
> big name players (financial/multinational etc.) are using the system, and
> we
> need to do some serious bomb-proofing.
> I would prefer to have a single high-performance cluster, to reduce number
> of requried SQL licences (sorry Bill!), and also avoid having to maintain
> too
> many live copies of the database (yes Mirroring is a cool technology, but
> maybe not for me at this moment in time?)
> Surely an Active:Passive Cluster (even if x64) with external array is such
> a
> basic setup these days, that kit for this is fairly standard?
> Thank you for reading this, and thanks in advance for any constructive
> anwers/pointers.
>
> Craig
>
>
>
>
|||Hi,
Thanks for reply, certainly helpful
I note the need for clarity in nomenclature, correction welcome.
If your (or anyone else), were to put names (not brands) to the bits, what
nomenclature should I be using to described the various components and bits
connecting them together?
Basically, my knowledge of storage systems is pretty weak (as you wil lhave
guessed) ... so I need a connector out the back of my sql boxes (a bit of
fibre ... 1 or 2 bits each?), something to plug the fibre cables into (an
array), some disks to stick in the array. If fibre connector is used, do
these disks need to be anything special?
Sorry for noddy style questions ... !!
Thanks again
"Geoff N. Hiten" wrote:
> Lets see.
> x64 is definitely the best bang for the buck in the range you are looking
> at. More memory will help. Put in as much as the box can hold.
> Obligatory naming rant: Active/Passive really doesn't describe how things
> work. You are building a two-node, single-instance cluster. That
> nomenclature is much more accurate and it scales. (Try describing a cluster
> with 6 nodes and 12 instances using the words "active" and "passive".)
> One very important consideration when building a cluster is to use some type
> of smart array or a true SAN. The key is that the disk cache must reside in
> the array, not on an onboard controller. Controller write cache is always
> disabled in a clustered setting to maintain data consistency.
> If performance is key, then RAID 1+0 is the configuration you want. Don't
> worry about swap files, a properly configured dedicated SQL server should
> not have any significnt paging activity. Do split tempdb onto its own fast
> disk set.
> As for connections, I strongly prefer fibre channel where possible. First,
> it is a full-duplex data path so you can read and write at the same time.
> SCSI is a bus so a large checkpoint can hammer read performance. Second,
> you can only have two nodes in the cluster with SCSI. I am also very
> reluctant to unplug a SCSI cable on a downed node should I need to repair
> something. Not so much with a fibre channel connector.
> You can always bolt mirroring on top of clustering for some geographic
> separation. Choosing one does not necessarily eliminate the other.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "craig_amtdatatechnologies@.discussions.mi"
> <craigamtdatatechnologiesdiscussionsmi@.discussions .microsoft.com> wrote in
> message news:130DBB73-7072-4E58-A36D-DFCD35CC6057@.microsoft.com...
>
|||What you will need is one or more fibre channel adapters (called Host Bus
Adapters or HBAs) in each host computer. These can connect directly to the
SAN/Smart array or through a fibre channel switch. The configuration is
different for each. Fibre channel is really just a wrapper protocol around
the standard SCSI block access protocol, along with a fiber and an
electrical (copper) spec.
A SAN generally has gigabtes of cache and uses large internal block sizes
and aggressive caching techniques to optimize IO. A SAN can also support
many host computers where a smart array is intended to support only a few.
SANs are much more scalable. A Smart array does some abstraction of the
internal disks, but not nearly to the degree a SAN will.
The SAN or Smart array will dictate what internal connection the disks have.
Some have fibre channel (sometimes over copper wire) all the way to the
disks. Some have SCSI or SAS attachments to the actual disks.
When dealing with a SAN, the topic of LUN alignment is critical. This is
where the OS allocation blocks line up with the SAN internal stripe
boundaries. If they are not lined up, the SAN must execute excessive
internal IO operations for each physical disk request. This can lose you up
to 40% of the IO capacity of the unit.
Whatever you buy, run some IO tests so you will have a realistic expectation
and a baseline comparison when the unit is in production.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"craig_amtdatatechnologies@.discussions.mi"
<craigamtdatatechnologiesdiscussionsmi@.discussions .microsoft.com> wrote in
message news:C485D29A-13F2-4F88-BF41-25C3F4B4B8AD@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for reply, certainly helpful
> I note the need for clarity in nomenclature, correction welcome.
> If your (or anyone else), were to put names (not brands) to the bits, what
> nomenclature should I be using to described the various components and
> bits
> connecting them together?
> Basically, my knowledge of storage systems is pretty weak (as you wil
> lhave
> guessed) ... so I need a connector out the back of my sql boxes (a bit of
> fibre ... 1 or 2 bits each?), something to plug the fibre cables into (an
> array), some disks to stick in the array. If fibre connector is used, do
> these disks need to be anything special?
> Sorry for noddy style questions ... !!
>
> Thanks again
>
> "Geoff N. Hiten" wrote:
|||Talking about nomenclature, unless your I/O traffic goes through some kind of
FC switch(es) or at least a hub, I wouldn't consider the storage setup as a
SAN. It's more like direct-attached.
From what you just described, I'm not sure you want the cost of setting up a
full-fledged SAN, which can be justified only if you want to share the
storage among many hosts and have significant growth expectation in that
sharing.
The thing that you would plug fibre cable in on the host is a host bus
adapter (HBA). The most commonly used HBAs are from QLogic and Emulex. Their
websites have a lot of info on HBAs that are worth reading.
The disks in the array doesn't need anything special. But the array
manufacturer typically has specs to restrict what you can plug in there if
you want their support.
Linchi
"craig_amtdatatechnologies@.discussions.mi" wrote:
[vbcol=seagreen]
> Hi,
> Thanks for reply, certainly helpful
> I note the need for clarity in nomenclature, correction welcome.
> If your (or anyone else), were to put names (not brands) to the bits, what
> nomenclature should I be using to described the various components and bits
> connecting them together?
> Basically, my knowledge of storage systems is pretty weak (as you wil lhave
> guessed) ... so I need a connector out the back of my sql boxes (a bit of
> fibre ... 1 or 2 bits each?), something to plug the fibre cables into (an
> array), some disks to stick in the array. If fibre connector is used, do
> these disks need to be anything special?
> Sorry for noddy style questions ... !!
>
> Thanks again
>
> "Geoff N. Hiten" wrote:
|||Thank you for reply.
You are right to observe that we probably don't want cost of full-blown SAN
at the moment. In future, as we do more data-warehousing for live systems, I
can see a theoretical need for shared a disk system, and we also have other
database servers related to this system where there are cost arguments for
piggy backing them on some sort of shared disk array. However, I don't think
I can justify up-front costs for this in the short and medium term.
Thanks for help with this q.
Craig
"Linchi Shea" wrote:
[vbcol=seagreen]
> Talking about nomenclature, unless your I/O traffic goes through some kind of
> FC switch(es) or at least a hub, I wouldn't consider the storage setup as a
> SAN. It's more like direct-attached.
> From what you just described, I'm not sure you want the cost of setting up a
> full-fledged SAN, which can be justified only if you want to share the
> storage among many hosts and have significant growth expectation in that
> sharing.
> The thing that you would plug fibre cable in on the host is a host bus
> adapter (HBA). The most commonly used HBAs are from QLogic and Emulex. Their
> websites have a lot of info on HBAs that are worth reading.
> The disks in the array doesn't need anything special. But the array
> manufacturer typically has specs to restrict what you can plug in there if
> you want their support.
> Linchi
> "craig_amtdatatechnologies@.discussions.mi" wrote:
|||I have a similar situation to Craig. I was looking at using the Dell MD 3000
for storage. I know that it is SCSI, but cost is an issue at this point. Any
thoughts on that box. Additionally, (this may be a stupid question so forgive
me), but can we have one server with an active intance of one database and a
passive instance of another. This way my two node cluster would be using both
severs actively and spread the load. Does this make sense.
Aaron
"Geoff N. Hiten" wrote:
> What you will need is one or more fibre channel adapters (called Host Bus
> Adapters or HBAs) in each host computer. These can connect directly to the
> SAN/Smart array or through a fibre channel switch. The configuration is
> different for each. Fibre channel is really just a wrapper protocol around
> the standard SCSI block access protocol, along with a fiber and an
> electrical (copper) spec.
> A SAN generally has gigabtes of cache and uses large internal block sizes
> and aggressive caching techniques to optimize IO. A SAN can also support
> many host computers where a smart array is intended to support only a few.
> SANs are much more scalable. A Smart array does some abstraction of the
> internal disks, but not nearly to the degree a SAN will.
> The SAN or Smart array will dictate what internal connection the disks have.
> Some have fibre channel (sometimes over copper wire) all the way to the
> disks. Some have SCSI or SAS attachments to the actual disks.
> When dealing with a SAN, the topic of LUN alignment is critical. This is
> where the OS allocation blocks line up with the SAN internal stripe
> boundaries. If they are not lined up, the SAN must execute excessive
> internal IO operations for each physical disk request. This can lose you up
> to 40% of the IO capacity of the unit.
> Whatever you buy, run some IO tests so you will have a realistic expectation
> and a baseline comparison when the unit is in production.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "craig_amtdatatechnologies@.discussions.mi"
> <craigamtdatatechnologiesdiscussionsmi@.discussions .microsoft.com> wrote in
> message news:C485D29A-13F2-4F88-BF41-25C3F4B4B8AD@.microsoft.com...
>
|||I have not used that particular unit, bit it does meet my basic criteria of
having the controllers and cache on-board the array. I would see if Dell
can give you access to one to do some I/O testing.
You have hit upon why I am so frustrated with the
"Active/Active/Passive/whatever" nomenclature. Simply put, you cannot have
a single database file accessed and managed by two host computers. You can
have two different databases on different disk sets that normally live on
different nodes, but can failover to a single node if necessary.
Of course, any single node must be able to handle the entire load under the
worst conditions. Because that is when the system will fail.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:5AB08E84-4804-4308-8EFC-959811EDC752@.microsoft.com...[vbcol=seagreen]
>I have a similar situation to Craig. I was looking at using the Dell MD
>3000
> for storage. I know that it is SCSI, but cost is an issue at this point.
> Any
> thoughts on that box. Additionally, (this may be a stupid question so
> forgive
> me), but can we have one server with an active intance of one database and
> a
> passive instance of another. This way my two node cluster would be using
> both
> severs actively and spread the load. Does this make sense.
> Aaron
> "Geoff N. Hiten" wrote:
|||Geoff,
I understand your frustration. I am still trying to learn and understand the
new lingo. What is the basic lowdown on nodes vs. instances.
THanks,
Aaron
"Geoff N. Hiten" wrote:
> I have not used that particular unit, bit it does meet my basic criteria of
> having the controllers and cache on-board the array. I would see if Dell
> can give you access to one to do some I/O testing.
> You have hit upon why I am so frustrated with the
> "Active/Active/Passive/whatever" nomenclature. Simply put, you cannot have
> a single database file accessed and managed by two host computers. You can
> have two different databases on different disk sets that normally live on
> different nodes, but can failover to a single node if necessary.
> Of course, any single node must be able to handle the entire load under the
> worst conditions. Because that is when the system will fail.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:5AB08E84-4804-4308-8EFC-959811EDC752@.microsoft.com...
>
No comments:
Post a Comment