Active/Active cluster on 2005 - can I oversubscribe RAM now?

Recently, I was in a training course about the new features of 2005.
The trainer said that 2005 dealt with active/active memory differently
during a failover - as the secondary came online, it would start
taking more memory, and the primary would release memory. So, on my
two boxes with 16gb RAM, I allocated 10 for one node and 12 for the
other (before we were at 6 & 8). However, when we had a failover it
appeared that the secondary was going to take as much memory as it
could - I had less than 1gb available by the time we "rolled" the
secondary back to its original box.
But, that begs the question - was that advice incorrect? On an active-
active cluster on 2005, does the memory still need to be set to not
oversubscribe?
Thanks in advance.
I don't oversubscribe memory in such a setup. If you want to have predictable
performance, I'd still recommend you ensure that the sum of the max server
memory allocation for all the instances be below the available memory. And if
that results in less than the required amount for a particular instance, add
more memory to the nodes to accommodate it, and save yourself the headache.
Linchi
"M Bourgon" wrote:

> Recently, I was in a training course about the new features of 2005.
> The trainer said that 2005 dealt with active/active memory differently
> during a failover - as the secondary came online, it would start
> taking more memory, and the primary would release memory. So, on my
> two boxes with 16gb RAM, I allocated 10 for one node and 12 for the
> other (before we were at 6 & 8). However, when we had a failover it
> appeared that the secondary was going to take as much memory as it
> could - I had less than 1gb available by the time we "rolled" the
> secondary back to its original box.
> But, that begs the question - was that advice incorrect? On an active-
> active cluster on 2005, does the memory still need to be set to not
> oversubscribe?
> Thanks in advance.
>
|||On Jan 3, 8:26 am, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I don't oversubscribe memory in such a setup. If you want to have predictable
> performance, I'd still recommend you ensure that the sum of the max server
> memory allocation for all the instances be below the available memory. And if
> that results in less than the required amount for a particular instance, add
> more memory to the nodes to accommodate it, and save yourself the headache.
Okay. It seemed like an easy way to get several GB of ram without
consequence, if MS had coded for it, so we did it. Mea culpa.
|||SQL 2005 does have dynamic mamory even when using AWE. However, the
mechanism does not allow for dynamic memory balancing between instances.
The OS signals all SQL instances that there is memory pressure and to reduce
physical RAM usage. So, all instances try and shrink. When pressure is
off, all instances get the same signal and can try to expand. The instances
do not communicate to each other about their respective memory needs and
usage, so there is no possibility of "balancing" memory. You can write a
script that executes on a SQL failover event to reset maximum memory on SQL
instances on the new node.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"M Bourgon" <bourgon@.gmail.com> wrote in message
news:8658516d-ab5c-4c5e-b1ad-0a65b805033f@.s19g2000prg.googlegroups.com...
> Recently, I was in a training course about the new features of 2005.
> The trainer said that 2005 dealt with active/active memory differently
> during a failover - as the secondary came online, it would start
> taking more memory, and the primary would release memory. So, on my
> two boxes with 16gb RAM, I allocated 10 for one node and 12 for the
> other (before we were at 6 & 8). However, when we had a failover it
> appeared that the secondary was going to take as much memory as it
> could - I had less than 1gb available by the time we "rolled" the
> secondary back to its original box.
> But, that begs the question - was that advice incorrect? On an active-
> active cluster on 2005, does the memory still need to be set to not
> oversubscribe?
> Thanks in advance.
|||On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> SQL 2005 does have dynamic memory even when using AWE. However, the
> mechanism does not allow for dynamic memory balancing between instances.
> The OS signals all SQL instances that there is memory pressure and to reduce
> physical RAM usage. So, all instances try and shrink. When pressure is
> off, all instances get the same signal and can try to expand. The instances
> do not communicate to each other about their respective memory needs and
> usage, so there is no possibility of "balancing" memory. You can write a
> script that executes on a SQL failover event to reset maximum memory on SQL
> instances on the new node.
Geoff, thanks for the clarification. I guess that the followup
question is, how would you write such a script? Have the instance
check the physical server name, and if it's not the right one then do
an sp_configure to lower the max server memory, with recompile? And I
assume that'd be a job on the node scheduled to run on startup?
Thanks,
Michael
|||You can have the cluster software run an application or a script following a
failover event. Use the cluster admin tool to set it up.
The Cluster.exe command can get information via the command-line, and I am
pretty sure there is some WMI stuff that lets you see cluster state.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"M Bourgon" <bourgon@.gmail.com> wrote in message
news:72a269c9-cb8a-44ff-9142-25bc37c7aed7@.k8g2000hsf.googlegroups.com...
> On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Geoff, thanks for the clarification. I guess that the followup
> question is, how would you write such a script? Have the instance
> check the physical server name, and if it's not the right one then do
> an sp_configure to lower the max server memory, with recompile? And I
> assume that'd be a job on the node scheduled to run on startup?
> Thanks,
> Michael
|||On Jan 4, 11:14 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> You can have the cluster software run an application or a script following a
> failover event. Use the cluster admin tool to set it up.
> The Cluster.exe command can get information via the command-line, and I am
> pretty sure there is some WMI stuff that lets you see cluster state.
Fantastic! Thanks again.