Categoría: Sin categorizar

por Fran Lens 2011-11-17

Cambiar automáticamente el valor de «Max Server Memory» si ocurre un Failover

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