Solucionar problemas de la configuración de Grupos de Disponibilidad Always On (SQL Server)

Se aplica a:SQL Server

En este artículo se proporciona información que le ayudará a solucionar problemas típicos con la configuración de instancias de servidor para grupos de disponibilidad AlwaysOn. Algunos de los problemas de configuración más habituales son que los grupos de disponibilidad Always On están deshabilitados, las cuentas no están configuradas correctamente, el punto de conexión de reflejo de la base de datos no existe, el punto de conexión no es accesible (error 1418 de SQL Server), el acceso de red no existe, y un comando de unión de base de datos falla (error 35250 de SQL Server).

En este tema:

Los grupos de disponibilidad AlwaysOn no están habilitados

La característica Grupos de disponibilidad AlwaysOn debe estar habilitada en cada instancia de SQL Server.

Si la característica Grupos de disponibilidad Always On no está habilitada, recibirá este mensaje de error al intentar crear un grupo de disponibilidad en SQL Server.

The Always On Availability Groups feature must be enabled for server instance 'SQL1VM' before you can create an availability group on this instance. To enable this feature, open the SQL Server Configuration Manager, select SQL Server Services, right-click on the SQL Server service name, select Properties, and use the Always On Availability Groups tab of the Server Properties dialog. Enabling Always On Availability Groups may require that the server instance is hosted by a Windows Server Failover Cluster (WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

En el mensaje de error se indica claramente que dicha característica no está habilitada y también se proporcionan instrucciones sobre cómo habilitarla. Hay dos escenarios en los que se puede llegar a este estado, además del obvio en el que el grupo de disponibilidad (AG) no estaba habilitado en primer lugar.

1. Si SQL Server se instaló y la característica Grupos de Disponibilidad Always On se habilitó antes de instalar la característica Cluster de Conmutación por Error de Windows, es posible que reciba este error al intentar crear un Grupo de Disponibilidad Always On.
2. Si quita una característica existente de clústeres de conmutación por error de Windows y la vuelve a crear mientras SQL Server todavía tiene configurado Always On, al intentar usar el Grupo de Disponibilidad de nuevo, puede producirse este error.

En estos casos, puede hacer lo siguiente para resolverlo:

1. Deshabilite la función AG.
2. Reinicie el servicio de SQL Server.
3. Vuelva a habilitar la característica Grupos de disponibilidad Always On.
4. Reinicie el servicio de SQL Server de nuevo.

Para obtener más información, vea Habilitar y deshabilitar grupos de disponibilidad AlwaysOn (SQL Server).

Cuentas

Las cuentas en las que se ejecuta SQL Server deben estar configuradas correctamente.

1. ¿Tienen las cuentas los permisos adecuados?

   1. Si los socios operan bajo la misma cuenta de dominio, automáticamente existen los inicios de sesión de usuario correctos en ambas bases de datos maestras. Esto simplifica la configuración de seguridad y es recomendable.

   2. Si dos instancias de servidor se ejecutan bajo cuentas diferentes, entonces cada cuenta debe crearse en master en la instancia de servidor remoto, y al principal de servidor se le deben conceder permisos CONNECT para conectarse al punto de conexión de reflejo de la base de datos de esa instancia de servidor. Para obtener más información, consulte Configurar cuentas de acceso para reflejo de bases de datos o Always On Availability Groups (SQL Server). Puede usar la siguiente consulta en cada instancia para comprobar si los inicios de sesión tienen permisos CONNECT:

   SELECT 
     perm.class_desc,
     prin.name,
     perm.permission_name,
     perm.state_desc,
     prin.type_desc as PrincipalType,
     prin.is_disabled
   FROM sys.server_permissions perm
     LEFT JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
     LEFT JOIN sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id
   WHERE 
     perm.class_desc = 'ENDPOINT'
     AND perm.permission_name = 'CONNECT'
     AND tep.type = 4    
   

2. Si SQL Server se ejecuta en una cuenta integrada, como sistema local, servicio local o servicio de red, o una cuenta que no es de dominio, debe utilizar certificados para la autenticación de puntos de conexión. Si las cuentas de servicio utilizan cuentas de dominio en el mismo dominio, puede elegir conceder acceso CONNECT para cada cuenta de servicio en todas las ubicaciones de réplica o puede utilizar certificados. Para obtener más información, consulte Usar certificados como un extremo de reflectación de bases de datos (Transact-SQL).

Puntos de conexión

Los extremos deben estar configurados correctamente.

1. Asegúrese de que cada instancia de SQL Server que va a hospedar una réplica de disponibilidad (cada ubicación de réplica) tenga un punto de conexión de reflejo de base de datos. Para determinar si hay un punto de conexión de reflejo de base de datos existente en una instancia de servidor dada, use la vista de catálogo sys.database_mirroring_endpoints:

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   Para obtener más información sobre cómo crear puntos de conexión, vea Crear un punto de conexión de creación de reflejo de la base de datos para la autenticación de Windows (Transact-SQL) o Permitir que un punto de conexión de creación de reflejo de la base de datos utilice certificados para las conexiones salientes (Transact-SQL&).

2. Compruebe que los números de puerto son correctos.

   Para identificar el puerto asociado actualmente al punto de conexión de reflejo de la base de datos de una instancia de servidor, utilice la siguiente instrucción Transact-SQL:

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. En caso de problemas de implementación de los grupos de disponibilidad Always On que son difíciles de diagnosticar, se recomienda que inspeccione cada instancia de servidor para determinar si están escuchando en los puertos correctos.

4. Asegúrese de que se han iniciado los puntos finales (STATE=STARTED). En cada una de las instancias de servidor, utilice la siguiente instrucción Transact-SQL.

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   Para obtener más información sobre la columna state_desc, consulte sys.database_mirroring_endpoints (Transact-SQL).

   Para iniciar un punto de conexión, utilice la siguiente instrucción Transact-SQL:

   ALTER ENDPOINT Endpoint_Mirroring   
   STATE = STARTED   
   AS TCP (LISTENER_PORT = <port_number>)  
   FOR database_mirroring (ROLE = ALL);  
   GO  
   

   Para obtener más información, vea ALTER ENDPOINT (Transact-SQL).

   Nota

   En algunos casos, si se inicia el punto de conexión pero no se comunican las réplicas de los Grupos de Disponibilidad de AlwaysOn, pruebe a detener y reiniciar el punto de conexión. Puede usar ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED seguido de ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED

5. Asegúrese de que el acceso desde el otro servidor dispone de permiso CONNECT. Para determinar quién tiene permiso CONNECT para un punto de conexión, utilice la siguiente instrucción Transact-SQL en cada instancia de servidor

   SELECT 'Metadata Check';  
   SELECT EP.name, SP.STATE,   
      CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
         AS GRANTOR,   
      SP.TYPE AS PERMISSION,  
      CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
         AS GRANTEE   
      FROM sys.server_permissions SP , sys.endpoints EP  
      WHERE SP.major_id = EP.endpoint_id  
      ORDER BY Permission,grantor, grantee;   
   

6. Asegúrese de que se usa el nombre de servidor correcto en la dirección URL del punto de conexión.

   Para el nombre del servidor en una dirección URL de punto de conexión, se recomienda usar el nombre de dominio completo (FQDN), aunque puede usar cualquier nombre que identifique de forma única la máquina. La dirección del servidor puede ser un nombre de NetBIOS (si los sistemas se encuentran en el mismo dominio), un nombre de dominio completo (FQDN) o una dirección IP (preferiblemente, una dirección IP estática). La opción recomendada es usar el nombre de dominio completamente calificado.

   Si ya ha definido una dirección URL del punto de conexión, puede consultarla mediante:

   select endpoint_url from sys.availability_replicas
   

   A continuación, compare la salida endpoint_url con el nombre del servidor (nombre de NetBIOS o FQDN). Para consultar el nombre del servidor, ejecute los siguientes comandos en una instancia de PowerShell en la réplica localmente:

   $env:COMPUTERNAME
   [System.Net.Dns]::GetHostEntry([string]$env:computername).HostName
   

   Para validar el nombre del servidor en un equipo remoto, ejecute este comando desde PowerShell.

   $servername_from_endpoint_url = "server_from_endpoint_url_output"
   
   Test-NetConnection -ComputerName $servername_from_endpoint_url
   

   Para obtener más información, vea Especificar la dirección URL del punto de conexión - Agregar o modificar una réplica de disponibilidad (SQL Server).

Acceso de red

Cada instancia de servidor que hospeda una réplica de disponibilidad debe poder acceder al puerto de cada una de las demás instancias de servidor a través de TCP. Esto es especialmente importante si las instancias de servidor están en distintos dominios que no confían unos en otros (dominios que no son de confianza). Siga estos pasos para comprobar si puede conectarse a los puntos de conexión:

• Use Test-NetConnection (equivalente a Telnet) para validar la conectividad. Estos son algunos ejemplos de comandos que puede usar:

  $server_name = "your_server_name"
  $IP_address = "your_ip_address"
  $port_number = "your_port_number"
  
  Test-NetConnection -ComputerName $server_name -Port $port_number
  Test-NetConnection -ComputerName $IP_address -Port $port_number
  

• Si el punto de conexión escucha y la conexión se realiza correctamente, verá "TcpTestSucceeded : True". Si no es así, recibirá un valor "TcpTestSucceeded : False".

• Si la conexión de Test-NetConnection (Telnet) a la dirección IP funciona, pero no con el ServerName, probablemente hay un problema de resolución de nombres o DNS.

• Si la conexión funciona con ServerName y no por dirección IP, podría haber más de un punto de conexión definido en ese servidor (quizás otra instancia de SQL) que escucha en ese puerto. Aunque el estado del punto de conexión en la instancia en cuestión muestra "STARTED", otra instancia podría tener realmente enlazado el puerto y evitar que la instancia correcta escuche y establezca conexiones TCP.

• Si Test-NetConnection no se puede conectar, busque un firewall y/o software antivirus que podría estar bloqueando el puerto del punto de conexión en cuestión. Compruebe la configuración de firewall para ver si permite la comunicación de puerto del punto de conexión entre las instancias de servidor que hospedan la réplica principal y la secundaria (el puerto 5022 de forma predeterminada). Ejecute el siguiente script de PowerShell para examinar las reglas de tráfico de entrada deshabilitadas.

• Si ejecuta SQL Server en una máquina virtual de Azure, también debe asegurarse de que el grupo de seguridad de red (NSG) permite el tráfico al puerto de punto de conexión. Compruebe la configuración del firewall (y del grupo de seguridad de red, para la máquina virtual de Azure) para ver si permite la comunicación por puertos del punto final entre las instancias de servidor que hospedan la réplica principal y la réplica secundaria (por defecto el puerto 5022).

  Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
  

• Captura la salida del cmdlet Get-NetTCPConnection (equivalente a NETSTAT -a) y compruebe que el estado es LISTENING o ESTABLISHED en el IP:Puerto para el punto de conexión especificado.

  Get-NetTCPConnection 
  

Escuchador

Para una configuración correcta de un agente de escucha de grupo de disponibilidad, siga "Configurar un agente de escucha para un grupo de disponibilidad AlwaysOn".

1. Una vez configurado el agente de escucha, puede validar la dirección IP y el puerto en el que escucha mediante la consulta siguiente:

   $server_name = $env:computername  #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S$server_name -Q"SELECT dns_name AS AG_listener_name, port, ip_configuration_string_from_cluster 
   FROM sys.availability_group_listeners"
   

2. También puede encontrar la información del cliente de escucha junto con los puertos de SQL Server mediante esta consulta:

   $server_name = $env:computername      #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S($server_name) -Q("SELECT  convert(varchar(32), SERVERPROPERTY ('servername')) servername, convert(varchar(32),ip_address) ip_address, port, type_desc,state_desc, start_time 
   FROM sys.dm_tcp_listener_states 
   WHERE ip_address not in ('127.0.0.1', '::1') and type <> 2")
   

3. Si necesita establecer conectividad con el cliente de escucha y sospecha que un puerto está bloqueado, puede realizar una prueba mediante el cmdlet Test-NetConnection de PowerShell (equivalente a telnet).

   $listener_name = "your_ag_listener"
   $IP_address = "your_ip_address"
   $port_number = "your_port_number"
   
   Test-NetConnection -ComputerName $listener_name -Port $port_number
   Test-NetConnection -ComputerName $IP_address -Port $port_number
   

4. Por último, compruebe si el cliente de escucha está escuchando en el puerto especificado:

   $port_number = "your_port_number"
   
   Get-NetTCPConnection -LocalPort $port_number -State Listen
   

Acceso al punto final (error 1418 de SQL Server)

Este mensaje de SQL Server indica que la dirección de red del servidor especificada en la dirección URL del punto de conexión no se encuentra o no existe, y recomienda que compruebe el nombre de la dirección de red y vuelva a emitir el comando.

Error de unión de la base de datos (error 35250 de SQL Server)

En esta sección se analiza las posibles causas y la resolución de un error al unir las bases de datos secundarias al grupo de disponibilidad porque la conexión a la réplica principal no está activa. Este es el mensaje de error completo:

Msg 35250 The connection to the primary replica is not active. The command cannot be processed.

Resolución:

A continuación se muestra un resumen de los pasos.

Para obtener instrucciones detalladas y paso a paso, vea el error de motor MSSQLSERVER_35250.

1. Asegúrese de que el punto de conexión esté creado y comenzado.
2. Compruebe si puede conectarse al punto de conexión a través de Telnet y descarte que haya reglas de firewall que estén bloqueando la conectividad.
3. Compruebe si hay errores en el sistema. Puede consultar la vista sys.dm_hadr_availability_replica_states para obtener el número de error de conexión, que puede ayudarle a diagnosticar el problema de unión.
4. Asegúrese de que el punto de conexión está definido de modo que coincida correctamente con la IP/puerto que AG está utilizando.
5. Compruebe si la cuenta de servicio de red tiene permiso de conexión al punto de conexión.
6. Compruebe los posibles problemas de resolución de nombres.
7. Asegúrese de que SQL Server está ejecutando una compilación reciente (preferiblemente la más reciente) para protegerse de problemas.

el enrutamiento de Read-Only no funciona correctamente

1. Asegúrese de haber configurado el enrutamiento de solo lectura según el documento Configuración del enrutamiento de solo lectura.

2. Asegúrese de que los controladores de cliente son compatibles.

   La aplicación cliente debe usar un proveedor de cliente que admita ApplicationIntent el parámetro . Vea Compatibilidad con la conectividad de cliente y controlador para grupos de disponibilidad.

   Nota

   Si te conectas a un Listener de nombre de red distribuida (DNN), el proveedor también debe admitir el parámetro MultiSubnetFailover.

3. Asegúrese de que las propiedades de la cadena de conexión se han establecido correctamente.

   Para que el enrutamiento de solo lectura funcione correctamente, la aplicación cliente debe usar estas propiedades en la cadena de conexión:

   • Un nombre de base de datos que pertenece al grupo de disponibilidad (AG).
   • El nombre del oyente del grupo de disponibilidad
     • Si usa DNN, debe especificar el nombre del oyente de DNN y el número de puerto de DNN <DNN name,DNN port>.
   • ApplicationIntent establecido en ReadOnly.
   • El nombre de red distribuido (DNN) requiere configurar MultiSubnetFailover en 'true'.

   Ejemplos

   En este ejemplo se muestra la cadena de conexión para el proveedor Microsoft.Data.SqlClient o System.Data.SqlClient de .NET para un agente de escucha de nombre de red virtual (VNN):

   Server=tcp:VNN_AgListener,1433;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   En este ejemplo se muestra la cadena de conexión para el proveedor .NET Microsoft.Data.SqlClient o System.Data.SqlClient de un listener de nombre de red distribuido (DNN):

   Server=tcp:DNN_AgListener,DNN_Port;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   Nota

   Si está usando programas de línea de comandos como SQLCMD, asegúrese de especificar las opciones correctas de nombre del servidor. Por ejemplo, en SQLCMD debe usar el modificador de mayúsculas -S que especifica el nombre del servidor, no el modificador en minúsculas -s que se usa para el separador de columnas.
   Ejemplo: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. Asegúrese de que el escuchador del grupo de disponibilidad esté en línea. Para asegurarse de que el agente de escucha del grupo de disponibilidad está en línea, ejecute la siguiente consulta en la réplica principal:

   SELECT * FROM sys.dm_tcp_listener_states;
   

   Si descubre que el listener está offline, puede intentar traerlo en línea mediante un comando como el siguiente:

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. Asegúrese de que READ_ONLY_ROUTING_LIST se ha rellenado correctamente. En la réplica principal, asegúrese de que READ_ONLY_ROUTING_LIST solo contiene instancias de servidor que hospedan réplicas secundarias legibles.

   Para ver las propiedades de cada réplica, puede ejecutar esta consulta y examinar el punto de conexión de conectividad (URL) de la réplica de solo lectura.

   SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url 
   FROM sys.availability_replicas;   
   

   Para ver una lista de enrutamiento de solo lectura y compararla con la dirección URL del punto de conexión:

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   Para cambiar una lista de enrutamiento de solo lectura, puede usar una consulta como esta:

   ALTER AVAILABILITY GROUP [AG1]   
   MODIFY REPLICA ON  
   N'COMPUTER02' WITH   
   (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
   

   Para obtener más información, consulte Configuración del enrutamiento de solo lectura para un grupo de disponibilidad: SQL Server AlwaysOn

6. Compruebe que el puerto READ_ONLY_ROUTING_URL está abierto. Asegúrese de que el Firewall de Windows no bloquea el puerto READ_ONLY_ROUTING_URL. Configure un Firewall de Windows para el acceso al motor de base de datos en todas las réplicas de la read_only_routing_list y en cualquier cliente que se conecte a esas réplicas.

   Nota

   Si ejecuta SQL Server en una máquina virtual de Azure, debe realizar pasos de configuración adicionales. Asegúrese de que el grupo de seguridad de red (NSG) de cada máquina virtual de réplica permita el tráfico hacia el puerto del punto final y el puerto DNN si está utilizando un listener DNN. Si usa el agente de escucha de VNN, debe asegurarse de que el equilibrador de carga está configurado correctamente.

7. Asegúrese de que READ_ONLY_ROUTING_URL (TCP://system-address:port) contiene el nombre de dominio completo (FQDN) y el número de puerto correctos. Vea:

   • Calculando la URL de enrutamiento de solo lectura para Always On
   • sys.availability_replicas (Transact-SQL)
   • ALTERAR GRUPO DE DISPONIBILIDAD (Transact-SQL)

8. Asegúrese de que la configuración de red de SQL Server es la adecuada en SQL Server Configuration Manager.

   Compruebe en todas las réplicas de read_only_routing_list lo siguiente:

   • La conectividad remota de SQL Server está habilitada.
   • TCP/IP está habilitado.
   • Las direcciones IP están configuradas correctamente.

   Nota

   Puede comprobar rápidamente que todos estos ajustes están configurados correctamente si puede conectarse desde un equipo remoto al nombre de instancia de SQL Server de una réplica secundaria de destino mediante la sintaxis TCP:SQL_Instance.

Vea Configurar un servidor para que escuche en un puerto TCP específico (Administrador de configuración de SQL Server) y Ver o cambiar las propiedades del servidor (SQL Server).

Tareas relacionadas

• Creación y configuración de grupos de disponibilidad (SQL Server)

• Crear un extremo de reflejo de la base de datos para la autenticación de Windows (Transact-SQL)

• Especificar la dirección URL del extremo al agregar o modificar una réplica de disponibilidad (SQL Server)

• Preparar manualmente una base de datos secundaria para un grupo de disponibilidad (SQL Server)

• Solucionar problemas relativos a una operación de agregar archivos con error (grupos de disponibilidad AlwaysOn)

• Gestión de inicios de sesión y trabajos para las bases de datos de un grupo de disponibilidad (SQL Server)

• Administrar los metadatos cuando una base de datos pasa a estar disponible en otra instancia de servidor (SQL Server)

Contenido relacionado

• Visualización de eventos y registros de un clúster de conmutación por error
• Cmdlet de Get-ClusterLog del clúster de conmutación por error
• Blog del equipo Always On de SQL Server: el blog oficial del equipo de Always On de SQL Server
• Seguridad de transporte para el reflejo de la base de datos y los grupos de disponibilidad Always On (SQL Server)
• Configuración de red de cliente
• Requisitos previos, restricciones y recomendaciones para Grupos de disponibilidad AlwaysOn (SQL Server)