Multiple instances on a cluster?

Question

Multiple instances on a cluster?

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

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

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?

mdevore Ten Centuries Points: 1293 More actions · Answer 2

Issuing a RECONFIGURE will make the memory settings take effect.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 3

...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.

mdevore Ten Centuries Points: 1293 More actions · Answer 4

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Hi Mdevore,

What a great work! Tremendous indeed!

I really appreciate your innovative approach! Keep it up!

Thanks for your post!

Best Regards,

SQLBuddy

Tom Van Zele SSC Veteran Points: 207 More actions · Answer 6

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

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