Wednesday, March 28, 2012
RAID and Cluster!
recommended RAID Solution for SQL Cluster on SAN, is it RAID 1+0 or RAID-5?
We just upgraded from RAID 1+0 to RAID 5 and since then we are getting
hiccups on the Cluster. If there is any connectivity failures on the Drives
(which are recovered after few mins) the Cluster goes haywire and becomes
unavailable (for e.g. it fails to read off the Quorum Log etc...)
Thanks in adv.
Env:
Win2000 Adv Server SP4
SQL2000 SP4
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23Lb1UxbIGHA.524@.TK2MSFTNGP09.phx.gbl...
> Hi All, Want to seek advice and experience from all of you. What is the
> recommended RAID Solution for SQL Cluster on SAN, is it RAID 1+0 or
RAID-5?
> We just upgraded from RAID 1+0 to RAID 5 and since then we are getting
> hiccups on the Cluster. If there is any connectivity failures on the
Drives
> (which are recovered after few mins) the Cluster goes haywire and becomes
> unavailable (for e.g. it fails to read off the Quorum Log etc...)
>
> Thanks in adv.
>
|||First, you need to fix you SAN issues, with or without clustering SQL really
likes to have its disks online at all times.
Have you separated your Databases and Transaction Log files? RAID 1 or 10
for the Logs. Raid 5 or 10 for the Databases. Quorum & MSDTC (if required)
on its own disk subsystems of 1 GB in size.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23Lb1UxbIGHA.524@.TK2MSFTNGP09.phx.gbl...
> Hi All, Want to seek advice and experience from all of you. What is the
> recommended RAID Solution for SQL Cluster on SAN, is it RAID 1+0 or
> RAID-5?
> We just upgraded from RAID 1+0 to RAID 5 and since then we are getting
> hiccups on the Cluster. If there is any connectivity failures on the
> Drives
> (which are recovered after few mins) the Cluster goes haywire and becomes
> unavailable (for e.g. it fails to read off the Quorum Log etc...)
>
> Thanks in adv.
>
|||That's not an upgrade, it is a reconfiguration. They do completely
different things. By RAID 1+0 do you mean a stripe of mirrors or a mirror
of stripes?
In either case, the answer is, I don't know. RAID5 gives particular levels
of redundancy while incurring a performance impact for certain types of I/O
patterns. A mirror of stripes gives equivalent redundancy to RAID5 without
having quite a much of a performance impact on certain types of I/O
patterns. A stripe of mirrors gives much greater redundancy than the other
two and generally better performance as well. But, if you are saturating
the I/O channel or stuffing all of your data on a tiny subset of the drives
in the SAN, it really isn't going to matter very much. The recommendations
also vary depending upon whether you have an access pattern that looks like
"tradional OLTP", "traditional DW", or a mix of the two.
Regardless of the performance characteristics, neither one is going to be
more stable than the other in your case. If the SAN drives disconnect, it
doesn't matter if it is a cluster or a standalone SQL Server, it isn't going
to like it. You have to fix the errors on your SAN that are causing the
disconnects first. If those aren't fixed, there isn't anything you are
going to be able to do to make the system stable.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23Lb1UxbIGHA.524@.TK2MSFTNGP09.phx.gbl...
> Hi All, Want to seek advice and experience from all of you. What is the
> recommended RAID Solution for SQL Cluster on SAN, is it RAID 1+0 or
> RAID-5?
> We just upgraded from RAID 1+0 to RAID 5 and since then we are getting
> hiccups on the Cluster. If there is any connectivity failures on the
> Drives
> (which are recovered after few mins) the Cluster goes haywire and becomes
> unavailable (for e.g. it fails to read off the Quorum Log etc...)
>
> Thanks in adv.
>
|||Not to mention that unless you have proper host isolation in the SAN, you
are going to run into severe spindle contention, even if this particular
host's channels are not saturated.
Anthony Thomas
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%234b0uwdIGHA.676@.TK2MSFTNGP10.phx.gbl...
> That's not an upgrade, it is a reconfiguration. They do completely
> different things. By RAID 1+0 do you mean a stripe of mirrors or a mirror
> of stripes?
> In either case, the answer is, I don't know. RAID5 gives particular
levels
> of redundancy while incurring a performance impact for certain types of
I/O
> patterns. A mirror of stripes gives equivalent redundancy to RAID5
without
> having quite a much of a performance impact on certain types of I/O
> patterns. A stripe of mirrors gives much greater redundancy than the
other
> two and generally better performance as well. But, if you are saturating
> the I/O channel or stuffing all of your data on a tiny subset of the
drives
> in the SAN, it really isn't going to matter very much. The
recommendations
> also vary depending upon whether you have an access pattern that looks
like
> "tradional OLTP", "traditional DW", or a mix of the two.
> Regardless of the performance characteristics, neither one is going to be
> more stable than the other in your case. If the SAN drives disconnect, it
> doesn't matter if it is a cluster or a standalone SQL Server, it isn't
going[vbcol=seagreen]
> to like it. You have to fix the errors on your SAN that are causing the
> disconnects first. If those aren't fixed, there isn't anything you are
> going to be able to do to make the system stable.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:%23Lb1UxbIGHA.524@.TK2MSFTNGP09.phx.gbl...
becomes
>
sql
Tuesday, March 20, 2012
Quorum on NAS
the cluster storing the database on its local hard drive,
is it OK to have the quorum drive on a NAS/network server.
So the database will be direct attached on one server. The
database will be replicated to another computer with a
direct attached disk. But the Quorum drive that they share
will be a drive on the network.
Is that good practice?
Thanks,
Todd
NAS is not supported in a cluster.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2050501c4591d$0205ba70$a501280a@.phx.gbl...
> In a two node SQL server cluster, with each computer in
> the cluster storing the database on its local hard drive,
> is it OK to have the quorum drive on a NAS/network server.
> So the database will be direct attached on one server. The
> database will be replicated to another computer with a
> direct attached disk. But the Quorum drive that they share
> will be a drive on the network.
> Is that good practice?
> Thanks,
> Todd
Quorum & MSDTC Cluster Questions
We are trying to setup a Windows Server 2003 Cluster with 2 systems and a DAV. We intend to install SQL 2005 on this Cluster. We purchased a DAV with 3 physical disk arrays as follows.
73GB RAID 1 (our plan is to use this to store sql transaction logs)
146GB RAID 1 (sql backups, temp database & other temp files)
420GB RAID 10 (sql databases)
Now as we are setting all this up we find out we need a shared physical drive on the DAV to store the Quorom. It is my understanding we cannot partition the physical drives and use one of the partitions to store the Quorum because when you create the resource for the Quorum the resource is the phsyical disk not the partition.
So my question is, is it in our best interest to buy a seperate physical disk for the Quorom?
My next question is, with regards to the MSDTC, is it in our best interest to buy a seperate physical disk for the MSDTC or can we store it on the 146GB RAID 1 and still use the drive for its original purpose?
Any guidance is much appreciated.
- Nick
It's best to have each device on its own physical device, but this isn't required. Each does have to be its own partition (drive letter), though. Here's a white paper that goes into a lot more depth:
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
Buck Woody
|||Actually, the quorum does need to be in a different device than the database files, since it is part of a different cluster Resource Group. It needs to be able to fail over between the nodes independantly of the SQL service. Since the underlying SCSI protocols will attach an entire drive to one system or the other, you can't split partitions.
If your definition of DAV is the one I'm familiar with, you should verify that this storage can be used in a Windows cluster. My understanding is that clusters will only use SCSI, iSCSI, or SAN storage.
|||That's a good point - when I said that it doesn't need to be on a separate device I should have been more clear. Many times clusters are set up on a SAN configuration. In that case you do want to present a separate LUN for the quorum drive, but you can use the same SAN/HBA. The whitepaper I have referenced there should help with the layout - if not, just re-post and we'll help out where we can.
Buck
|||Thanks for your responses.
My apologies, I meant to say DAS not DAV. Sorry for the confusion.
It is a clusterable storage device that acts similarily to a SAN. We purchased it from DELL and they assured us it was certified to work in a Windows Cluster environment. Unfortunately for us we cannot split the drive arrays into multiple virtual drives. So we're stuck with 3 drive arrays and we cannot take one drive array and split it into 3 so the one drive array is presented to Windows as 3 seperate drives.
It does seem like a waste to have a seperate drive dedicated to the Quorum, especially since it only uses 4MB and the smallest HD you can find is 36GB.
What are your thoughts on Volume Mount Points? Does that work for the Quorum (or MS DTC for that matter)?
What about the MS DTC ? Does it need a seperate physical disk like the Quorum or can it be placed on drive with other minor usage?
Thanks again.
- Nick
|||When I set up clustering we had this kind of layout:
C Drive: Physical, Local RAID 1, OS and Binaries
Q Drive: Physical, Local RAID 1, Quorum (yes, we cheated and put a few other things on here. But not much, and nothing that we let users get to)
S Drive: LUN, SAN, Shared DB Databases
L Drive: LUN, SAN, Shared DB Logs
I view this as the mininum, with other sets defined like S for high read/write activities and filegroups
Buck
|||
Thanks for your responses again.
Where did you place the MS DTC physical disk required by the SQL Cluster?
If you have a Cluster of 2 or more systems doesn't the Quorum drive have to be on a shared drive not local?
Thanks!
- Nick
|||The issue isn't usage or volume of data. The issue is that the storage must be able to move from node to node independantly of any other resource group.
So, if you put MSDTC and the quorum on the same physical drive (even in separate partitions), then if you moved the cluster service from node A to node B, MSDTC would die because its disk would disappear out from under it. Worse, if MSDTC failed and took the disk offline, the cluster service would die.
For those reasons, MSCS won't let you configure two resource groups that depend on the same physical drive.
Can you give the model of the Dell array? That sounds interesting, and I'd like to read up on it. From your description, each array forms one virtual drive, but can't be divided into LUNs? That seems strange. With a device where you can form virtual disks (LUNs), you can set up a very small LUN for Quorum.
|||Kevin
The DAS is a Dell PowerVault 220S DAS. Click here for more info.
The 2 servers we're setting this up on are "Dell 2950's running Server 2003 and SQL 2005".
We were going to arrange the drives like this:
On board *each* Dell PE2950:
(2) 73GB drives mirrored w/RAID 1 holding OS, Apps (C:) and Win Swap files (G:)
On DAS (220S):
Virtual Disk 0 - (2) 73GB drives mirrored w/RAID 1 for SQL Transaction Log files (D:)
Virtual Disk 1 - (2) 146 GB drives mirrored w/RAID 1 for a variety of temp jobs, SQL temp files, etc.(E:)
Virtual Disk 2 - (6) 146 GB drives mirrored and striped w/RAID 10 for the SQL database (F:)
We can't slice the paired drive sets using the SCSI controller as DELL disables 'slicing' virtual drives (sets) when you are in cluster config.
Thanks for your curiousity Kevin!
- Nick
|||We're purchasing a 4th drive array for the DAS for the Quorum to reside. That said would it be wise to setup the clustering like this:
[Cluster Group]
Cluster IP Address
Cluster Name
SQL Network Name
SQL IP Address
SQL Server (With dependencies of Disk D:, E: & F:)
DTC Network Name
DTC IP Address
DTC Service (With dependencies of Disk E:)
Disk D: (Shared Drive, SQL Transaction Logs)
Disk E: (Shared Drive, SQL Temp Files, MS DTC)
Disk F: (Shared Drive, SQL Databases)
Disk Q: (Quorum)
I think this looks good to me - you've got a C drive for local, obviously, so I'm assuming that's local to the system and a RAID 1 or similar.
Buck
|||Almost.
Each Resource Group must be independant and not depend on any resource in any other group.
I'd lay it out as:
[Cluster Group]
Cluster IP Address
Cluster Name
Disk Q: (Quorum)
[SQL Group]
SQL Network Name
SQL IP Address
SQL Server (With dependencies of Disk D:, E: & F:)
Disk D: (Shared Drive, SQL Transaction Logs)
Disk E: (Shared Drive, SQL Temp Files)
Disk F: (Shared Drive, SQL Databases)
[DTC Group]
DTC Network Name
DTC IP Address
DTC Service (With dependencies of Disk G:)
Disk G:(DTC)
You could possibly combine SQL and DTC, but ideally they should be separate.
Wednesday, March 7, 2012
Quick Fact Check on Clustering SQL Server
active\active cluster configuration, (cluster will be built on Windows 2000)
I can have a maximum of one virtual server name per node and up to 16 named
instances per node if I do not use the Default Instance.
Thanks
Steven S. Warren [MVP Windows]
MCDBA, MCSE, MCSA, CCA
Close. You get up to 16 instances per cluster. You can have 1 default and
15 named or just 1 named instances. You can arrange them on the host nodes
however you see fit.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steven S. Warren" <swtechworksnomorespam@.tampabay.rr.com> wrote in message
news:%23209KlbvEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Am I correct in my assumption? If I am creating a two-node SQL Server 2000
> active\active cluster configuration, (cluster will be built on Windows
2000)
> I can have a maximum of one virtual server name per node and up to 16
named
> instances per node if I do not use the Default Instance.
> Thanks
> --
> Steven S. Warren [MVP Windows]
> MCDBA, MCSE, MCSA, CCA
>
|||Thanks...Back to designing my cluster...I am enjoying this.
Thanks
Steven S. Warren [MVP Windows]
MCDBA, MCSE, MCSA, CCA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OI$YfUcvEHA.612@.TK2MSFTNGP15.phx.gbl...
> Close. You get up to 16 instances per cluster. You can have 1 default
> and
> 15 named or just 1 named instances. You can arrange them on the host
> nodes
> however you see fit.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Steven S. Warren" <swtechworksnomorespam@.tampabay.rr.com> wrote in
> message
> news:%23209KlbvEHA.2536@.TK2MSFTNGP11.phx.gbl...
> 2000)
> named
>
Saturday, February 25, 2012
Queue Reader fails
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"