| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2003-04-25 : 03:00:39
|
| I didn't want to hijack the other guy's thread, but Chad made a comment in [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25508[/url] that "the active node has to have control over the shared disk space".First my disclaimer: I've never done clustering of SQL Servers. I'm not currently trying to do clustering. I promise if I ever do want to cluster SQL Servers I will do real research, reading BOL, articles, etc., etc. I only have a surface understanding of the topic, but would like to have that little bit of knowledge at least be accurate...So, here's my question: I've heard comments like this before, and so now my assumption is that when you cluster SQL Servers, they are sharing the same physical data disks (SAN? NAS? something else?) rather than separate drives that need to be kept synchronized, like we did with an Active/Passive IIS fail-over cluster. Is that necessarily true (that they share the disks) or is that just one configuration option?------------------------------------------------------The more you know, the more you know you don't know. |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2003-04-25 : 04:35:31
|
| Ajarn,Some good info here:[url]http://www.sql-server-performance.com/q&a_clustering.asp[/url]macka.--There are only 10 types of people in the world - Those who understand binary, and those who don't. |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-04-25 : 04:44:46
|
| Hi ,It is an configuration option which you might need to adopt depending on the budget and hardware contraints . But it is recommended and would be necessary to have Fibre channel Shared disk array with RAID configuration . The RAID configuration again will depend on your requirement . But remember , that even shared disk will provide a single point of failure and you need a spare hard disks in the event of failure .So every solution will have its own pros and cons and it all dpends on the budget and availability .Use of SANs is supported for use in conjunction with failover clustering as a Cluster/Multiple-Cluster Device if purchased as part of a complete clustering solution. admin001 . |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-04-25 : 08:09:34
|
quote:
But it is recommended and would be necessary
What do you mean "would be necessary"?Sam - There is a hyphen in anal-retentive. |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-04-25 : 08:25:32
|
| It would be necessary because FibreChannel is designed specifically for high bandwidth and high capacity . If it is SAN , then disk arrays would use networking protocols over FibreChannel to do all the I/O's . admin001 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-04-25 : 09:02:28
|
| Mark, here is what Chad is saying....Let's say you have a 2-node active/active SQL Server cluster. Two servers(server1 and server2) and two nodes(node1 and node2) and two instances (default and \instance1). The clustered drives are E: and G: and the datafiles for the default instance are on E: and the \instance1 is on G:. In the normal operation mode, you've got node1 running the default instance on server1 and node2 running the \instance1 on server2. The E drive is a dependency of node1 and the G drive is a dependency of node2, because the shared drives have to be attached to whichever group (node) SQL is currently running on.Now lets say you fail the cluster over. Node1, the default instance and the E drive move from server1 to server2. So now, server2 is running both sql instances and has control of the E drive and G drive. Now lets say you put a (bigger,badder)F drive on server1 and add that drive to the cluster (note: I'm not 100% sure you can add a new resource (F:) to the cluster without downtime, but maybe). And then you fail node1, the default instance and the E drive back to server1.At this point your default instance is still running on the E drive on node1. The only way to get those file onto F is to sp_detach_db, copy and sp_attach_db ... the downtime of that action cannot be avoided.Jay White{0} |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-25 : 12:55:57
|
If you want to play with clustering and have VMWare workstation you can set yourself up a cluster on your desktop (I have a 2 node SQL2000 cluster on 2 Windows 2000 AS DC's on my laptop  ). You still need a shared disk but it's a VMWare disk (i.e. just a file) so it makes it so much easier and cheaper to play with than getting hold of a spare 2 servers and some shared disk. For details on setup see [url]http://www.robbastiaansen.nl/vmware/vmw2kcluster.html[/url]Here's a quote from the Win2000 guide to installing clustering re shared disk, if you want more than one instance then each needs it's own physical disk resource because only one node can own a resource at a time - it's a shared nothing solutionquote:
Shared Disk Requirements:- All shared disks, including the quorum disk, must be physically attached to a shared bus.
- Verify that disks attached to the shared bus can be seen from all nodes. This can be checked at the host adapter setup level. Please refer to the manufacturer’s documentation for adapter-specific instructions.
- SCSI devices must be assigned unique SCSI identification numbers and properly terminated, as per manufacturer’s instructions.
- All shared disks must be configured as basic (not dynamic).
- All partitions on the disks must be formatted as NTFS.
While not required, the use of fault-tolerant RAID configurations is strongly recommended for all disks. The key concept here is fault-tolerant raid configurations—not stripe sets without parity.
HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-25 : 13:41:08
|
| Thanks guys! I knew you'd come through!Jay, thanks for the extra clarification on that specific scenario. I swear you make me aware of how much my signature post applies to me. When I start thinking I could call myself a SQL expert, all I have to do is come to SQLTeam and I can find some topic that I realize I really don't know all that much about (like clustering). But then, I "grew up" on the developer side of the house and have picked up Admin stuff along the way. There's nothing like a good team to help keep a guy humble.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-25 : 18:57:59
|
| Page47, I made a suggestion in that other thread and people were apparently polite enough to just ignore it rather than pointing and lauging.Still, I'm curious: why not just add another file to the filegroup? In your scenario, add a file on F, and then remove the file from E? In non-clustered SQL server scenarios, that should certainly work fine. Why not in a clustered scenario?Cheers-b |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-25 : 19:04:48
|
| Aiken,Yes you can add files, but then you have to move the tables to those files which involves downtime.Also, how do you remove the primary file? I don't believe that you can without downtime.Tara |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-26 : 05:23:42
|
| Hmmf. Maybe I'm just confused, but I was under the impression that you could add a second file to the primary filegroup, which all tables and indexes default to. Then, by removing the original file from that filegroup, SQL server would be forced to move all data to that new file. Everything stays in the same filegroup, but the file moves.Am I smoking too much crack or something? Either I've got a huge misunderstanding of files/filegroups, or everyone else is missing the obvious. My money's on the former, but I need someome to tell me *why* I'm wrong before I accept it Cheers-b |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-26 : 11:52:27
|
| You can't remove the first file from a database. Whilst with other files you can use DBCC SHRINKFILE and EMPTYFILE to move data to other files in the same filegroup and then remove the file, you can't do this with the first file because it contains database information and system tables and are always placed on the first file in the primary filegroup and cannot be moved. Thus if your primary filegroup has multiple files you can remove any file except the first one (fileid = 1) as that cannot be emptied (there will always be around 12 extents allocated to the system tables etc)HTHJasper SmithEdited by - jasper_smith on 04/26/2003 14:11:40 |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-29 : 01:01:22
|
| Thank you -- that does explain it.Cheers-b |
|
|
|
|
Spin-off Question on Clustering