Whats the best way to setup memory on a four-node cluster?

Though it is more to do with SQL Storage  kind of question, I have just dragged into Performance tuning section for the sake of large installation you talk with clustering.

For the sake of discussion it seems better to plan ahead for the failing instances since, for example, two of the four servers could go down. In the majority of cases, you’re better off configuring each instance to use most of the memory available on each node. Let’s say that each node has 32GB of RAM. Setting max server memory to something like 28GB for each instance would be reasonable.

So when it goes down or in the event of a node failure, the instance running on that node would fail over to the passive node and could potentially gain access to 28GB of memory. The other two instances would remain unaffected; they keep running on the other two nodes within the cluster.

If you are having only SQL Server instances as a dedicated box then better to leave the memory settings as 'dynamic'; if this is the case of having OLAP & OLTP application databases on these instances, you’re going to have to set some carefully selected memory counters for your relational engine instances as they won’t give up memory they’ve acquired. Though this is the case until SQL Server 2000 version but it is unlikely with SQL Server 2005 and 64-bit instances that have access to a large address space with or without AWE; even when you do use AWE in this situation, it’s dynamic and can release memory when requested.

The motive of this post is to "be prepared for the worst case scenario", in order to achieve that you need to plan and deploy. Say in practical terms, unless you’ve really over-engineered the system, it’s unlikely you’re going to survive three of four nodes going down in a cluster running five SQL Server relational engine instances and one Analysis Services instance. This would likely deliver a good combination of high availability (HA) support while allowing you to get decent utilization of expensive resources. Further keep an eye on the blogs on this website for:

http://sqlserver-qa.net/blogs/x64/ for X64 based

http://sqlserver-qa.net/blogs/perftune - security & performance tuning

http://sqlserver-qa.net/blogs/tools - tools and storage engine