I wrote something bespoke that checks what drives are currently visible on the node (given that D: should always be on 1 node, and H: should always be on the other), and when they're on the same node, set MAXSERVERMEMORY appropriately on both instances.

I wrote something bespoke that checks what drives are currently visible on the node (given that D: should always be on 1 node, and H: should always be on the other), and when they're on the same node, set MAXSERVERMEMORY appropriately on both instances.

Great. I'll check on the link and try to test the script on my test environment.

Did you in any way blog the script you have created that sets the max memory once it detects that the instance are on the same node? :)

Any cons if I don't set some kind of script to re-balance the memory when cluster fails other than you need to do it manually and you cannot auto configure in a way the max memory setting if it say failure happens at 3AM?

You might get memory related problems if you have 2 instances with a combined max memory setting greater than that available in the machine.

I don't have a blog, but you can have a copy of the script I wrote. It's not universal, i.e. it was written specifically for our environment, but it shouldn't be too difficult to make it work for you (given that you only have 2 instances/2 nodes)...
in fact, you probably just have to change the drive letters over and put in linked server details. You might also want to play with how much RAM is left over after failover - change the bit:

/ 1024 / 1024) / 2) * 0.9

Once you have it set up, just create a SQL Server Agent job that runs at startup to execute this procedure.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster] Script Date: 05/13/2013 16:12:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Rebalance_RAM_in_Cluster]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster] Script Date: 05/13/2013 16:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
AS
--WAITFOR DELAY '00:00:15'
DECLARE @Command VARCHAR(2000)
DECLARE @RAM INT
DECLARE @RAM_event NVARCHAR(10)
DECLARE @Link VARCHAR(50)
DECLARE @RemSQL VARCHAR(500)
/*
AUTHOR: ANDREW BAINBRIDGE - MS SQL DBA
DATE: 13/05/2013
VERSION: 2.0
If there is a cluster failover that results in both SQL Server instances running on the same node, this script will
automatically rebalance the amount of RAM allocated to each instance. This is to prevent the combined RAM allocated to
SQL Server overwhelming the node.
If the D: drive and the H: drive are visible to the same host, that means that both
instances are running on the same node. In this event, the amount of RAM allocated to each of the SQL Servers
will be 90% of half the amount of total RAM in the server. E.g. (384GB / 2) * 0.9
If only the D: drive or H: drive is visible, then 90% of the total amount of RAM available on the server is allocated
to the SQL Server instance.
This stored procedure will also set the max server memory of the other SQL Server instance in the cluster. As this needs
to be run across the linked server, and the sp_procoption startup procedure is owned by SA (therefore can't use windows
authentication), the stored procedure will be run on SQL Server Agent startup, via a job.
*/
SET NOCOUNT ON;
BEGIN
IF (SELECT @@SERVERNAME) = 'MYSERVER\INSTANCE'
SET @Link = 'LINKED_SERVER_TO_OTHER_NODE'
ELSE
SET @Link = 'LINKED_SERVER_TO_MYSERVER\INSTANCE'
SET @Command = 'USE [master];
EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory (MB)'', $;
RECONFIGURE WITH OVERRIDE;'
IF OBJECT_ID('tempdb..#fd') IS NOT NULL
DROP TABLE #fd
CREATE TABLE #fd(drive CHAR(2), MBfree INT)
INSERT INTO #fd EXEC xp_fixeddrives
IF (SELECT COUNT(drive) FROM #fd WHERE drive IN ('D', 'H')) > 1
BEGIN
SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024) / 2) * 0.9) AS RAM_in_MB
FROM master.sys.dm_os_sys_info)
SET @Command = REPLACE(@Command, '$', @RAM)
SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
EXEC (@Command)
SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
EXEC (@RemSQL)
END
ELSE
BEGIN
SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024)) * 0.9) AS RAM_in_MB
FROM master.sys.dm_os_sys_info)
SET @Command = REPLACE(@Command, '$', @RAM)
SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
EXEC(@Command)
SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
EXEC (@RemSQL)
END
END
GO