Устранение неполадок конфигурации групп доступности Always On (SQL Server)

Область применения:SQL Server

В этой статье содержатся сведения, помогающие устранять типичные проблемы с настройкой экземпляров сервера для групп доступности AlwaysOn. Примеры типичных проблем настройки: группы доступности AlwaysOn отключены, учетные записи настроены неправильно, конечная точка зеркального отображения баз данных не существует, конечная точка недоступна (ошибка SQL Server 1418), отсутствует сетевой доступ, команда присоединения базы данных завершается с ошибкой (ошибка SQL Server 35250).

Примечание.

Убедитесь, что вы выполняете предварительные условия для групп доступности Always On. Дополнительные сведения см. в статье Предварительные требования, ограничения и рекомендации для групп доступности Always On (SQL Server).

В этом разделе:

Раздел	Описание
Функция групп доступности Always On не включена	Если экземпляр SQL Server не настроен для групп доступности Always On, этот экземпляр не поддерживает создание групп доступности и не может размещать реплики группы доступности.
Учетные записи	Описывает требования для правильной настройки учетных записей, в которых выполняется SQL Server.
Конечные точки	Обсуждается, как диагностировать вопросы, связанные с конечной точкой зеркалирования баз данных для экземпляра сервера.
Сетевой доступ	Документирует требование, что каждый экземпляр сервера, на котором размещена реплика доступности, должен иметь доступ к порту каждого из остальных экземпляров сервера через TCP.
Листенер	Документы, как установить IP-адрес и порт прослушивателя и убедиться, что он работает и прослушивает входящие подключения.
Доступ к конечной точке (ошибка SQL Server 1418)	Содержит сведения об этом сообщении об ошибке SQL Server.
Ошибка присоединения базы данных (ошибка SQL Server 35250)	** Обсуждаются возможные причины и способы устранения проблемы неспособности присоединить вторичные базы данных к группе доступности из-за неактивного соединения с первичной репликой.
Маршрутизация только для чтения не работает должным образом
Связанные задачи	Содержит список статей, ориентированных на задачи, в электронной документации по SQL Server, которые относятся к устранению неполадок конфигурации группы доступности.
См. также	Содержит список соответствующих ресурсов, внешних для электронной документации по SQL Server.

Группы доступности Always On не активированы

Компонент групп доступности AlwaysOn должен быть включен для каждого экземпляра SQL Server.

Если функция групп доступности Always On не включена, при попытке создать группу доступности на 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)

Сообщение об ошибке ясно указывает на то, что функция групп доступности не активирована, и также содержит инструкции по ее активации. Существуют два сценария, в которых вы можете оказаться в таком состоянии, помимо очевидного, когда группа доступности (AG) изначально не была включена.

1. Если SQL Server установлен и включена функция групп доступности AlwaysOn перед установкой функции отказоустойчивой кластеризации Windows, при попытке создать группу доступности AlwaysOn может возникнуть эта ошибка.
2. Если удалить существующий компонент отказоустойчивой кластеризации Windows и перестроить его, пока на SQL Server по-прежнему настроена функция Always On, при последующей попытке использовать AG может возникнуть эта ошибка.

В таких случаях для ее устранения можно выполнить следующие действия.

1. Отключите функцию AG.
2. Перезапустите службу SQL Server.
3. Включите обратно функцию AG.
4. Еще раз перезапустите службу SQL.

Дополнительные сведения см. в разделе Включение и отключение групп доступности Always On (SQL Server).

Учётные записи

Учетные записи, в которых работает SQL Server, должны быть правильно настроены.

1. Имеют ли учетные записи нужные разрешения?

   1. Если для участников используется одна и та же учетная запись домена, то правильные имена входа существуют в обеих базах данных master. Такой подход рекомендуется, так как он упрощает настройку безопасности.

   2. Если два экземпляра сервера выполняются под разными учетными записями, то каждая учетная запись должна быть создана в базе данных master на удаленном экземпляре сервера, и этому серверному принципалу необходимо присвоить разрешения CONNECT для подключения к конечной точке зеркального отображения базы данных на этом экземпляре сервера. Дополнительные сведения см. в статье Настройка учетных записей входа для зеркального отображения баз данных или групп доступности Always On (SQL Server). Чтобы проверить, имеются ли у учётных записей разрешения 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. Если SQL Server работает под встроенной учетной записью, такой как локальная система, локальная служба или сетевая служба, или учетная запись, не являющаяся доменом, необходимо использовать сертификаты для проверки подлинности конечной точки. Если учетные записи служб используют учетные записи доменов в одном домене, вы можете предоставить доступ CONNECT для каждой учетной записи службы на всех расположениях реплики либо воспользоваться сертификатами. Дополнительные сведения см. в статье Использование сертификатов для конечной точки зеркального отображения базы данных (Transact-SQL).

Конечные точки

Конечные точки должны быть правильно настроены.

1. Убедитесь, что каждый экземпляр SQL Server, на котором будет размещаться реплика доступности (каждое расположение реплики), имеет конечную точку зеркального отображения базы данных. Чтобы определить, существует ли конечная точка зеркального отображения баз данных на конкретном экземпляре сервера, воспользуйтесь представлением каталога sys.database_mirroring_endpoints.

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   Дополнительные сведения о создании конечных точек см. в статье Создание конечной точки зеркального отображения базы данных с проверкой подлинности Windows (Transact-SQL) или Использование сертификатов для исходящих соединений при зеркальном отображении базы данных (Transact-SQL).

2. Убедитесь, что номера портов правильны.

   Чтобы определить, какой порт в текущий момент связан с конечной точкой зеркального отображения базы данных экземпляра сервера, воспользуйтесь следующей инструкцией Transact-SQL:

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. Если при настройке групп доступности Always On возникают трудно объяснимые проблемы, рекомендуется проверить, прослушивает ли каждый экземпляр сервера правильный порт.

4. Убедитесь, что конечные точки запущены (STATE=STARTED). На каждом экземпляре сервера выполните следующую инструкцию Transact-SQL:

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   Дополнительные сведения о столбце state_desc см. в разделе sys.database_mirroring_endpoints (Transact-SQL).

   Чтобы запустить конечную точку, выполните следующую инструкцию Transact-SQL:

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

   Дополнительные сведения см. в статье ALTER ENDPOINT (Transact-SQL).

   Примечание.

   В некоторых случаях, если конечная точка запущена, но реплики группы доступности (AG) не взаимодействуют, попробуйте остановить и перезапустить конечную точку. Можно использовать команду ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED, а затем ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED.

5. Убедитесь, что логину с другого сервера разрешено подключение. Чтобы узнать, кто имеет разрешение CONNECT для конечной точки, выполните следующую инструкцию 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. Убедитесь, что в URL-адресе конечной точки имя сервера указано правильно.

   В URL-адресе конечной точки рекомендуется указывать полное доменное имя (FQDN) сервера. При этом можно использовать любое уникальное имя, идентифицирующее компьютер. Адрес сервера может быть NetBIOS-именем (если системы находятся в одном домене), полным доменным именем или IP-адресом (желательно статическим). Рекомендуется использовать полностью определённое доменное имя.

   Если вы уже определили URL-адрес конечной точки, можно запросить его следующим образом:

   select endpoint_url from sys.availability_replicas
   

   Затем сравните полученное значение endpoint_url с именем сервера (NetBIOS или FQDN). Чтобы запросить имя сервера, выполните следующие команды в среде PowerShell на реплике локально:

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

   Чтобы проверить имя сервера на удаленном компьютере, выполните следующую команду в PowerShell.

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

   Дополнительные сведения см. в разделе Выбор URL-адреса конечной точки при добавлении или изменении реплики доступности (SQL Server).

Примечание.

Чтобы использовать аутентификацию Kerberos для обмена данными между конечными точками группы доступности, зарегистрируйте имя главного имени службы для подключений Kerberos для конечных точек зеркального отображения базы данных, используемых конечными точками группы доступности.

Сетевой доступ

Каждый экземпляр сервера, на котором размещена реплика доступности, должен иметь доступ к порту каждого из других экземпляров сервера через TCP. Это особенно важно, если экземпляры сервера находятся в разных доменах, не имеющих доверительных отношений друг с другом (домены без доверия). Проверьте, можно ли подключиться к конечным точкам, выполнив следующие действия:

• Используйте команду Test-NetConnection (эквивалент Telnet) для проверки подключения. Вот примеры команд, которые можно использовать:

  $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
  

• Если конечная точка прослушивает и подключение успешно, отображается сообщение "TcpTestSucceeded: True". Если нет, вы получите сообщение "TcpTestSucceed: False".

• Если подключение Test-NetConnection (Telnet) к IP-адресу работает, но подключение по имени сервера установить не удается, вероятно, это вызвано проблемой с DNS или с разрешением имен.

• Если подключение работает через ServerName, а не через IP-адрес, возможно, на этом сервере определено несколько конечных точек (возможно, другой экземпляр SQL), прослушивающих этот порт. Хотя на данном экземпляре состояние конечной точки показывается как "STARTED", на самом деле другой экземпляр может занять порт, что помешает корректному экземпляру прослушивать и устанавливать TCP-соединения.

• Если Test-NetConnection не удается подключиться, найдите брандмауэр и (или) антивирусное программное обеспечение, которое может блокировать порт конечной точки. Проверьте параметры брандмауэра, чтобы узнать, разрешена ли связь для портов конечных точек между экземплярами серверов, на которых размещаются первичная и вторичная реплика (по умолчанию порт 5022). Выполните следующий сценарий PowerShell, чтобы проверить наличие отключенных правил для входящего трафика.

• Если вы используете SQL Server на виртуальной машине Azure, необходимо также убедиться, что группа безопасности сети (NSG) разрешает трафик к порту конечной точки. Проверьте параметры брандмауэра (и группы безопасности сети для виртуальной машины Azure), чтобы убедиться, что разрешено взаимодействие через порт конечной точки между экземплярами серверов, на которых размещаются первичная и вторичная реплики (по умолчанию порт 5022).

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

• Получите данные, выводимые командлетом Get-NetTCPConnection (эквивалент NETSTAT -a), и убедитесь, что состояние для IP-адреса и порта указанной конечной точки указано как LISTENING (Ожидает передачи данных) или ESTABLISHED (Соединение установлено).

  Get-NetTCPConnection 
  

Слушатель

Для правильной настройки прослушивателя группы доступности выполните инструкции "Настройка прослушивателя для группы доступности AlwaysOn"

1. После настройки прослушивателя можно проверить IP-адрес и порт, с помощью следующего запроса:

   $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. Вы также можете найти информацию о прослушивателе и портах SQL Server с помощью этого запроса:

   $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. Если же вам нужно установить подключение к прослушивателю и вы подозреваете, что порт заблокирован, проверьте порт с помощью командлета Test-NetConnection PowerShell (эквивалент 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. Наконец, проверьте, прослушивает ли прослушиватель по указанному порту:

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

Доступ к конечной точке (ошибка SQL Server 1418)

Это сообщение SQL Server уведомляет, что сетевой адрес сервера, указанный в URL-адресе конечной точки сервера, недоступен или не существует, и предлагает выполнить проверку имени сетевого адреса и повторно выполнить команду.

Ошибка присоединения базы данных (ошибка SQL Server 35250)

В этом разделе обсуждаются возможные причины и способы устранения проблемы с присоединением вторичных баз данных к группе доступности из-за того, что соединение с первичной репликой неактивно. Полный текст сообщения об ошибке:

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

Решение.

Ниже приведено общее описание действий.

Подробные пошаговые инструкции см. в описании ошибки ядра MSSQLSERVER_35250.

1. Убедитесь, что конечная точка создана и активирована.
2. Проверьте, можете ли вы подключиться к конечной точке через Telnet, и убедитесь, что никакие правила брандмауэра не блокируют подключение
3. Проверьте наличие ошибок в системе. Вы можете выполнить запрос к sys.dm_hadr_availability_replica_states на last_connect_error_number, что может помочь вам диагностировать проблему соединения.
4. Убедитесь, что конечная точка определена таким образом, чтобы она правильно соответствовала IP-адресу и порту, которые использует AG.
5. Проверьте наличие у учетной записи сетевой службы разрешения CONNECT для конечной точки.
6. Проверьте наличие возможных проблем с разрешением имен.
7. Убедитесь, что используется свежая сборка SQL Server (желательно использовать последнюю сборку) для защиты от возникновения уже исправленных проблем.

Маршрутизация только для чтения работает неправильно.

1. Убедитесь, что вы настроили маршрутизацию только для чтения, следуя инструкции по настройке маршрутизации только для чтения.

2. Проверьте поддержку клиентских драйверов.

   Клиентское приложение должно использовать клиентский провайдер, поддерживающий параметр ApplicationIntent. См. раздел Поддержка возможности подключения драйвера и клиента для групп доступности

   Примечание.

   Если вы подключаетесь к прослушивателю с именем распределенной сети (DNN), поставщик также должен поддерживать параметр MultiSubnetFailover.

3. Убедитесь, что свойства строки подключения заданы правильно.

   Для правильной работы маршрутизации только для чтения клиентское приложение должно использовать следующие свойства в строке подключения.

   • Имя базы данных, принадлежащее группе доступности (Availability Group).
   • Имя прослушивателя группы доступности
     • При использовании DNN необходимо указать имя прослушивателя и номер порта DNN: <DNN name,DNN port>.
   • Для ApplicationIntent задано значение ReadOnly.
   • Для имени распределенной сети (DNN) параметр MultiSubnetFailover должен иметь значение true.

   Примеры

   В этом примере показана строка подключения для поставщика .NET Microsoft.Data.SqlClient или System.Data.SqlClient для прослушивателя виртуальной сети (VNN):

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

   В этом примере показана строка подключения для поставщика .NET Microsoft.Data.SqlClient или System.Data.SqlClient для прослушивателя распределенной сети (DNN).

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

   Примечание.

   Если вы используете программы командной строки, такие как SQLCMD, убедитесь, что указаны правильные параметры для имени сервера. Например, в SQLCMD необходимо использовать параметр -S в верхнем регистре, указывающий имя сервера, а не параметр -s в нижнем регистре, используемый для разделителя столбцов.
   Пример: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. Убедитесь, что прослушиватель группы доступности находится в режиме «в сети». Чтобы убедиться, что прослушиватель группы доступности подключен, выполните на первичной реплике следующий запрос:

   SELECT * FROM sys.dm_tcp_listener_states;
   

   Если прослушиватель отключен, вы можете попытаться включить его, выполнив следующую команду:

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. Убедитесь, что параметр READ_ONLY_ROUTING_LIST заполнен правильно. На первичной реплике убедитесь, что параметр READ_ONLY_ROUTING_LIST содержит только те экземпляры сервера, на которых размещены читаемые вторичные реплики.

   Чтобы просмотреть свойства каждой реплики, можно выполнить этот запрос и осмотреть конечную точку подключения (URL-адрес) реплики только для чтения.

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

   Чтобы просмотреть список маршрутизации только для чтения и сравнить с URL-адресом конечной точки:

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   Чтобы изменить список маршрутизации только для чтения, можно использовать следующий запрос:

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

   Дополнительные сведения см. в разделе "Настройка маршрутизации только для чтения" для группы доступности — SQL Server AlwaysOn

6. Убедитесь, что открыт порт READ_ONLY_ROUTING_URL. Убедитесь, что брандмауэр Windows не блокирует порт READ_ONLY_ROUTING_URL. Настройте брандмауэр Windows для доступа к механизму базы данных на каждой реплике в read_only_routing_list, а также для клиентов, подключающихся к этим репликам.

   Примечание.

   Если вы используете SQL Server на виртуальной машине Azure, необходимо выполнить дополнительные действия по настройке. Убедитесь, что группа безопасности сети (NSG) каждой виртуальной машины реплики разрешает трафик к порту конечной точки и порту DNN, если используется прослушиватель DNN. Если вы используете прослушиватель VNN, необходимо убедиться, что подсистема балансировки нагрузки настроена правильно.

7. Убедитесь, что параметр READ_ONLY_ROUTING_URL (TCP://system-address:port) содержит правильное полное доменное имя (FQDN) и номер порта. См.

   • Вычисление значения read_only_routing_url для AlwaysOn
   • sys.availability_replicas (Transact-SQL)
   • ИЗМЕНИТЬ ГРУППУ ДОСТУПНОСТИ (Transact-SQL)

8. Убедитесь в правильной конфигурации сети SQL Server в диспетчере конфигурации SQL Server.

   Проверьте каждую реплику в списке read_only_routing_list на следующее:

   • Удаленное подключение к SQL Server включено
   • включен протокол TCP/IP;
   • правильно настроены IP-адреса.

   Примечание.

   Все эти параметры можно быстро настроить, если вы можете подключиться с удаленного компьютера к имени экземпляра SQL Server целевой вторичной реплики с помощью TCP:SQL_Instance синтаксиса.

См. статьи Настройка сервера для прослушивания определенного TCP-порта (диспетчер конфигурации SQL Server) и Просмотр или изменение свойств сервера (SQL Server).

Связанные задачи

• Создание и настройка групп доступности (SQL Server)

• Создать конечную точку зеркалирования базы данных с проверкой подлинности Windows (Transact-SQL)

• Укажите URL-адрес конечной точки при добавлении или изменении реплики доступности (SQL Server)

• Вручную подготовить вторичную базу данных для группы доступности (SQL Server)

• Устранение неполадок при сбое операции добавления файла (группы доступности Always On)

• Управление логинами и заданиями для баз данных группы доступности (SQL Server)

• Управление метаданными при создании базы данных в другом экземпляре сервера (SQL Server)

Связанный контент

• Просмотр событий и логов для отказоустойчивого кластера
• Командлет Get-ClusterLog для отказоустойчивого кластера
• Блоги команды разработчиков SQL Server AlwaysOn: официальный блог по SQL Server AlwaysOn
• Транспортная безопасность для зеркального отображения баз данных и групп доступности Always On (SQL Server)
• Конфигурация клиентской сети
• Предварительные требования, ограничения и рекомендации для групп доступности AlwaysOn (SQL Server)