Solucionar problemas de configuração de grupos de disponibilidade AlwaysOn (SQL Server)

Aplica-se a: SQL Server

Este tópico fornece informações para ajudar a solucionar problemas típicos ao configurar instâncias de servidor para o Grupos de disponibilidade AlwaysOn. Os problemas de configuração típicos incluem grupos de disponibilidade Always On desabilitados, contas configuradas incorretamente, ponto de extremidade de espelhamento de banco de dados inexistente, ponto de extremidade inacessível (Erro 1418 do SQL Server), acesso à rede inexistente e falha no comando de junção de banco de dados (Erro 35250 do SQL Server).

Neste tópico:

Os grupos de disponibilidade AlwaysOn não estão habilitados

O recurso do Grupos de disponibilidade AlwaysOn deve estar habilitado em cada uma das instâncias do SQL Server.

Se o recurso de Grupos de Disponibilidade Always On não estiver habilitado, você receberá esta mensagem de erro ao tentar criar um grupo de disponibilidade no 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)

A mensagem de erro indica claramente que o recurso AG não está habilitado e também orienta como habilitá-lo. Há dois cenários em que você pode entrar nesse estado, além do óbvio, em que o AG não foi habilitado em primeiro lugar.

1. Se o SQL Server foi instalado e o recurso Grupos de Disponibilidade Always On foi habilitado antes de ser instalado o recurso de Clustering de Failover do Windows, você poderá receber esse erro ao tentar criar um AG no Always On.
2. Se você remover um recurso de Clustering de Failover do Windows existente e recompilá-lo enquanto o SQL Server ainda tiver Always On configurado, quando você tentar usar o AG novamente, esse erro poderá ocorrer.

Nesses casos, você pode executar as seguintes etapas para resolver esse problema:

1. Desabilitar o recurso ag
2. Reiniciar o serviço do SQL Server
3. Habilitar o recurso AG de volta
4. Reiniciar o serviço SQL novamente

Para obter mais informações, confira Habilitar e desabilitar Grupos de Disponibilidade Always On (SQL Server).

Contas

As contas nas quais o SQL Server está sendo executado devem ser configuradas corretamente.

1. As contas têm as permissões corretas?

   1. Se os parceiros forem executados na mesma conta de domínio, os logons de usuário corretos existirão automaticamente em ambos os bancos de dados mestre. Além de recomendável, isso simplifica a configuração de segurança.

   2. Se duas instâncias do servidor executadas em contas diferentes, então cada conta deverá ser criada no banco de dados mestre na instância do servidor remoto, e esse servidor de entidade de segurança deverá receber permissões CONNECT para se conectar ao ponto de extremidade de espelhamento do banco de dados dessa instância do servidor. Para obter mais informações, confira Configurar contas de logon para espelhamento de banco de dados ou para grupos de disponibilidade Always On (SQL Server). Você pode usar a seguinte consulta em cada instância para verificar se os logons têm permissões 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. Se SQL Server estiver sendo executado em uma conta interna, como Sistema Local, Serviço Local ou Serviço de Rede, ou uma conta que não pertença ao domínio, você deverá usar certificados para autenticação de ponto de extremidade. Se suas contas de serviço estiverem usando contas de domínio no mesmo domínio, você poderá escolher conceder acesso de CONNECT a cada conta de serviço em todos os locais de réplica ou usar certificados. Para obter mais informações, confira Usar certificados para um ponto de extremidade de espelhamento de banco de dados (Transact-SQL).

Pontos de extremidade

Os pontos de extremidade devem ser configurados corretamente.

1. Verifique se cada instância do SQL Server que vai hospedar uma réplica de disponibilidade (cada local de réplica) tem um ponto de extremidade de espelhamento de banco de dados. Para determinar se existe um ponto de extremidade de espelhamento do banco de dados em determinada instância do servidor, use a exibição de catálogo sys.database_mirroring_endpoints:

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   Para obter mais informações sobre como criar pontos de extremidade, confira Criar um ponto de extremidade de espelhamento de banco de dados para a Autenticação do Windows (Transact-SQL) ou Permitir que um ponto de extremidade de espelhamento de banco de dados use certificados para conexões de saída (Transact-SQL).

2. Verifique se os números da porta estão corretos.

   Para identificar a porta associada atualmente com o ponto de extremidade de espelhamento de banco de dados de uma instância de servidor, use a seguinte instrução Transact-SQL:

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. Para problemas da configuração dos grupos de disponibilidade Always On que forem difíceis de explicar, recomendamos que você inspecione cada instância do servidor para determinar se ela está escutando nas portas corretas.

4. Verifique se os pontos de extremidade foram iniciados (STATE=STARTED). Em cada instância do servidor, use a seguinte instrução Transact-SQL:

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   Para obter mais informações sobre a coluna state_desc, veja sys.database_mirroring_endpoints (Transact-SQL).

   Para iniciar um ponto de extremidade, use a seguinte instrução TransactSQL:

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

   Para obter mais informações, confira ALTER ENDPOINT (Transact-SQL).

   Observação

   Em alguns casos, se o ponto de extremidade for iniciado, mas as réplicas de AG não estiverem se comunicando, você poderá tentar parar e reiniciar o ponto de extremidade. Você pode usar ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED seguido por ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED

5. Verifique se o logon do outro servidor tem permissão CONNECT. Para determinar quem tem permissão CONNECT para um ponto de extremidade, em cada instância do servidor use a seguinte instrução Transact-SQL:

   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. Verifique se o nome correto do servidor é usado na URL do ponto de extremidade

   No caso do nome do servidor em uma URL de ponto de extremidade, é recomendável usar o FQDN (nome de domínio totalmente qualificado), embora você possa usar qualquer nome que identifique exclusivamente o computador. O endereço de servidor pode ser um nome Netbios (se os sistemas estiverem no mesmo domínio), um FQDN (nome de domínio totalmente qualificado) ou um endereço IP (preferivelmente, um endereço IP estático). Usar o nome de domínio totalmente qualificado é a opção recomendada.

   Se você já tiver definido uma URL de ponto de extremidade, poderá consultá-la usando:

   select endpoint_url from sys.availability_replicas
   

   Em seguida, compare a saída endpoint_url com o nome do servidor (nome do NetBIOS ou FQDN). Para consultar o nome do servidor, execute os seguintes comandos em um PowerShell na réplica localmente:

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

   Para validar o nome do servidor em um computador remoto, execute este comando no PowerShell.

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

   Para obter mais informações, confira Especificar a URL do Ponto de Extremidade ao Adicionar ou Modificar uma Réplica de Disponibilidade (SQL Server).

Acesso de rede

Cada instância do servidor que esteja hospedando uma réplica de disponibilidade deve poder acessar a porta de cada uma das instâncias do servidor sobre TCP. Isso será especialmente importante se as instâncias do servidor estiverem em domínios diferentes que não confiam um no outro (domínios não confiáveis). Verifique se você pode se conectar aos pontos de extremidade seguindo estas etapas:

• Use Test-NetConnection (equivalente a Telnet) para validar a conectividade. Aqui estão alguns exemplos de comandos que você pode 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
  

• Se o ponto de extremidade estiver ouvindo e a conexão for bem-sucedida, você verá "TcpTestSucceeded : True". Caso contrário, você receberá um "TcpTestSucceeded: False".

• Se a conexão Test-NetConnection (Telnet) com o endereço IP funcionar, mas não funcionar com o ServerName, provavelmente há um problema de resolução de nome ou de DNS

• Se a conexão funcionar pelo ServerName e não pelo endereço IP, pode haver mais de um ponto de extremidade definido no servidor (talvez outra instância SQL) que esteja escutando nessa porta. Embora o status do ponto de extremidade na instância em questão mostre "INICIADO", outra instância pode realmente ter a porta associada e impedir que a instância correta escute e estabeleça conexões TCP.

• Se o Test-NetConnection falhar ao se conectar, procure o firewall e/ou o software antivírus que possa estar bloqueando a porta do ponto de extremidade em questão. Verifique a configuração do firewall para ver se ele permite comunicação da porta do ponto de extremidade entre as instâncias do servidor que hospedam a réplica primária e secundária (por padrão, a porta 5022). Execute o script do PowerShell a seguir para examinar as regras de tráfego de entrada desabilitadas.

• Se você estiver executando o SQL Server na VM do Azure, além disso, precisará garantir que o NSG (grupo de segurança de rede) permita o tráfego para a porta do ponto de extremidade. Verifique a configuração do firewall (e NSG para VM do Azure) para ver se ele permite comunicação da porta do ponto de extremidade entre as instâncias do servidor que hospedam a réplica primária e secundária (por padrão, a porta 5022).

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

• Capture uma saída do cmdlet Get-NetTCPConnection (equivalente a NETSTAT -a) e verifique se o status é LISTENING ou ESTABLISHED no IP:Porta do ponto de extremidade especificado

  Get-NetTCPConnection 
  

Ouvinte

Para obter a configuração correta de um ouvinte do Grupo de Disponibilidade, siga "Configurar um ouvinte para um grupo de disponibilidade Always On"

1. Depois que o ouvinte estiver configurado, você poderá validar o endereço IP e a porta em que ele está escutando usando a seguinte consulta:

   $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. Você também pode encontrar as informações do ouvinte com as portas do SQL Server usando 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. Se você precisar estabelecer conectividade com o ouvinte e suspeitar que uma porta está bloqueada, poderá executar um teste usando o cmdlet Test-NetConnection do 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 fim, verifique se o ouvinte está escutando na porta especificada:

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

Acesso ao ponto de extremidade (erro 1418 do SQL Server)

Essa mensagem do SQL Server indica que o endereço de rede do servidor especificado na URL do ponto de extremidade não pode ser acessado ou não existe e sugere que você verifique o nome do endereço de rede e emita o comando novamente.

Falha ao unir bancos de dados (erro 35250 do SQL Server)

Discute as possíveis causas e resolução de uma falha ao unir bancos de dados secundários a um grupo de disponibilidade porque a conexão com a réplica primária não está ativa. Essa é a mensagem de erro completa:

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

Resolução:

O resumo das etapas está descrito abaixo.

Para obter instruções detalhadas passo a passo, confira o erro do mecanismo MSSQLSERVER_35250

1. Verifique se o ponto de extremidade foi criado e iniciado.
2. Verifique se você pode se conectar ao ponto de extremidade via Telnet e verifique se nenhuma regra de firewall está bloqueando a conectividade
3. Verifique se há erros no sistema. Você pode consultar o sys.dm_hadr_availability_replica_states pelo last_connect_error_number, que pode ajudar você a diagnosticar o problema de ingresso.
4. Verifique se o ponto de extremidade está definido para que ele corresponda corretamente ao IP/porta que o grupo de disponibilidade está usando.
5. Verifique se a conta de serviço de rede tem permissão CONNECT para o ponto de extremidade.
6. Verifique se há possíveis problemas de resolução de nomes.
7. Verifique se o SQL Server está executando um build recente (preferivelmente o build mais recente para evitar enfrentar problemas já corrigidos.

O roteamento somente leitura não está funcionando corretamente

1. Verifique se você configurou o roteamento somente leitura seguindo o documento Configurar roteamento somente leitura.

2. Garantir o suporte ao driver cliente

   O aplicativo cliente precisa usar um provedor cliente que dê suporte ao parâmetro ApplicationIntent. Confira Suporte de driver e conectividade de cliente para grupos de disponibilidade

   Observação

   Se você estiver se conectando a um ouvinte DNN (nome de rede distribuído), o provedor também precisará dar suporte ao parâmetro MultiSubnetFailover

3. Verifique se as propriedades da cadeia de conexão estão definidas corretamente

   Para que o roteamento somente leitura funcione corretamente, seu aplicativo cliente precisa usar essas propriedades na cadeia de conexão:

   • Um nome de banco de dados que pertence ao grupo de disponibilidade
   • Um nome de ouvinte do grupo de disponibilidade
     • Se você estiver usando o DNN, especifique o nome do ouvinte DNN e o número da porta DNN <DNN name,DNN port>
   • ApplicationIntent definido como ReadOnly
   • Um MultiSubnetFailover definido como true é necessário para o DNN (nome de rede distribuído)

   Exemplos

   Este exemplo ilustra a cadeia de conexão para o provedor .NET System.Data.SqlClient para um ouvinte VNN (nome de rede virtual):

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

   Isso ilustra a cadeia de conexão para o provedor .NET System.Data.SqlClient para um ouvinte DNN (nome de rede distribuída):

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

   Observação

   Se você estiver usando programas de linha de comando como o SQLCMD, especifique as opções corretas para o nome do servidor. Por exemplo, no SQLCMD, você deve usar a opção maiúscula -S que especifica o nome do servidor, não a minúscula -s, que é usada para separador de coluna.
   Exemplo: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. Verifique se o ouvinte do grupo de disponibilidade está online. Para garantir que o ouvinte do grupo de disponibilidade esteja online, execute a seguinte consulta na réplica primária:

   SELECT * FROM sys.dm_tcp_listener_states;
   

   Se você achar que o ouvinte está offline, poderá tentar colocá-lo online usando um comando como este:

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. Verifique se READ_ONLY_ROUTING_LIST está preenchido corretamente. Na réplica primária, verifique se READ_ONLY_ROUTING_LIST contém somente instâncias de servidor que hospedem uma réplica secundária para leitura.

   Para exibir as propriedades de cada réplica, você pode executar essa consulta e examinar o ponto de extremidade de conectividade (URL) da réplica somente leitura.

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

   Para exibir uma lista de roteamento somente leitura e comparar com a URL do ponto de extremidade:

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   Para alterar uma lista de roteamento somente leitura, você pode usar uma consulta como esta:

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

   Para mais informações, confira Configurar o roteamento somente leitura para um grupo de disponibilidade – SQL Server Always On

6. Verifique se a porta READ_ONLY_ROUTING_URL está aberta. Verifique se o firewall do Windows não está bloqueando a porta READ_ONLY_ROUTING_URL. Configure um Firewall do Windows para o acesso ao mecanismo de banco de dados em cada réplica na read_only_routing_list e qualquer um para clientes que serão conectados a essas réplicas.

   Observação

   Se você estiver executando SQL Server na VM do Azure, deverá executar etapas de configuração adicionais. Verifique se o NSG (grupo de segurança de rede) de cada VM de réplica permite o tráfego para a porta do ponto de extremidade e a porta DNN, se você estiver usando o ouvinte DNN. Se você estiver usando o ouvinte VNN, verifique se o balanceador de carga está configurado corretamente.

7. Verifique se READ_ONLY_ROUTING_URL (TCP://system-address:port) contém o FQDN (nome de domínio totalmente qualificado) e o número da porta corretos. Consulte:

   • Calculando read_only_routing_url de AlwaysOn
   • sys.availability_replicas (Transact-SQL)
   • ALTER AVAILABILITY GROUP (Transact-SQL)

8. Garanta uma configuração de Rede SQL Server adequada no SQL Server Configuration Manager.

   Verifique em cada réplica na read_only_routing_list se:

   • A conectividade remota do SQL Server está habilitada
   • O TCP/IP está habilitado
   • Os endereços IP estão configurados corretamente

   Observação

   Você poderá verificar rapidamente se todos estão configurados da forma correta se você puder se conectar de um computador remoto ao nome da instância do SQL Server da réplica secundária de destino usando a sintaxe TCP:SQL_Instance.

Veja: Configurar um servidor para escutar em uma porta TCP específica (SQL Server Configuration Manager) e Exibir ou alterar as propriedades do servidor (SQL Server)

Related Tasks

• Criação e configuração de grupos de disponibilidade (SQL Server)

• Criar um ponto de extremidade de espelhamento de banco de dados para a Autenticação do Windows (Transact-SQL)

• Especifique a URL do Ponto de Extremidade Ao Adicionar ou Modificando uma Réplica de disponibilidade (SQL Server)

• Preparar um banco de dados secundário manualmente para um grupo de disponibilidade (SQL Server)

• Solução de problemas de uma operação de adicionar arquivos com falha (grupos de disponibilidade de AlwaysOn)

• Gerenciamento de logons e trabalhos para os bancos de dados de um grupo de disponibilidade (SQL Server)

• Gerenciar metadados ao disponibilizar um banco de dados em outra instância do servidor (SQL Server)

Conteúdo relacionado

• Exibir eventos e logs de um cluster de failover

• Cluster de failover Get-ClusterLog do cmdlet

• Blog da equipe do Always On do SQL Server: o blog oficial da equipe do Always On do SQL Server

Consulte Também

Segurança de transporte para espelhamento de banco de dados e Grupos de Disponibilidade AlwaysOn (SQL Server)
Configuração de rede de cliente
Pré-requisitos, restrições e recomendações para Grupos de Disponibilidade AlwaysOn (SQL Server)