Multiple instances on a cluster?

  • Thanks for posting.

    I didn't see where there was a server "restart" for the new memory to take affect. It only seems as though the "configured" value is changing and not the "running". Where is this done?

  • Issuing a RECONFIGURE will make the memory settings take effect.

  • ...well I guess it depends as to whether a server restart is required. If SQL already grabbed the "max" memory it's not going to release it. I don't think it will quickly grab all of the memory just after a restart.

    But I now see how this can be achieved, or how you're doing it. You could set the memory for when the server comes up (future setting) to what it should be in the event it fails over to the other node, so when the failover occurs it has the proper memory setting for that configuration.

    thanks again.

  • Steve (8/11/2009)


    ...well I guess it depends as to whether a server restart is required. If SQL already grabbed the "max" memory it's not going to release it. I don't think it will quickly grab all of the memory just after a restart.

    But I now see how this can be achieved, or how you're doing it. You could set the memory for when the server comes up (future setting) to what it should be in the event it fails over to the other node, so when the failover occurs it has the proper memory setting for that configuration.

    thanks again.

    Its actually the min server memory that can cause you problems. That's the lowest it can go. If you have this set to the default (0), SQL will manage it based on the available memory. Max server memory can be changed on the fly and does not require a restart. For example, if I have 7gb allocated to 2 instances on node a with 16gb on it and 14gb to a third instance on node b. When a failover occurs on node b, thereby placing 3 (or more) instances on a, I can change the max server memory to 4gb for 1, 4gb for 2, and 6 gb for the third (failed) instance, and issue a RECONFIGURE for each. Now I'm back at 14gb max in use (by SQL Server) for node a, and the OS still has 2gb to work with. And since SQL Server doesn't immediately grab the max setting, you've got time to rebalance everything so the OS doesn't run into trouble. When all instances are back where they are suppsed to be, the scripts will detect that and reset the memory to 7/7/14 (or whatever you have them set to). The only drawback I've seen is that changing the max will flush the cache, resulting in a temporary performance hit, but this is outweighed by the gains received when not running in failed state.

    Ex. Table Settings: (hope you can sort out the columns, I can't get it to format right :crazy: )

    InstanceDefaultNodeDefaultMemoryFailoverMemoryFailed

    Inst1B1433661440

    Inst2A716840960

    Inst3A716840960

  • Hi Mdevore,

    What a great work! Tremendous indeed!

    I really appreciate your innovative approach! Keep it up!

    Thanks for your post!

    Best Regards,

    SQLBuddy

  • Hi all,

    I have created a little update on mdevore's script. It was an excellent start, but there were some things that I didn't like, for instance: I don't like the idea of a 'default' node and a 'default' memory setting.

    Therefore I changed this in using 'weights' for a given instance.

    Please read my post for details: http://synsol.eu/blog/2011/03/sql-clustered-instances-automatic-max-server-memory-settings/[/url]

    Tom

    Tom Van Zele | Blog | Twitter | LinkedIn

  • Hi All,

    After 7 years, this question is still put on the table.

    I want to utilise a two-node windows cluster (N1, N2) for two different sql server clusters, let's say S1 and S2 on a hardware:

    24 cores,

    256 GB memory

    Shared clustered disks.

    If N1 fails than S1 and S2 will switch to N2 (this won't be an issue for failing over the both instances).

    My plan is to use Standard edition of SQL Server 2016, so S1 and S2 use 128 GBs each.

    However the HW resources concerns me, as well the two sql server clusters.

    I know that a three-node solution is the better option, but I'm interested for the two-node solution (to minimise the costs).

    Can anyone share more about this question?

    Igor Micev,My blog: www.igormicev.com

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply