January 29, 2013 at 12:49 pm
This seems like it should be so simple, but apparently not!
I have a 2-node, 2-Instance SQL cluster. Each server has 96 GB RAM. If there is just one Instance running per node, I want each Instance to have Max Memory set to 88 GB. If both Instances are running on one node, then set each Instance to Max Memory 44 GB. Sounds simple, right?
Any time an Instance starts up, I can run a Job (Schedule type: Start automatically when SQL Server Agent starts). This job could determine if the other Instance is running on this server or not, and adjust each Instance's Max Memory as needed. But I am having no luck!
Problem 1: how can I tell which SQL Instances are actually up and running on this node of the cluster? I haven't made any progress on this!
I assume you would use WMI, just like Configuration Manager does, but I don't know how.
Any idea how to determine which Instances are actually running on a cluster node? (it is easy to tell which are installed, but not which are running)
Problem 2: How can I have one Instance change Max Memory over on the other Instance?
I have tried to do this with Linked Servers, so I made Linked Servers between the Instances. I can easily see the Max Memory on the other server by running this:
SELECT * FROM OPENQUERY([INSTANCEB], 'SELECT value FROM sys.configurations WHERE name = ''max server memory (MB)''')
So I know my Linked Servers are working properly. But when I try to run any of these to actually adjust max memory, I get errors.
SELECT * FROM OPENQUERY([INSTANCEB], 'sp_configure ''show advanced options'', 1')
SELECT * FROM OPENQUERY([INSTANCEB], 'RECONFIGURE')
SELECT * FROM OPENQUERY([INSTANCEB], 'sp_configure ''max server memory'', 3000')
SELECT * FROM OPENQUERY([INSTANCEB], 'RECONFIGURE')
The first line gives this error:
Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'select name,
convert(int, minimum) as minimum,
convert(int, maximum) as maximum,
convert' in procedure 'sp_configure' is not compatible with the statement 'select duplicate_options = name
from sys.configurations
where lower(name collate Latin1_Genera' in procedure 'sp_configure'.
The second line gives this error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "RECONFIGURE". The OLE DB provider "SQLNCLI11" for linked server "INSTANCEB" indicates that either the object has no columns or the current user does not have permissions on that object.
Any ideas how you can have one Instance adjust the Max Memory over on another Instance?
Any help much appreciated!
January 29, 2013 at 1:03 pm
there are two ways to see where cluster nodes are;
1) go to Start> Administrative Tools> Failover Cluster Manager.
Click on the server cluster name
Click on Nodes
This will show you two instance (what you named your nodes during install)
click on one of them.
In the dialog box to the right it gives you the name, status, ect...
at the bottom it tells you where it lives.
2) Click on Server Manager> Features> failover Cluster Manager>Cluster Server Name (click the +).
Click on Nodes
This will show you two instance (what you named your nodes during install)
click on one of them.
In the dialog box to the right it gives you the name, status, ect...
at the bottom it tells you where it lives.
As for memory, I need more information about the server, is it a true active \ passive cluster?
MCSE SQL Server 2012\2014\2016
January 29, 2013 at 1:09 pm
Problem 2 is reasonably easy
CREATE PROCEDURE FixMemory
AS
EXEC sp_configure 'max server memory', 3000
RECONFIGURE
GO
EXEC FixMemory AT <linked server name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2013 at 1:11 pm
worker bee (1/29/2013)
there are two ways to see where cluster nodes are;
I was looking for a way to find out which Instances are running on which server programmatically, so that I could then change Max Memory programmatically. Do you think this can be done?
worker bee (1/29/2013)
As for memory, I need more information about the server, is it a true active \ passive cluster?
These are two identical servers, each with 96 GB RAM, running Windows Server 2008 R2, and they are set up as a Windows Cluster. I then have two Clustered Instances of SQL Server running on this Windows Cluster. Each Instance can run on each server. Under normal working conditions, the cluster is configured so that InstanceA runs on Node1, and InstanceB runs on Node2 (what I understand to be "Active/Active"). But there are a number of cases where both Instances can and do both end up running on just one Node. Then the Max Memory for the two Instances together adds up to 176 GB, and the server only has 96 GB! So that's why I want to auto-adjust the memory for both Instances down to 44 GB in this case.
January 29, 2013 at 1:46 pm
Thanks Gail!
I had to 1. actually create the SP over on the other server first, 2. add in both single-quotes and parentheses around ('FixMemory'), and 3. also put in a RECONFIGURE line. But what you gave me was the important bit, thanks!
Here's what works:
EXEC ('database.dbo.FixMemory') AT [INSTANCEB]
EXEC ('RECONFIGURE') AT [INSTANCEB]
January 30, 2013 at 10:57 am
OK, I've got what I need now. Again, pretty much thanks to Gail!
My question was: how do I find the Instances that are running on this Node of this cluster? Figuring out how to do that would be cool, but I actually don't need to.
Since this is a cluster, and the Instance names are fixed, I can just explicity look into the other Instance and ask, "what server are you running on?" You get this from SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
So then here is the basic statement to find out what physical server an Instance is running on, for a Linked Server:
SELECT * FROM OPENQUERY([INSTANCEB], 'SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')')
Here is the code to stuff that name into a variable so you can use it:
DECLARE @InstanceBComputer NVARCHAR(250)
SELECT @InstanceBComputer = CONVERT(NVARCHAR(250), RemoteServer ) FROM OPENQUERY([FQTC3SQL2012B\INSTANCEB], 'SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') as RemoteServer')
PRINT @InstanceBComputer
So now I can determine which physical server each Instance is running on (using this code), and then adjust those values as needed (using the code from the earlier post).
In my opinion, this should be done on *all* SQL Server clusters, in order to tune memory usage as Instances move between nodes. Otherwise you might set each Instance to use half the RAM on each Node, which is a waste and very dumb. Or you set each Instance to use all RAM on each Node, but then SQL is set to use double the physical RAM when both Instances move to one Node, which is also dumb.
I'm surprised everyone doesn't do this! Hopefully this will help you out if you choose to in the future..............
January 31, 2013 at 8:52 am
Would this work?
Each instance should have a drive that has a dependency set in cluster management. Place a text file somewhere on each drive, for each instance, and use the following script in a job that runs whenever the instance is started.
-- 'E:\DBA\SQLInst1\SQL1 is ActiveHere.txt' -- E: drive - this usually runs on NodeA
-- 'F:\DBA\SQLInst2\SQL2 is ActiveHere.txt' -- F: drive - this usually runs on NodeB
-- Job on NodeA
SET NOCOUNT ON
DECLARE @FileName varchar(255)
DECLARE @File_Exists int
SELECT @FileName='E:\DBA\SQLInst2\SQL2 is ActiveHere.txt' -- Check to see if this file exists for the instance that usually does NOT run on this node.
-- This would be the situation where both instances are running on one node
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
IF @File_Exists = 1
EXEC [SQLInst2\SQL2].msdb.dbo.sp_start_job @job_name = 'Set MaxMemory Values for Each Instance' -- This job would set lower MaxMemory values
ELSE
PRINT 'SQLInst2\SQL2 is not running on NodeB'
-- Job on NodeB
SET NOCOUNT ON
DECLARE @FileName varchar(255)
DECLARE @File_Exists int
SELECT @FileName='E:\DBA\SQLInst1\SQL1 is ActiveHere.txt' -- Check to see if this file exists for the instance that usually does NOT run on this node.
-- This would be the situation where both instances are running on one node
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
IF @File_Exists = 1
EXEC [SQLInst1\SQL1].msdb.dbo.sp_start_job @job_name = 'Set MaxMemory Values for Each Instance' -- This job would set lower MaxMemory values
ELSE
PRINT 'SQLInst1\SQL1 is not running on NodeB'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply