What is the logic of the Active/Active cluster vs Active/Passive cluster?
I have searched a lot and read lots of documents but have not found the answer.
In active active cluster can I have 1 database which is in shared storage and that database have 2 instances?
What is the pros/cons of active active cluster?
In active passive cluster can I have one database which is in shared storage and it use active node. If active node is shutdown, will the passive node become the active node?
Best Answer
Active/Active refers, typically, to the idea of having multiple copies of a database available concurrently. However, in SQL Server, there is no way to accomplish that for read-write databases, other than the somewhat edge case of sharding data across mutliple instances. In SQL Server, Active/Active is normally used to denote a multi-node Windows Server Failover Cluster that has multiple SQL Server instances installed, where each node has a separate instance. However, for this to work successfully, each node needs to be configured with enough memory and CPU resources to successfully serve all instances in case that node is the only node left running at some point.
Active/Passive refers to having a multi-node cluster where one node serves the database(s), while the other node is available to take the load if the primary node fails.
As an example of Active/Active, consider the following:
2 computers configured in a Windows Server Failover Cluster, Node "A" and Node "B". The cluster has two instances installed, "X" and "Y". Typically, "X" runs on node "A", and "Y" runs on node "B". This means the horsepower of both machines can be combined to serve the needs of both instances. If, however, node "A" goes down, then node "B" will host both "X" and "Y" instances.
MSSQLTips.com has a great article comparing the two types of clustering.
MSDN has a good blog post on how to create an Active/Active cluster using Hyper-V.
From the description in your question it sounds to me like you want a typical 2-node cluster with a single instance serving a single database. The database resides on shared storage so that if the active node (the one currently serving the database) goes down, the passive node will automatically start up the instance and begin serving the database from there. Once the original node is brought back online, the instance could be moved back over to it, although that is not strictly necessary.