I would like to set up a multiple-instance (active/active) cluster using two nodes. They have eight CPUs each with 18431 MB of memory each. I am using a SAN. I will have two different databases on each node for two different applications - HR and finance. I will have each virtual server residing in a different MSCS cluster group, with a different of set of IP addresses, a distinct network name, and data files that reside on separate sets of shared cluster disks.
Is it possible to have one database on its instance on node A active and the same database inactive on its own instance on node B?
Is it possible to have one database on the default instance active and one database on the named instance inactive - all on the node A. Is it possible to have the opposite on node B? Can there be a failover and have both databases active on the failed node?
How do I take advantage of the power of the two servers at the same time?
You cannot do shared database access in a cluster. It is simply not possible. There is only one copy of your database, and it will reside on the node where you place it or it gets placed due to a failover. When a node fails, the databases are transferred to the other node and restarted, but they are precisely the same databases regardless of which node has ownership. During failover, yes, both SQL Server instances can run on a single node which is what clustering was designed for. If you want to take advantage of the processing capacity on both servers, you can configure one SQL Server instance to run on Node A and a different SQL Server instance to run on Node B. Keeping in mind that during a failover when both SQL Server instances are running on the same node, you can get degradation in performance.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.