La conmutación por error automática es una característica crucial en SQL Server que garantiza alta disponibilidad y tiempo de inactividad mínimo en caso de fallo de una base de datos. Sin embargo, aunque el reflejo de bases de datos proporciona capacidades de conmutación por error automática, carece de soporte incorporado para manejar acciones externas que deben ocurrir cuando se produce una conmutación por error. Aquí es donde entra en juego el Service Broker y la activación automática.

Service Broker y activación automática

El Service Broker es un marco de mensajería en SQL Server que le permite construir aplicaciones asíncronas, confiables y escalables. También se puede utilizar para activar automáticamente un procedimiento almacenado cuando ocurren ciertos eventos, como una conmutación por error de la base de datos.

Para configurar la activación automática en el Service Broker, es necesario crear varios objetos de base de datos almacenados externamente a la base de datos reflejada:

• Un procedimiento almacenado para procesar los mensajes en la cola del Service Broker
• Una cola del Service Broker
• Un servicio del Service Broker
• Una ruta para el servicio del Service Broker
• Una notificación de evento para el evento DATABASE_MIRRORING_STATE_CHANGE
• Un procedimiento almacenado de activación automática

El procedimiento almacenado que cree deberá leer de la cola del Service Broker y procesar los mensajes. El evento enviado por el Service Broker estará en forma de un mensaje XML, que deberá analizar para determinar cómo ha cambiado el estado del reflejo de la base de datos.

Aquí tienes un ejemplo de cómo puedes leer y analizar el primer mensaje en la cola del Service Broker:

DECLARE @Message XML, @DBName sysname, @MirrorStateChange int, @ServerName sysname, @PostTime datetime, @SPID int, @TextData nvarchar(500), @DatabaseID int, @TransactionsID int, @StartTime datetime

/* Recibir el primer mensaje no leído en la cola del Service Broker */
RECEIVE TOP (1) @Message = CAST(message_body AS XML) FROM DBMirrorQueue;

/* Analizar el tipo de cambio de estado y la base de datos afectada */
SET @MirrorStateChange = @Message.value('(/EVENT_INSTANCE/State)[1]', 'int');
SET @DBName = @Message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
SET @ServerName = @Message.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname');
SET @PostTime = @Message.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
SET @SPID = @Message.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
SET @TextData = @Message.value('(/EVENT_INSTANCE/TextData)[1]', 'nvarchar(500)');
SET @DatabaseID = @Message.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int');
SET @TransactionsID = @Message.value('(/EVENT_INSTANCE/TransactionsID)[1]', 'int');
SET @StartTime = @Message.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime');

Configuración del Service Broker

Para implementar la activación automática en el Service Broker, es necesario crear los objetos de base de datos necesarios. Aquí tienes un ejemplo de cómo puedes crearlos:

Creación de una cola del Service Broker

/* Crear la cola si no existe */
IF NOT EXISTS (SELECT 1 FROM sys.service_queues WHERE name = 'DBMirrorQueue')
BEGIN
    CREATE QUEUE DBMirrorQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (PROCEDURE_NAME = dbo.DBA_MirroringStateChanged, MAX_QUEUE_READERS = 1, EXECUTE AS SELF);
END

Creación de un servicio del Service Broker

/* Crear el servicio si no existe */
IF NOT EXISTS (SELECT 1 FROM sys.services WHERE name = 'DBMirrorService')
BEGIN
    CREATE SERVICE DBMirrorService ON QUEUE DBMirrorQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
END

Creación de una ruta del Service Broker

/* Crear la ruta si no existe */
IF NOT EXISTS (SELECT 1 FROM sys.routes WHERE name = 'DBMirrorRoute')
BEGIN
    CREATE ROUTE DBMirrorRoute WITH SERVICE_NAME = 'DBMirrorService', ADDRESS = 'Local';
END

Creación de una notificación de evento

El evento DATABASE_MIRRORING_STATE_CHANGE no se captura automáticamente por el Service Broker. Es necesario crear manualmente una notificación de evento para generar una notificación para este evento. Aquí tienes un ejemplo:

/* Crear la notificación de evento si no existe */
IF NOT EXISTS (SELECT 1 FROM sys.server_event_notifications WHERE name = 'DBMirrorStateChange')
BEGIN
    CREATE EVENT NOTIFICATION DBMirrorStateChange ON SERVER FOR DATABASE_MIRRORING_STATE_CHANGE TO SERVICE 'DBMirrorService', 'current database';
END

Al configurar el Service Broker y la activación automática, puedes asegurarte de que se realicen las acciones necesarias cuando se produce una conmutación por error de la base de datos. Esto te permite manejar procesos externos, como la actualización de configuraciones de aplicaciones o la notificación a los administradores, de manera fluida y automática.

Recuerda personalizar los ejemplos de código proporcionados para que se ajusten a los requisitos específicos de tu base de datos y aplicación.