Changes to clustered sql server

Hi
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?
The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?
|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:

> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.6 5...
>
|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>
|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:

> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
>
|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:

> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171. 68...
>
|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65 ...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.

> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.

> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH

> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>