SQL Server Performance Forum – Threads Archive
Active/Active sql cluster. Need help
Hello all,It’s my first time when I got such task. I need to configure active/active 2 node sql server cluster on win2003 server. I’ve read a lot of articles about the clustering, but only a little part of them directly describe the active/active type. So any help would be apprecate I have two servers and shared disk array, which I can divide into 4 disks(is it enough?)(for example I and H drives depends to db1 data and log files; K and L to db2)
So if I correctly understood, all goes in that way: I have to install one sql 2000 server named instance on one node(s1 with db1) and second named instance on the other node(s2 with db2). Then on the s1 I have to install virtual sql named instance vs1/first, where the s1 would be the primary and s2 the secondary, and on the s2 I have to install second virtual sql named instance vs2/second, where s2 would be the primary and s1 – secondary. Now if the server s1 failover to s2 what happens? s2 takes control of db1 data and log files, which resides on the drives I, H, and what about s1 serveragent, msdb and db1 jobs?? Or maybye my understanding about this is like in the stone-age And the second thing: There is the third server s3 which stands here for log shipping purpose. S1 and S2 make backups and s3 copies and restores them. Now when S1 failover to S2, what hapens with S1 TRN? I think that s3 then restores s2 TRN only. Am I right? Thanks in advance for any help
The term Active/Active mean that you have one or more instances running on both nodes in the cluster. You can have up to 16 instances in a SQL Server 2000 cluster that you can divide among your two (or more) nodes. If all 16 instances where running on one node you would have an Active/Passive cluster. In a cluster you can have either 16 named instances or 1 default instance and 15 named insances. Each instance owns it’s own disks, have it’s own copy of master, msdb, tempdb and have it’s own sql server and agent services etc. When a specific instance is running on one node the services are stopped on all other nodes. When you failover, the sql services on the current node are stopped and then started on the node you fail over too. If you ship transaction logs to a standby server you should copy them through the virtual SQL server name (or IP) for that instance since that name will move with the sql service and the disks when you failover.
Thank you Argyle for your circumstantial explanation. I think that this is the simplest and the most informative answer I’ve got back. You said: "Each instance owns it’s own disks, have it’s own copy of master, msdb, tempdb and have it’s own sql server and agent services etc. " My question is: Are you talking about named instances of sql server or virtual? Or both? And one more: In active/pasive configuaration it’s clearly said that there must be one virtual server. What about active/active? Always two or not?
Not sure what you mean by logical and physical disk, RAID maybe?. Each instance in the cluster need a minimum of one "logical" disk dedicated to it in the shaerd array. If this disk is in turn built as a RAID5 of say 4 physical disks or a RAID1 of 2 physical disks is up to you. Logically speaking from the operating system view each instance need a minimum of one disk to use. Normally you would give your instance at least 2 logical drives. One for Data and one for Log. If you have the money you might add a disk for tempDB and one for Backup. Example:
Disk K: SQL Data instance 1, RAID5
Disk L: SQL Log instance 1, RAID0+1
Disk M: SQL Backup instance 1, RAID5
Disk T: SQL Data instance 2, RAID5
Disk U: SQL Log instance 2, RAID0+1
Disk V: SQL Backup instance 2, RAID5 Note that this can not be partitions on the same raid array. You can not have a one RAID5 on the shared array and then create windows disk partitions from the OS and share these. This will not work during failover. Each logical disk must be its own RAID set.
Thank you again for your help
]]>