just want to straighten my concept properly - when we are talking about SQL multi instance failover cluster, e.g. on a 2 node cluster, it means that each node can run its own sql instance, but each instances are running its own database right ?

e.g. 2 nodes (node1, node2)
sql instance A on node1
sql instance B on node2

sql instance A and B are individual instances and are not sharing the same database right ?

when node1 is down, SQL instance A will move to node2 but both sql instance A and B are still separate databases - right ?


That's correct. A traditional failover cluster may contain multiple instances, and each instance may contain multiple databases. A database is part of only one instance, and an instance only runs on one node at a time.

  • so it isn't like oracle rac whereby 2 instance share the same database yea ? in ms sql, the database is only use by 1 instance. so the instances on each node are running their own separate databases yea ? – tiongmaru Feb 5 '16 at 16:51
  • Right. The cluster fails over the whole instance and all the associated databases. To get something closer to RAC, you'd use an AlwaysOn availability group. – techieb0y Feb 6 '16 at 3:54
  • so at any moment, only 1 particular instance/database run on 1 particular node, another node might run another instance, but it is a separate instance with its own database - right ? (sorry I know I sounds naggy, just wanna be sure) – tiongmaru Feb 7 '16 at 4:21
  • Correct -- a node could run more than one instance at a time if it has sufficient resources, but any given instance is only active on one node at a time, and a single database is an active member of only one instance. (Now, you might be able to set up two instances such that instance A has a db active and mirrors or replicates that to instance B, but that would be generally overly complicated.) – techieb0y Feb 8 '16 at 2:21
  • just curious - why can't ms sql do active-active ? – tiongmaru Feb 9 '16 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.