Configuración de escalado de lectura para un grupo de disponibilidad Always On

Artículo
04/26/2023

Se aplica a:SQL Server

Se puede configurar un grupo de disponibilidad AlwaysOn de SQL Server para las cargas de trabajo de escalado de lectura en Windows. Hay dos tipos de arquitectura para los grupos de disponibilidad:

Una arquitectura de alta disponibilidad en la que se usa un administrador de clústeres para proporcionar una continuidad empresarial mejorada y que puede incluir réplicas secundarias legibles. Para crear esta arquitectura de alta disponibilidad, vea Creación y configuración de grupos de disponibilidad (SQL Server).
Una arquitectura en la que solo se admiten cargas de trabajo de escalado de lectura.

En este artículo se explica cómo crear un grupo de disponibilidad sin un administrador de clústeres para las cargas de trabajo de escalado de lectura. Esta arquitectura solo proporciona escalado de lectura. No proporciona alta disponibilidad.

Nota:

Un grupo de disponibilidad con CLUSTER_TYPE = NONE puede incluir réplicas hospedadas en varias plataformas de sistema operativo. No puede admitir la alta disponibilidad. Para el sistema operativo Linux vea Configurar un grupo de disponibilidad de SQL Server para la escala de lectura en Linux.

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. Vea Instalar SQL Server para obtener más información.

Habilitar los grupos de disponibilidad AlwaysOn y reiniciar mssql-server

Nota:

En el comando siguiente se usan los cmdlets del módulo sqlserver que se publica en la Galería de PowerShell. Puede instalar este módulo mediante el comando Install-Module.

Habilite los grupos de disponibilidad AlwaysOn en todas las réplicas en las que se hospede una instancia de SQL Server. Después, reinicie el servicio SQL Server. Ejecute el comando siguiente para habilitar y reiniciar los servicios de SQL Server:

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Habilitar una sesión de eventos AlwaysOn_health

Para ayudar con el diagnóstico de la causa raíz cuando solucione los problemas de un grupo de disponibilidad, opcionalmente puede habilitar una sesión de eventos extendidos (XEvents) de los grupos de disponibilidad AlwaysOn. Para hacerlo, 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 XEvents, vea Eventos extendidos de grupos de disponibilidad AlwaysOn.

Autenticación de puntos de conexión de creación de reflejo de la base de datos

Para que la sincronización funcione correctamente, las réplicas que participan en el grupo de disponibilidad de escalado de lectura se deben autenticar a través del punto de conexión. En las secciones siguientes se describen los dos escenarios principales que se pueden usar para este tipo de autenticación.

Cuenta de servicio

En un entorno de Active Directory donde todas las réplicas secundarias están unidas al mismo dominio, SQL Server se puede autenticar mediante la cuenta de servicio. Tendrá que crear de forma explícita un inicio de sesión para la cuenta de servicio en todas las instancias de SQL Server:

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

En los entornos donde es posible que las réplicas secundarias no estén unidas a un dominio de Active Directory, tendrá que usar la autenticación de SQL. El script de Transact-SQL siguiente crea un inicio de sesión denominado dbm_login y un usuario denominado dbm_user. Actualice el script con una contraseña segura. Para crear el usuario de punto de conexión de creación de reflejo de la base de datos, ejecute el comando siguiente en todas las instancias de SQL Server:

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Autenticación de certificado

Si se usa una réplica secundaria que requiere la autenticación con Autenticación de SQL, use un certificado para la autenticació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. Ejecute el script en la instancia principal de SQL Server para crear el certificado:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\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 c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer y una clave privada en c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\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.

En cada réplica secundaria, asegúrese de que la cuenta de servicio para la instancia de SQL Server tenga permisos para acceder al certificado.

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. El comando también autoriza al usuario a acceder al certificado. Actualice el script con contraseñas seguras. La contraseña de descifrado es la misma que se usó para crear el archivo .pvk en un paso anterior. Para crear el certificado, ejecute el script siguiente en todas las réplicas secundarias:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

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

Los puntos de conexión 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 punto de conexión 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. Inicia el punto de conexión y concede permiso de conexión a la cuenta de servicio o al inicio de sesión de SQL que se creó en el paso anterior. 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;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

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

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

Creación de un grupo de disponibilidad

Cree un grupo de disponibilidad. Establezca CLUSTER_TYPE = NONE. Además, establezca cada réplica con FAILOVER_MODE = NONE. 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.

En el siguiente script de Transact-SQL se 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;

Una las 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. Para unir al grupo de disponibilidad, ejecute el script de Transact-SQL siguiente en cada réplica secundaria de SQL Server:

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. Para crear una base de datos denominada db1 y realizar una copia de seguridad de ella, ejecute el script de Transact-SQL siguiente en la instancia principal de SQL Server:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

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

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

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

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

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;

Este grupo de disponibilidad no es una configuración de alta disponibilidad. Si necesita alta disponibilidad, siga las instrucciones descritas en Configure an Always On Availability Group for SQL Server on Linux (Configuración de un grupo de disponibilidad AlwaysOn para SQL Server en Linux) o Creación y configuración de grupos de disponibilidad en Windows.

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. Para obtener más información, vea Réplicas secundarias legibles.
Las aplicaciones también pueden usar el enrutamiento de solo lectura, lo que requiere un agente de escucha. Si va a implementar un escenario de escalado de lectura sin un administrador de clústeres, puede crear un cliente de escucha que apunte a la dirección IP de la réplica principal actual y al mismo puerto que escucha SQL Server. Tendrá que volver a crear el cliente de escucha para que apunte a la nueva dirección IP principal después de una conmutación por error. Para obtener más información, vea Enrutamiento de solo lectura.

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.

Tenga en cuenta que, si usa un agente de escucha para conectarse, deberá volver a crearlo después de realizar la conmutación por error.

Share via

Configuración de escalado de lectura para un grupo de disponibilidad Always On

Requisitos previos

Habilitar los grupos de disponibilidad AlwaysOn y reiniciar mssql-server

Habilitar una sesión de eventos AlwaysOn_health

Autenticación de puntos de conexión de creación de reflejo de la base de datos

Cuenta de servicio

Autenticación de certificado

Crear el certificado en los servidores secundarios

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

Creación de un grupo de disponibilidad

Una las 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

Pasos siguientes

Comentarios

Comentarios

Recursos adicionales

Share via

Configuración de escalado de lectura para un grupo de disponibilidad Always On

Requisitos previos

Habilitar los grupos de disponibilidad AlwaysOn y reiniciar mssql-server

Habilitar una sesión de eventos AlwaysOn_health

Autenticación de puntos de conexión de creación de reflejo de la base de datos

Cuenta de servicio

Autenticación del inicio de sesión SQL

Autenticación de certificado

Crear el certificado en los servidores secundarios

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

Creación de un grupo de disponibilidad

Una las 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

Pasos siguientes

Comentarios

Comentarios

Recursos adicionales