This takes one table in master, two stored procedures, and one job that is set to execute on startup and calls the first stored procedure ([master].[dbo].[auto_MemoryReconfigure]). I set the default memory to use all but 3 gb on each node. That allows enough for the OS, and for a failover to start. Once the first failover is detected, the memory on all instances gets rebalanced to use in total only what each server has. You will also need linked servers set up on each instance in the cluster to all other instances on the cluster. This written for a 2-node cluster, but could be used as a framework for more than that. If you've got a memory-constrained cluster like I do, it REALLY helps out.

Table Definition:

CREATE TABLE [dbo].[ClusterMemory](

[Instance] [varchar](50) NOT NULL,

[DefaultNode] [varchar](50) NOT NULL,

[DefaultMemory] [int] NOT NULL,

[FailoverMemory] [int] NOT NULL,

[Failed] [bit] NOT NULL,

CONSTRAINT [PK_ClusterMemory] PRIMARY KEY CLUSTERED

(

[Instance] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[ClusterMemory] ADD CONSTRAINT [DF_ClusterMemory_Failed] DEFAULT ((0)) FOR [Failed]

This procedure should be automatically executed at startup, either automatically (I couldn't get it to work right) or by a job that executes at Agent startup.

ALTER proc [dbo].[auto_MemoryReconfigure]

as

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

/****************************************************************************/

/* Procedure to reconfigure memory on a clustered instance. */

/* Procedure will automatically execute at startup by a job. */

/* Expects: No Parameters */

/* Requires table master.dbo.ClusterMemory */

/* Calls stored procedure master.dbo.MemoryReconfigure */

/* */

/* Written by: Murray DeVore */

/* Date Written:07/29/2009 */

/* Revision Log */

/* Revised By Date Comments */

/* ------------- ---------- --------------------------------------------*/

/****************************************************************************/

declare @InstanceName as nvarchar(128)

declare @ServerName as nvarchar(128)

declare @DefaultNode as nvarchar(128)

declare @MemoryToUse as int

declare @MemoryInUse as int

set @InstanceName = (select @@SERVERNAME)

set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))

set @DefaultNode = (select DefaultNode from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')

/*Check to see if instance is running where it is supposed to and set memory accordingly*/

IF @DefaultNode = @ServerName

BEGIN

set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

UPDATE [].master.dbo.ClusterMemory SET Failed = 'False' WHERE Instance = @InstanceName

print 'Default Node'

END

ELSE

BEGIN

set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

UPDATE [].master.dbo.ClusterMemory SET Failed = 'True' WHERE Instance = @InstanceName

print 'Fail Node'

END

IF @MemoryInUse @MemoryToUse

BEGIN

exec sp_configure 'max server memory (MB)',@MemoryToUse

RECONFIGURE

print @InstanceName+' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

END

ELSE print @InstanceName+' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

/*Send email/page alert */

declare @sub as varchar(100)

declare @msg as varchar(1000)

declare @to as varchar(100)

set @msg = 'Instance '+@InstanceName+' has restarted and is now running on node '+@ServerName+'. Its default node is '+@DefaultNode+'.'

SET @to = (select pager_address from msdb..sysoperators where name = 'SQL Server DBA')

--set @to = (select email_address from msdb..sysoperators where name = 'SQL Server DBA')

set @sub = 'WARNING: Instance '+@InstanceName+' has restarted.'

exec msdb.dbo.sp_send_dbmail @recipients = @to

, @subject = @sub

, @body = @msg

/*Tell the other instances on the cluster to check themselves*/

DECLARE @sql varchar(1000)

DECLARE curInstance CURSOR

READ_ONLY

FOR select 'EXEC '+QUOTENAME(Instance)+'.master.dbo.MemoryReconfigure' from [].master.dbo.ClusterMemory where Instance @InstanceName

OPEN curInstance

FETCH NEXT FROM curInstance INTO @sql

WHILE (@@fetch_status -1)

BEGIN

IF (@@fetch_status -2)

BEGIN

exec (@sql)

END

FETCH NEXT FROM curInstance INTO @sql

END

CLOSE curInstance

DEALLOCATE curInstance

This stored proc is called by the first one, only on each of the other instances on the cluster.

ALTER proc [dbo].[MemoryReconfigure]

as

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

/****************************************************************************/

/* Procedure to reconfigure memory on a clustered instance. */

/* Procedure is called remotely by auto_MemoryReconfigure */

/* Expects: No Parameters */

/* Requires table master.dbo.ClusterMemory */

/* */

/* Written by: Murray DeVore */

/* Date Written:07/29/2009 */

/* Revision Log */

/* Revised By Date Comments */

/* ------------- ---------- --------------------------------------------*/

/****************************************************************************/

declare @InstanceName as nvarchar(128)

declare @ServerName as nvarchar(128)

declare @DefaultNode as nvarchar(128)

declare @MemoryToUse as int

declare @MemoryInUse as int

declare @FailedCount as int

set @InstanceName = (select @@SERVERNAME)

set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))

set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')

set @FailedCount = (select count(Failed) from [].master.dbo.ClusterMemory WHERE Failed = 'True')

IF @FailedCount = 0

/* Make sure memory is set to default allocation */

BEGIN

set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

END

ELSE

/* Failover condition, reduced memory allocation */

BEGIN

set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

END

IF @MemoryInUse @MemoryToUse

BEGIN

exec sp_configure 'max server memory (MB)',@MemoryToUse

RECONFIGURE

print @InstanceName+ ' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

END

ELSE print @InstanceName+ ' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)