Cambiar automáticamente el valor de “Max Server Memory” si ocurre un Failover

For English version visit this link Automatic change “Max Server Memory” value if a Failover occurs
 
En uno de nuestros entornos, tenemos un cluster de dos nodos activo-activo con una instancia SQL Server en cada uno.
 Durante esta semana, sufrimos una caida del servicio en uno de los nodos, esto provocó que se realizara un failover al nodo que quedaba disponible. El problema que nos encontramos es que el nodo tenía solamente 25GB de RAM y el valor de “Max Server Memory” asignado a cada instancia era de 20GB, por lo que corriamos el riesgo de que si las dos instancias empezaban a necesitar RAM llegaria un momento que el sistema operativo Windows perdería rendimiento.
 
La idea es mantener siempre un mínimo de 3GB de RAM disponible para Windows, por lo que generamos unos scripts ejecutados desde job para que en caso de que dos instancias se encuentren en el mismo nodo, el valor de “Max Server Memory” se reduzca, y en el caso que vuelvan a su estado normal (una instancia por nodo) el valor aumente.
 
El primer paso es crear los Linked Server en cada Instancia (en las dos instancias es necesario crear los dos Linked Server), una vez hecho esto, creamos un Job en cada instancia el cual será el encargado de chequear si en el nodo se encuentran 1 o 2 instancias, y en el caso que así sea modificar el valor “Max Server Memory”.
 
--1. Creamos los Linked Server en cada Instancia (Requerido)
--Valores a modificar: @server, @datasrc
exec sp_addlinkedserver @server='ServerA', @srvproduct='', @provider='SQLNCLI', @datasrc='ServerA\InstanceA'
exec sp_addlinkedserver @server='ServerB', @srvproduct='', @provider='SQLNCLI', @datasrc='ServerB\InstanceB'

--2. Creamos Job con el siguiente contenido en cada Instancia
--Valores a modificar: @memorynotshared, @memoryshared, ServerA, ServerB
DECLARE @memorynotshared as int
DECLARE @memoryshared as int
SET @memorynotshared = 20496 --Valor de Max Server Memory cuando el Nodo tiene 1 Instancia
SET @memoryshared = 10496 --Valor de Max Server Memory cuando el Nodo tiene 2 Instancias
IF (select * from openquery([ServerA], 'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')')) = (select * from openquery([ServerB], 'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')'))
BEGIN
IF (select value from sys.configurations where name = 'max server memory (MB)') = @memorynotshared
BEGIN
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'max server memory', @memoryshared;
RECONFIGURE;
END
END
ELSE
BEGIN
IF (select value from sys.configurations where name = 'max server memory (MB)') = @memoryshared
BEGIN
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'max server memory', @memorynotshared;
RECONFIGURE;
END
END

About Fran Lens
SQL Server Analyst at Avanade Spain http://www.lensql.net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: