Configurar el enrutamiento de solo lectura en un grupo de disponibilidad Always On en SQL Server on AWS

Creado por Subhani Shaik () AWS

Entorno: PoC o piloto	Tecnologías: bases de datos; infraestructura	Carga de trabajo: Microsoft
AWSservicios: Microsoft AD AWS gestionado; Amazon EC2

Resumen

Este patrón explica cómo utilizar la réplica secundaria en espera en SQL Server Always On descargando las cargas de trabajo de solo lectura de la réplica principal a la réplica secundaria.

La duplicación de bases de datos tiene mapeo. one-to-one No puede leer la base de datos secundaria directamente, por lo que debe crear instantáneas. La función de grupo de disponibilidad Always On se introdujo en Microsoft SQL Server 2012. En versiones posteriores se han introducido importantes funcionalidades, incluido el enrutamiento de solo lectura. En los grupos de disponibilidad de Always On, puede leer los datos directamente desde la réplica secundaria cambiando el modo de réplica a uno de solo lectura.

La solución de grupos de disponibilidad Always On ofrece alta disponibilidad (HA), recuperación de desastres (DR) y una alternativa a la duplicación de bases de datos. Los grupos de disponibilidad Always On funcionan a nivel de base de datos, y maximizan la disponibilidad de un conjunto de bases de datos de usuarios.

SQLEl servidor utiliza el mecanismo de enrutamiento de solo lectura para redirigir las conexiones entrantes de solo lectura a la réplica de lectura secundaria. Para ello, debe agregar los siguientes parámetros y valores en la cadena de conexión:

• ApplicationIntent=ReadOnly

• Initial Catalog=<database name>

Requisitos previos y limitaciones

Requisitos previos 

• Una AWS cuenta activa con una nube privada virtual (VPC), dos zonas de disponibilidad, subredes privadas y un grupo de seguridad

• Dos máquinas Amazon Elastic Compute Cloud (AmazonEC2) con SQLservidor 2019 Enterprise Edition Amazon Machine Image con clústeres de conmutación por error de Windows Server (WSFC) configurados a nivel de instancia y un grupo de disponibilidad Always On configurado a nivel de SQL servidor entre el nodo principal (WSFCNODE1) y el nodo secundario (WSFCNODE2), que forman parte del AWS directorio Directory Service for Microsoft Active Directory denominado tagechtalk.com

• Uno o más nodos configurados para aceptar read-only en la réplica secundaria

• Un oyente con el nombre SQLAG1 para el grupo de disponibilidad Always On

• SQLEl motor de base de datos del servidor se ejecuta con la misma cuenta de servicio en dos nodos

• SQLEstudio de administración de servidores (SSMS)

• Una base de datos de prueba llamada test

Versiones de producto

• SQLServer 2014 y versiones posteriores

Arquitectura

Pila de tecnología de destino

• Amazon EC2

• AWS Managed Microsoft AD

• Amazon FSx

Arquitectura de destino

El siguiente diagrama muestra cómo el oyente del grupo de disponibilidad (AG) Always On redirige las consultas que contienen el parámetro ApplicationIntent en la conexión al nodo secundario correspondiente.

1. Se envía una solicitud al oyente del grupo de disponibilidad Always On.

2. Si la cadena de conexión no tiene el parámetro ApplicationIntent, la solicitud se envía a la instancia principal.

3. Si la cadena de conexión lo contieneApplicationIntent=ReadOnly, la solicitud se envía a la instancia secundaria con una configuración de enrutamiento de solo lectura, que tiene un grupo de WSFC disponibilidad Always On.

Herramientas

AWSservicios

• AWSDirectory Service para Microsoft Active Directory permite que sus cargas de trabajo y AWS recursos compatibles con directorios utilicen Microsoft Active Directory en la nube. AWS

• Amazon Elastic Compute Cloud (AmazonEC2) proporciona capacidad informática escalable en la AWS nube. Puede lanzar tantos servidores virtuales como necesite y escalarlos o reducirlos con rapidez.

• Amazon FSx proporciona sistemas de archivos que admiten los protocolos de conectividad estándares del sector y ofrecen alta disponibilidad y replicación en todas AWS las regiones.

Otros servicios

• SQLServer Management Studio (SSMS) es una herramienta para conectar, gestionar y administrar las instancias del SQL servidor.

• sqlcmd es una utilidad de línea de comandos.

Prácticas recomendadas

Para obtener más información sobre los grupos de disponibilidad de Always On, consulte la documentación SQL del servidor.

Epics

Configure el enrutamiento de solo lectura

Tarea	Descripción	Habilidades requeridas
Actualice las réplicas a solo lectura.	Para actualizar la réplica principal y la secundaria a una de solo lectura, conéctese a la réplica principal desde SSMS la sección de información adicional y ejecute el código del paso 1 de la sección de información adicional.	DBA
Cree el enrutamiento. URL	Para crear el enrutamiento URL para ambas réplicas, ejecute el código del paso 2 de la sección de información adicional. En este código, tagechtalk.com es el nombre del directorio de Microsoft AD AWS administrado.	DBA
Cree la lista de enrutamiento.	Para crear la lista de enrutamiento para ambas réplicas, ejecute el código del Paso 3 que encontrará en la sección de Información adicional.	DBA
Valide la lista de enrutamiento.	Conéctese a la instancia principal desde SQL Server Management Studio y ejecute el código del paso 4 de la sección Información adicional para validar la lista de enrutamiento.	DBA

Pruebe el enrutamiento de solo lectura

Tarea	Descripción	Habilidades requeridas
Conéctese mediante el ApplicationIntent parámetro.	DesdeSSMS, conéctese al grupo de disponibilidad Always On con ApplicationIntent=ReadOnly;Initial Catalog=test el nombre del agente de escucha. Se establecerá una conexión con la réplica secundaria. Para realizar la prueba, ejecute el siguiente comando para mostrar el nombre del servidor conectado. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') El resultado devolverá el nombre de la réplica secundaria actual (WSFCNODE2).	DBA
Realice una conmutación por error.	DesdeSSMS, conéctese al nombre del agente de escucha del grupo de disponibilidad de Always On. Compruebe que las bases de datos principal y secundaria estén sincronizadas y que no se pierdan datos. Realice una conmutación por error para que la réplica principal actual pase a ser la réplica secundaria, y la réplica secundaria pase a ser la réplica principal. DesdeSSMS, conéctese al nombre del agente de escucha del grupo de disponibilidad de Always On con. ApplicationIntent=ReadOnly;Initial Catalog=test Se establecerá una conexión con la réplica secundaria. Para realizar la prueba, ejecute el siguiente comando para mostrar el nombre del servidor conectado. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') Se mostrará el nombre de la réplica secundaria actual (WSFCNODE1).	DBA

Conéctese mediante la utilidad de línea de comandos sqlcmd

Tarea	Descripción	Habilidades requeridas
Conéctese mediante sqlcmd.	Para conectarse desde sqlcmd, ejecute el código del Paso 5 que encontrará en la sección de Información adicional en la línea de comandos. Después de conectarse, ejecute el siguiente comando para mostrar el nombre del servidor conectado. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') . El resultado devolverá el nombre de la réplica secundaria actual (WSFCNODE1).	DBA

Resolución de problemas

Problema	Solución
Se produce un error al crear el listener y aparece el mensaje «El WSFC clúster no pudo poner en línea el recurso Network Name».	Para obtener más información, consulte la entrada del blog de Microsoft Create Listener falla con el mensaje «El WSFC clúster no pudo poner en línea el recurso Network Name».
Posibles problemas, incluidos otros problemas con los oyentes o problemas de acceso a la red.	Consulte Solución de problemas de configuración de grupos de disponibilidad siempre activos (SQLservidor) en la documentación de Microsoft.

Recursos relacionados

• Configure el enrutamiento de solo lectura en un grupo de disponibilidad Always On

• Solucione los problemas de configuración de los grupos de disponibilidad Always On (servidor) SQL

Información adicional

Paso 1. Actualice las réplicas a solo lectura

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO

Paso 2. Cree el enrutamiento URL

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode1.tagechtalk.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode2.tagechtalk.com:1433'))
GO

Paso 3. Cree la URL de enrutamiento

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('WSFCNODE2','WSFCNODE1')));
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WSFCNODE1','WSFCNODE2')));
GO

Paso 4. Cree la lista de enrutamiento

SELECT AGSrc.replica_server_name AS PrimaryReplica, AGRepl.replica_server_name AS ReadOnlyReplica, AGRepl.read_only_routing_url AS RoutingURL , AGRL.routing_priority AS RoutingPriority FROM sys.availability_read_only_routing_lists AGRL INNER JOIN sys.availability_replicas AGSrc ON AGRL.replica_id = AGSrc.replica_id INNER JOIN sys.availability_replicas AGRepl ON AGRL.read_only_replica_id = AGRepl.replica_id INNER JOIN sys.availability_groups AV ON AV.group_id = AGSrc.group_id ORDER BY PrimaryReplica

Paso 5. SQLUtilidad de comandos

sqlcmd -S SQLAG1,1433 -E -d test -K ReadOnly