August 4, 2010 at 10:52 am
We want to migrate three SQL-2008 instances (db engine) from two standalone servers to an active-active-passive failover cluster.
Here are the memory settings currently (on the standalone servers SERVER1 and SERVER2):
SERVER1\instanceA:
min memory: 15 GB
max memory: 23 GB
SERVER2\instanceB:
min memory: 0 GB
max memory: 11 GB
SERVER2\instanceC:
min memory: 0 GB
max memory: 12 GB
All three instances have "Lock Pages in Memory" ENABLED for the SQL-service account.
The 3 instances will be migrated to the cluster as follows:
SERVER1\instanceA: to cluster server CLUSTER1
SERVER2\instanceB: to cluster server CLUSTER2
SERVER2\instanceC: to cluster server CLUSTER2
Server CLUSTER3 will serve as the passive node.
The 3 standalone servers and the 3 servers in the cluster ALL have the same RAM: 32 GB
We want to account for the possibility that all 3 instances might fail over to the passive node at the same time, and they will need to share (reduced) resources for a short length of time.
In order to achieve minimum disruption to operations in the event that all 3 instances failover to the passive node simultaneously:
(1) should I keep "Lock Pages in Memory" DISABLED when I move the instances to the cluster environment?
(2) should I set min memory to 0 for all instances in the cluster?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 4, 2010 at 11:54 am
keep the locked pages but you will need to review your MAX Memory settings!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2010 at 12:59 pm
Perry Whittle (8/4/2010)
keep the locked pages but you will need to review your MAX Memory settings!
Hmm, I thought if I have locked pages disabled, that I would be able to manually adjust the max mem settings if more than one instance fails over to the passive node at the same time and memory allocation would be dynamically adjusted.
With "locked pages in memory" turned on, the dynamic re-allocation of memory (after re-adjusting the max-mem setting following failover) is not possible.
Is that not the case?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 5, 2010 at 1:11 pm
With locked pages in memory sql server will never release below the min memory setting. It is important to set both the min and max memory when using locked pages.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2010 at 1:23 pm
Perry Whittle (8/5/2010)
With locked pages in memory sql server will never release below the min memory setting. It is important to set both the min and max memory when using locked pages.
Thank you, I think I understand now.
So I will adjust the minimum memory settings on the 3 SQL instances, so that the SUM of the 3 min-mem settings will be lower than the physical RAM on the failover server (with enough memory left over for O/S operations, SQL-CLR etc.)
Maintain the max mem settings as they currently are, and keep the "lock pages in memory" setting turned ON.
How does that sound?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 5, 2010 at 1:35 pm
Even with locked pages the OS can still ask ever so nicely for SQL to free some memory it just can't force it to as the buffer pool pages are locked.
Lock pages only applies to the buffer pool and not other areas of SQL server memory
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply