SQL Server failover clustering is widely implemented in the world. One of the DBA’s tasks is to adjust memory when failover happens. When that happens, 2 or more SQL instances are running on one physical node, then max memory for both instances should be adjusted. When the failed nodes are up, failback will perform, then DBAs need to do memory adjustments again on all affected instances. It would be more productive if this process can be automated. In this post, I will give you my ideas and the implementation of it.

The idea is that

• A process reads the location of the instance from the cluster
• Once the instance distribution is confirmed, this process calls a function to return the memory configuration for that distribution
• The process uses the memory configurations returned by the function to adjust instances in the cluster.

A table needs to be created to host the node information. MaxMemory is the maximum memory can be assigned to all the SQL Server instances on this node because we always want to keep some memory for the OS.

create table dbo.ClusterPhysicalNode
(
	NodeName varchar(128) primary key,
	MaxMemory int not null default(0)
)
--This table can be populated from DMV sys.dm_os_cluster_nodes

Following code can return physical node name from SQL Server instance

select cast(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') as varchar(128)), cast(@@ServerName as varchar(128))

sp_configure can be used to ajust memory settings for each instance.

Now all technical difficulties and exisitng known configurations are clear. The problem becomes to how to calculate the memory distribution. I create a table to give every SQL instance a “weight” value and based on different weights to calculate the memeory configuration under various situations.

create table dbo.ClusterSQLNode
(
	NodeName varchar(128) primary key,
	Weight int not null default(0)
) 

Let’s say you have 3 active-active cluster, 3 SQL Server instances, S1, S2, and S3 with 3 Nodes, N1, N2, and N3. Every node has 32 GB memory but maximum allowable memory for SQL Server is 28GB, 28672 MB. In ClusterPhysicalNode table, I will put 28672 into MaxMemory for each node. Assuming you want to give S1 55% of memory, S2 25% of memory, and S3 20% of memory when those 3 are running on one node. I will put (S1,55), (S2, 25), (S3, 20) in table ClusterSQLNode table. The calculation of memory configuations on a node will be

Allowable memory * Weight of SQL Node / Total weight of all SQL Instances on the node.

Let’s say in the normal case S1 is running on N1. S1 should get memory configuration 28672 * 55 / 55. ( the First 55 is the weight of the S1, the second 55 is the total weight of all SQL instances on N1). So, S1 gets 100% of allowable memroy on node N1. Assuming failover happens, N1 now has S1 and S3. Memory settings for S1 will become to 28672 * 55 / (55 + 20) = 21026 MB. Memory settings for S3 will be 28672 * 20 / (55 + 20) = 7646 MB. This seems work. Now let’s implement it.

First, I think DBAs will be interested to know and test the weights for different failover scenarios. I create a function for this purpose as well as the process itself.

create type dbo.ClusterNodeDistribution as table
(
	InstanceName varchar(128) primary key,
	NodeName varchar(128) NOT NULL,
	unique(NodeName, InstanceName) -- this is not necessary, just for index purpose.
)
go
create function dbo.ClusterMemoryDistribution(@ClusterNodeDistribution ClusterNodeDistribution readonly)
returns @ret table (InstanceName varchar(128), Memory int)
as
begin
	insert into @ret(InstanceName, Memory)
		select 
				InstanceName,
				cast((
					(select b.MaxMemory from ClusterPhysicalNode b where b.NodeName = a.NodeName)
					*(select c.Weight from ClusterSQLNode c where c.NodeName = a.InstanceName) * 1.0
					/(
							select sum(d.Weight) 
							from ClusterSQLNode  d 
							where NodeName in (
												select InstanceName 
												from @ClusterNodeDistribution e
												where e.NodeName = a.NodeName
											)
					)
				) as int)
		from @ClusterNodeDistribution a
		
	return
end
go

Ok, now it’s time to implement the process

create procedure dbo.ClusterSetAll @Retry int = 10, @debug int = 0
as
begin
	set xact_abort off
	declare @ClusterNodeDistribution ClusterNodeDistribution
	declare @InstanceName nvarchar(max), @Proc sysname, @Errors varchar(max) = '', @MaxMemory int, @SQL nvarchar(max), @CurrentMemory int, @Executions int = 0

	while @Executions < @Retry
	begin
		begin try
			declare c cursor local static for
				select NodeName
				from ClusterSQLNode
			open c
			fetch next from c into @InstanceName
			while @@FETCH_STATUS = 0
			begin
				begin try
					select @Proc = QUOTENAME(@InstanceName)+'.master..sp_executesql'
					insert into @ClusterNodeDistribution
						exec @Proc N'select cast(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') as varchar(128)), cast(@@ServerName as varchar(128))'
				end try
				begin catch
					select @Errors = @Errors + ERROR_MESSAGE()
				end catch
				fetch next from c into @InstanceName
			end
			close c
			select @SQL = '
							select @CurrentMemory = cast(value as int) from sys.configurations where name = ''max server memory (MB)'' 
							if @CurrentMemory!= @MaxMemory and @Debug = 0
							begin
								exec sp_configure ''show advanced options'', 1
								reconfigure with override
								exec sp_configure ''min server memory (MB)'', 0
								exec sp_configure ''max server memory (MB)'', @MaxMemory
								exec sp_configure ''show advanced options'', 0
								reconfigure with override
							end
						'
			deallocate c
			declare c cursor local static for
				select InstanceName, Memory 
				from dbo.ClusterMemoryDistribution(@ClusterNodeDistribution) a
			open c
			fetch next from c into @InstanceName, @MaxMemory
			while @@FETCH_STATUS = 0
			begin
				select @Proc = QUOTENAME(@InstanceName)+'.master..sp_executesql'
				exec @Proc @SQL, N'@MaxMemory int, @CurrentMemory int output, @Debug int', @MaxMemory, @CurrentMemory output, @debug
				if @debug !=0
					print 'Memory on ' + @InstanceName + ' will be adjusted from ' + cast(@CurrentMemory as varchar(20)) + ' to ' + cast(@MaxMemory as varchar(20))

					
				fetch next from c into @InstanceName, @MaxMemory
			end
			close c 
			deallocate c
			return
		end try
		begin catch
			print 'Execution ' + cast(@Executions as varchar(20)) + ' is failed.'
			print error_message()
			select @Executions = @Executions + 1
		end catch
	end 
end

This procedure should be scheduled when SQL Server Agent starts and run once every 10 minutes or 20 minutes. It will only adjust the memory settings when it's needed.