Configuración de un grupo de disponibilidad de SQL Server para escalado de lectura en Linux

Artículo
06/28/2024

Se aplica a: SQL Server - Linux

En este artículo se explica cómo crear un grupo de disponibilidad Always On de SQL Server sin un administrador de clústeres. Esta arquitectura solo proporciona escalado de lectura. No proporciona alta disponibilidad.

Hay dos tipos de arquitecturas para los grupos de disponibilidad. En una arquitectura de alta disponibilidad se usa un administrador de clústeres para proporcionar una continuidad empresarial mejorada. Para crear la arquitectura de alta disponibilidad, vea Configuración de un grupo de disponibilidad AlwaysOn de SQL Server para alta disponibilidad en Linux.

Un grupo de disponibilidad con CLUSTER_TYPE = NONE puede incluir réplicas hospedadas en otras plataformas de sistema operativo. No puede admitir la alta disponibilidad.

Requisitos previos

Antes de crear el grupo de disponibilidad, debe:

Establecer el entorno de forma que todos los servidores que hospedarán las réplicas de disponibilidad se puedan comunicar.
Instale SQL Server.

En Linux, debe crear un grupo de disponibilidad antes de agregarlo como un recurso de clúster para que lo administre el clúster. En este documento, se proporciona un ejemplo que crea el grupo de disponibilidad.

Actualice el nombre de equipo de cada host.

Cada nombre de instancia de SQL Server debe cumplir con los siguientes requisitos:
- 15 caracteres como máximo.
- Debe ser único en la red.
Para establecer el nombre del equipo, edite /etc/hostname. El siguiente script le permite editar /etc/hostname con vi:
```
sudo vi /etc/hostname
```
Configure el archivo de hosts.

Nota

Si los nombres de host están registrados con su dirección IP en el servidor DNS, no hay que realizar los pasos siguientes. Compruebe que todos los nodos destinados a formar parte de la configuración del grupo de disponibilidad pueden comunicarse entre sí. (Un ping al nombre de host debe responder con la dirección IP correspondiente). Además, asegúrese de que el archivo /etc/hosts no contiene ningún registro que asigne la dirección IP de localhost 127.0.0.1 con el nombre de host del nodo.

El archivo de hosts de cada servidor contiene las direcciones IP y los nombres de todos los servidores que participarán en el grupo de disponibilidad.

El comando siguiente devuelve la dirección IP del servidor actual:
```
sudo ip addr show
```
Actualice /etc/hosts. El siguiente script le permite editar /etc/hosts con vi:
```
sudo vi /etc/hosts
```
En el ejemplo siguiente, se muestra /etc/hosts en node1 con adiciones para node1, node2 y node3. En este ejemplo, node1 hace referencia al servidor que hospeda la réplica principal y node2 y node3 hacen referencia a los servidores que hospedan las réplicas secundarias.
```
127.0.0.1    localhost localhost4 localhost4.localdomain4
::1          localhost localhost6 localhost6.localdomain6
10.128.18.12 node1
10.128.16.77 node2
10.128.15.33 node3
```

Instalación de SQL Server

Instale SQL Server. Los siguientes vínculos apuntan a las instrucciones de instalación de SQL Server para varias distribuciones:

Habilitación de los Grupos de disponibilidad AlwaysOn

Habilite los grupos de disponibilidad AlwaysOn en cada nodo en el que se hospede una instancia de SQL Server y, a continuación, reinicie mssql-server. Ejecute el siguiente script:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Habilitar una sesión de eventos AlwaysOn_health

Opcionalmente, puede habilitar los eventos extendidos (XE) para ayudar con el diagnóstico de la causa raíz cuando solucione los problemas de un grupo de disponibilidad. Ejecute el comando siguiente en todas las instancias de SQL Server:

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE = ON);
GO

Para obtener más información sobre esta sesión de XE, vea Configuración de eventos extendidos para grupos de disponibilidad.

Crear un certificado

El servicio SQL Server en Linux usa certificados para autenticar la comunicación entre los puntos de conexión de creación de reflejo.

El script de Transact-SQL siguiente crea una clave maestra y un certificado. Después, realiza una copia de seguridad del certificado y protege el archivo con una clave privada. Actualice el script con contraseñas seguras. Conecte con la instancia de SQL Server principal. Para crear el certificado, ejecute el script siguiente de Transact-SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

En este momento, la réplica principal de SQL Server tiene un certificado en /var/opt/mssql/data/dbm_certificate.cer y una clave privada en var/opt/mssql/data/dbm_certificate.pvk. Copie estos dos archivos en la misma ubicación en todos los servidores que hospedarán las réplicas de disponibilidad. Utilice el usuario de mssql o conceda permiso al usuario de mssql para tener acceso a estos archivos.

Por ejemplo, en el servidor de origen, el siguiente comando copia los archivos en el equipo de destino. Reemplace los valores <node2> por los nombres de las instancias de SQL Server que hospedarán las réplicas.

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

En cada servidor de destino, conceda permiso al usuario de mssql para que acceda al certificado.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

Crear el certificado en los servidores secundarios

El script de Transact-SQL siguiente crea una clave maestra y un certificado a partir de la copia de seguridad creada en la réplica principal de SQL Server. Actualice el script con contraseñas seguras. La contraseña de descifrado es la misma que ha usado para crear el archivo .pvk en un paso anterior. Ejecute el siguiente script en todos los servidores secundarios para crear el certificado:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

Crear los puntos de conexión de creación de reflejo de la base de datos en todas las réplicas

Los extremos de creación de reflejo de la base de datos usan el Protocolo de control de transmisión (TCP) para enviar y recibir mensajes entre las instancias del servidor que participan en las sesiones de creación de reflejo de la base de datos o que hospedan las réplicas de disponibilidad. El extremo de creación de reflejo de la base de datos escucha en un número de puerto TCP exclusivo.

El script de Transact-SQL siguiente crea un punto de conexión de escucha denominado Hadr_endpoint para el grupo de disponibilidad. Se inicia el punto de conexión y se concede permiso de conexión al certificado que ha creado. Antes de ejecutar el script, reemplace los valores entre < ... >. Opcionalmente puede incluir una dirección IP LISTENER_IP = (0.0.0.0). La dirección IP de escucha debe ser una dirección IPv4. También se puede usar 0.0.0.0.

Actualice el script de Transact-SQL siguiente para el entorno en todas las instancias de SQL Server:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

Nota

Si usa la edición de SQL Server Express en un nodo para hospedar una réplica de solo configuración, el único valor válido para ROLE es WITNESS. Ejecute el siguiente script en la edición de SQL Server Express:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

El puerto TCP en el firewall debe estar abierto para el puerto de escucha.

Importante

Para la versión de SQL Server 2017 (14.x), el único método de autenticación que se admite para el punto de conexión de creación de reflejo de la base de datos es CERTIFICATE. La opción WINDOWS no está disponible.

Para obtener más información, vea El punto de conexión de creación de reflejo de la base de datos (SQL Server).

Crear el grupo de disponibilidad

Cree el grupo de disponibilidad (AG). Establezca CLUSTER_TYPE = NONE. Además, establezca cada réplica con FAILOVER_MODE = MANUAL. Las aplicaciones cliente que ejecutan cargas de trabajo de informes o análisis se pueden conectar directamente a las bases de datos secundarias. También se puede crear una lista de enrutamiento de solo lectura. Las conexiones a la réplica principal reenvían las solicitudes de conexión de lectura a todas las réplicas secundarias de la lista de enrutamiento en modo Round Robin.

El script de Transact-SQL siguiente crea un grupo de disponibilidad denominado ag1. El script configura las réplicas de grupo de disponibilidad con SEEDING_MODE = AUTOMATIC. Esta configuración hace que SQL Server cree de manera automática la base de datos en todos los servidores secundarios después de que se agreguen al grupo de disponibilidad. Actualice el script siguiente para su entorno. Reemplace los valores <node1> y <node2> con los nombres de las instancias de SQL Server que hospedan las réplicas. Reemplace el valor <5022> con el puerto que haya definido para el punto de conexión. Ejecute el script de Transact-SQL siguiente en la réplica principal de SQL Server:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        ),
        N'<node2>' WITH (
            ENDPOINT_URL = N'tcp://<node2>:<5022>',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Unir instancias de SQL Server secundarias al grupo de disponibilidad

El script de Transact-SQL siguiente une un servidor a un grupo de disponibilidad denominado ag1. Actualice el script para su entorno. En cada réplica secundaria de SQL Server, ejecute el script siguiente de Transact-SQL para unirla al grupo de disponibilidad:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Agregar una base de datos al grupo de disponibilidad

Asegúrese de que la base de datos que se agrega al grupo de disponibilidad está en el modelo de recuperación completa y tiene una copia de seguridad de registros válida. Si la base de datos es una base de datos de prueba o una base de datos recién creada, realice una copia de seguridad. En el servidor de SQL Server principal, ejecute el script de Transact-SQL (T-SQL) siguiente para crear una base de datos denominada db1 y realizar una copia de seguridad de ella:

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';

En la réplica principal de SQL Server, ejecute el script de T-SQL siguiente para agregar una base de datos denominada db1 a un grupo de disponibilidad denominado ag1:

ALTER AVAILABILITY GROUP [ag1]
ADD DATABASE [db1];

Compruebe que la base de datos se crea en los servidores secundarios.

En todas las réplicas secundarias de SQL Server, ejecute la consulta siguiente para ver si la base de datos db1 se ha creado y está sincronizada:

SELECT * FROM sys.databases
WHERE name = 'db1';
GO

SELECT DB_NAME(database_id) AS 'database',
    synchronization_state_desc
FROM sys.dm_hadr_database_replica_states;
GO

Este grupo de disponibilidad no es una configuración de alta disponibilidad. Si necesita alta disponibilidad, siga las instrucciones de Configuración de un grupo de disponibilidad AlwaysOn de SQL Server para alta disponibilidad en Linux. En concreto, cree el grupo de disponibilidad con CLUSTER_TYPE=WSFC (en Windows) o CLUSTER_TYPE=EXTERNAL (en Linux). Luego, puede integrar con un administrador de clústeres mediante clústeres de conmutación por error de Windows Server en Windows o Pacemarker en Linux.

Conectar con réplicas secundarias de solo lectura

Hay dos maneras de conectarse a réplicas secundarias de solo lectura. Las aplicaciones se pueden conectar directamente a la instancia de SQL Server que hospeda la réplica secundaria y consultar las bases de datos. También pueden usar el enrutamiento de solo lectura, lo que requiere un agente de escucha.

Conmutación por error de la réplica principal en un grupo de disponibilidad de escalado de lectura

Cada grupo de disponibilidad tiene solo una réplica principal. La réplica principal permite lecturas y escrituras. Para cambiar la réplica principal, puede efectuar una conmutación por error. En un grupo de disponibilidad habitual, el administrador de clústeres automatiza el proceso de conmutación por error. En un grupo de disponibilidad con el tipo de clúster NONE, el proceso de conmutación por error es manual.

Hay dos maneras de efectuar una conmutación por error de la réplica principal en un grupo de disponibilidad de tipo de clúster NONE:

Conmutación por error manual sin pérdida de datos
Conmutación por error manual forzada con pérdida de datos

Conmutación por error manual sin pérdida de datos

Use este método si la réplica principal está disponible, pero necesita modificar temporal o permanentemente la instancia que hospeda dicha réplica principal. Antes de emitir la conmutación por error manual, asegúrese de que la réplica secundaria de destino está actualizada para evitar una posible pérdida de datos.

Para realizar la conmutación por error manual sin pérdida de datos:

Establezca las réplicas de destino principal y secundaria actuales en SYNCHRONOUS_COMMIT.

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Ejecute la consulta siguiente para identificar que las transacciones activas se confirman en la réplica principal y en al menos una réplica secundaria sincrónica:
```
SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 
```
La réplica secundaria se sincroniza si synchronization_state_desc es SYNCHRONIZED.
Actualice REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT a 1.

El siguiente script establece REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT en 1 en un grupo de disponibilidad denominado ag1. Antes de ejecutar el siguiente script, reemplace ag1 por el nombre del grupo de disponibilidad:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
Este valor garantiza que todas las transacciones activas se confirman en la réplica principal y en, al menos, una réplica secundaria sincrónica.

Nota

Esta opción no es específica de la conmutación por error y se debe establecer en función de los requisitos del entorno.
Establezca la réplica principal y las réplicas secundarias que no participan en la conmutación por error sin conexión para prepararse para el cambio de rol:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

Ascienda la réplica secundaria de destino a principal.

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

Actualice el rol de la réplica principal antigua y otras réplicas secundarias a SECONDARY, ejecute el comando siguiente en la instancia de SQL Server en la que se hospeda la réplica principal anterior:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
Nota:

Para eliminar un grupo de disponibilidad, use DROP AVAILABILITY GROUP. Para un grupo de disponibilidad creado con el tipo de clúster NONE o EXTERNAL, ejecute el comando en todas las réplicas que forman parte del grupo de disponibilidad.
Reanude el movimiento de datos, ejecute el siguiente comando para cada base de datos del grupo de disponibilidad en la instancia de SQL Server que hospeda la réplica principal:
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
Vuelva a crear cualquier cliente de escucha que haya creado para fines de escalado de lectura y que no esté administrado por un administrador de clústeres. Si el cliente de escucha original apunta a la réplica principal anterior, suéltela y vuelva a crearla para que apunte a la nueva réplica principal.

Conmutación por error manual forzada con pérdida de datos

Si la réplica principal no está disponible y no se puede recuperar justo en ese momento, deberá forzar una conmutación por error en la secundaria con pérdida de datos. Sin embargo, si la réplica principal original se recupera tras la conmutación por error, pasará a ostentar el rol principal. Para evitar discrepancias en los estados de las réplicas, quite la principal original del grupo de disponibilidad tras haber forzado la conmutación por error con pérdida de datos. Una vez que la principal original vuelva a estar en línea, quite el grupo de disponibilidad al completo.

Para forzar una conmutación por error manual con pérdida de datos de la réplica principal N1 a la secundaria N2, siga estos pasos:

En la réplica secundaria (N2), inicie una conmutación por error forzada:
```
ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
```
En la nueva réplica principal (N2), quite la principal original (N1):
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
Valide que el tráfico de todas las aplicaciones apunte al cliente de escucha o la nueva réplica principal.
Si la principal original (N1) está en línea, desconecte el grupo de disponibilidad AGRScale de la principal original (N1) de inmediato:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Si hay datos o cambios sin sincronizar, conserve dichos datos por medio de copias de seguridad u otras opciones de replicación de datos, en consonancia con los requisitos de su empresa.
Luego, quite el grupo de disponibilidad de la principal original (N1):
```
DROP AVAILABILITY GROUP [AGRScale];
```
Anule la base de datos del grupo de disponibilidad de la réplica principal original (N1):
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(Opcional) Si quiere, ahora puede volver a agregar N1 como nueva réplica secundaria al grupo de disponibilidad AGRScale.

Compartir a través de

Configuración de un grupo de disponibilidad de SQL Server para escalado de lectura en Linux

Requisitos previos

Instalación de SQL Server

Habilitación de los Grupos de disponibilidad AlwaysOn

Habilitar una sesión de eventos AlwaysOn_health

Crear un certificado

Crear el certificado en los servidores secundarios

Crear los puntos de conexión de creación de reflejo de la base de datos en todas las réplicas

Crear el grupo de disponibilidad

Unir instancias de SQL Server secundarias al grupo de disponibilidad

Agregar una base de datos al grupo de disponibilidad

Compruebe que la base de datos se crea en los servidores secundarios.

Conectar con réplicas secundarias de solo lectura

Conmutación por error de la réplica principal en un grupo de disponibilidad de escalado de lectura

Conmutación por error manual sin pérdida de datos

Conmutación por error manual forzada con pérdida de datos

Comentarios

Recursos adicionales

Compartir a través de

Configuración de un grupo de disponibilidad de SQL Server para escalado de lectura en Linux

Requisitos previos

Instalación de SQL Server

Habilitación de los Grupos de disponibilidad AlwaysOn

Habilitar una sesión de eventos AlwaysOn_health

Crear un certificado

Crear el certificado en los servidores secundarios

Crear los puntos de conexión de creación de reflejo de la base de datos en todas las réplicas

Crear el grupo de disponibilidad

Unir instancias de SQL Server secundarias al grupo de disponibilidad

Agregar una base de datos al grupo de disponibilidad

Compruebe que la base de datos se crea en los servidores secundarios.

Conectar con réplicas secundarias de solo lectura

Conmutación por error de la réplica principal en un grupo de disponibilidad de escalado de lectura

Conmutación por error manual sin pérdida de datos

Conmutación por error manual forzada con pérdida de datos

Contenido relacionado

Comentarios

Recursos adicionales