Группы доступности — гибрид зеркального отображения баз данных SQL Server и отказоустойчивой кластеризации Windows — становятся фактическим стандартом при реализации решений высокой доступности для Microsoft SQL Server. Освоить их может быть сложно, но для этого не потребуется много времени. В этой статье мы не будем подробно рассматривать группы доступности (AG) или процессы установки и настройки групп доступности, а обратим внимание на динамические объекты управления (DMO), полезные при анализе архитектуры и состояния групп доступности с использованием динамических административных представлений (DMV) SQL Server.

Динамические объекты управления, рассматриваемые в статье, разделяются на две категории: ориентированные на уровень детализации сервера и предназначенные для исследования состояния баз данных в группах доступности, размещенных в вашей среде (см. рисунок 1). Независимо от категории, для просмотра результатов из любого DMV необходимо иметь разрешения View Server State («Просмотр состояния сервера»).

 

Две категории динамических объектов управления
Рисунок 1. Две категории динамических объектов управления

 

Эти DMV единообразны в версиях SQL Server, от 2012 до 2016. В SQL Server 2016 появились два новых динамических административных представления, относящихся к новой функции или методу заполнения реплик без процесса резервного копирования и восстановления:

  • dm_hadr_physical_seeding_stats;
  • dm_hadr_physical_seeding_stats.

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

Далее в статье будет представлен обзор предоставляемой каждым DMV информации (возвращаемые столбцы) и некоторых вопросов, на которые каждый DMV отвечает индивидуально или с помощью других динамических административных представлений или представлений системного каталога. Некоторые из этих объектов полезны только для преобразования идентификаторов объектов (ag_id, group_id, replica_id и т. д.) в логическое имя, но большинство служат достойным целям идентификации состояния синхронизации, состояния AG, архитектуры, конфигурации и т. д. Вам не обязательно ограничиваться возможностями графического интерфейса пользователя в среде SQL Server Management Studio. Благодаря динамическим административным представлениям SQL Server у вас есть множество вариантов построения собственных сценариев мониторинга и информационных панелей для работы с группами доступности в среде SQL Server.

sys.dm_hadr_cluster

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • cluster_name;
  • quorum_type;
  • quorum_type_desc;
  • quorum_state;
  • quorum_state_desc.

Это DMV предоставляет информацию о кворуме для каждого узла в WSFC, поддерживающего группу доступности. Если узел имеет кворум, то будет показана информация о кворуме для этого узла. Если кворум отсутствует, никаких результатов не возвращается.

sys.dm_hadr_cluster_members

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • member_name;
  • member_type:

— 0 = Cluster Node;

— 1 = Disk Witness;

— 2 = File Share Witness;

  • member_type_description;
  • member_state:

— 0 = Offline;

— 1 = Online;

  • member_state_desc;
  • number_of_quorum_votes.

sys.dm_hadr_cluster_members предоставляет информацию обо всех узлах в кластерной экосистеме, которая составляет фундамент групп доступности. Он идентифицирует имя сервера каждого узла-участника, тип участника (узел или свидетель) и состояние участника. Столбцы, представляющие особый интерес, формируются запросом, приведенным в листинге 1; не все столбцы содержат значение.

Замечу, что если узел отказоустойчивого кластера Windows Server (WSFC), участвующий в WSFC, не имеет кворума, то строка для узла не будет возвращена.

dm_hadr_cluster_networks

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • member_name;
  • network_sub_ip;
  • network_subnet_ipv4_mask;
  • network_subnet_prefix_length;
  • is_ipv4.

Это динамическое административное представление возвращает сетевые метаданные для каждого члена кластера WSFC, входящего в подсеть группы доступности. Полезность этого DMV ограничена, но оно позволяет проверить виртуальный IP-адрес сети, настраиваемый для каждой реплики доступности, и выяснить, находится он в подсети IPv4 или IPv6 и является ли сеть общедоступной или частной. Учитывая количество строк, которые предположительно будут возвращены для данного кластера, и цели запросов, направляемых к DMV для получения общих сетевых метаданных, запрос SELECT * приемлем для этого DMV:

SELECT *
FROM sys.dm_hadr_cluster_networks;

Вследствие кратности первичный ключ для данного DMV представляет собой составной ключ из member_name, network_subnet_ip и network_subject_name_prefix_length.

sys.dm_hadr_instance_node_map

Тип — динамическое администра­тивное представление. Возвра­ща­емые столбцы:

  • ag_resource_id;
  • instance_name;
  • node_name.

Это DMV предоставляет имя экземпляра и имя узла WSFC и имена всех экземпляров, участвующих в WSFC. Вы также увидите ag_resource_id, назначенный экземпляру. Это позволяет передать имя узла и имя экземпляра SQL любому DMV, предоставляющему ag_resource_id для преобразования ag_resource_id в нечто пригодное для идентификации. Вряд ли вы когда-нибудь направите запрос к этому DMV напрямую, скорее оно как часть более крупного запроса будет использоваться для предоставления контекста именования, если существует только ag_resource_id.

Кроме того, как отмечается в документации Microsoft, это динамическое административное представление полезно для обнаружения группы доступности с несколькими репликами доступности, размещенными на том же узле WSFC. Это неподдерживаемая схема, которая может возникнуть после отработки отказа FCI, если группа доступности настроена неверно.

Это условие идентифицируется с помощью запроса, приведенного в листинге 2.

dm_hadr_name_id_map

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • ag_name;
  • ag_id;
  • ag_resource_id;
  • ag_group_id.

Это DMV существует единственно с целью обеспечить связывание и разрешение имен в случае переименования группы доступности. Имена входа и учетные записи пользователей в SQL связаны с идентификатором SID, который уникально идентифицирует объект (вместо того, чтобы идентифицировать объект с помощью имени входа или имени пользователя), то же и в случае с ag_id. Аналогично, как вы увидите у динамических административных представлений, рассматриваемых далее в этой статье, ресурсы и группы, составляющие экосистему AG (при наличии свойства имени, которое может быть изменено), также имеют базовое значение ID, которое не изменяется и используется для поддержания отношений между различными объектами и уровнями детализации в AG. Это DMV используется в объединениях, предусматривающих отношения между многими DMV, рассмотренными в данной статье. К этому DMV редко обращаются напрямую, но его часто используют в качестве промежуточного соединения между двумя или несколькими DMV. resource_id и group_id сопоставляются с ресурсами и группами ресурсов WSFC.

Следует отметить, во избежание недоразумений, что ag_id связывается с группой доступности, тогда как ag_group_id идентифицирует WSFC Group Id группы доступности.

dm_hadr_availability_replica_cluster_nodes

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • group_name;
  • replica_server_name;
  • node_name.

Это DMV возвращает строку для каждой реплики доступности (независимо от состояния присоединения) группы доступности в отказоустойчивом кластере Windows Server (WSFC). Поскольку идентификатор группы доступности не представлен в данном DMV, необходимо присоединить sys.dm_hadr_availability_replica_cluster_nodes через sys.dm_hadr_availability_replica_cluster_states.replica_server_name или sys. dm_hadr_availability_replica_cluster_states.node_name. Аналогично вы можете присоединить к dm_hadr_availability_group_states.primary_replica на replica_server_name, чтобы получить group_id для группы доступности, или к sys.dm_hadr_name_id_map.ag_name = dm_hadr_availability_replica_cluster_nodes.group_name, чтобы возвратить значение ag_id. Это область, в которой Microsoft не определила соглашения об именовании в динамических объектах управления и представления системных каталогов. В одном DMV вы видите столбец с именем ag_name, в другом он же именуется group_name. Вы можете использовать любые из этих присоединений в обратном порядке, чтобы упростить восприятие ag_id, например в sys.dm_hadr_availability_group_states, выдавая ag_name.

dm_hadr_availability_replica_cluster_states

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • replica_id;
  • replica_server_name;
  • group_id;
  • join_state;
  • join_state_desc.

Это DMV наиболее полезно, когда необходимо преобразовать replica_id в различных DMV в понятное имя, в данном случае имя сервера для реплики, представленное в столбце replica_server_name. Пример использования этого DMV — dm_hadr_availability_group_states, рассматриваемое ниже.

dm_hadr_availability_replica_states

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • replica_id;
  • group_id;
  • is_local;
  • role;
  • role_desc;
  • operational_state;
  • operational_state_desc;
  • connected_state;
  • connected_state_desc;
  • recovery_health;
  • recovery_health_desc;
  • synchronization_health;
  • synchronization_health_desc;
  • last_connect_error_number;
  • last_connect_error_description;
  • last_connect_error_timestamp.

Это DMV возвращает строку, показывающую состояние каждой локальной и удаленной реплики доступности в той же группе доступности, что и локальная реплика, к которой направляется запрос. Лучше всего использовать для этой цели хост для первичной реплики, чтобы обеспечить наиболее полную картину состояния всех реплик, участвующих в AG. Если выполнить запросы на sys.dm_hadr_availability_replica_states на хосте для любой вторичной реплики, то вы возвратите только локальную информацию для группы доступности (запрос к вспомогательному узлу).

Чтобы вернуть полную информацию о состоянии реплики для AG, выполните код, приведенный в листинге 3.

dm_hadr_availability_group_states

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • group_id;
  • primary_replica;
  • primary_recovery_health;
  • secondary_recovery_health;
  • primary_recovery_health_desc;
  • secondary_recovery_health_desc;
  • synchronization_health;
  • synchronization_health_desc.

sys.dm_hadr_availability_group_states предоставляет сведения о работоспособности для AG, а также для узла, к которому направляется запрос, как показано в запросе в листинге 4.

DMV уровня базы данных, ориентированные на AG dm_hadr_database_replica_states

Тип — динамическое административное представление, возвращаемые столбцы показаны на рисунке 2.

 

Возвращаемые столбцы динамического административного представления dm_hadr_database_replica_states
Рисунок 2. Возвращаемые столбцы динамического административного представления dm_hadr_database_replica_states

sys.dm_hadr_database_replica_states возвращает строку для каждой базы данных, участвующей в группе доступности, и предоставляет информацию как о первичной, так и о вторичной реплике. На вторичной реплике это представление возвращает строку для каждой базы данных-получателя на экземпляре сервера. На первичной реплике это представление возвращает строку для каждой базы данных-источника и дополнительную строку для соответствующей базы данных-получателя. Поэтому учитывайте, что две строки возвращаются для каждой базы данных при выполнении на первичной реплике, но только одна — и для значений, связанных со вторичной репликой базы данных, — при выполнении на хосте вторичной реплики. Кроме того, Microsoft ясно дает понять, что значения столбцов end_of_log_lsn, last_hardened_lsn, last_received_lsn, last_sent_lsn, recovery_lsn и truncation_lsn не являются регистрационными номерами транзакции в журнале (LSN). В действительности это идентификатор блока журнала с заполнением нулями. Блоки журнала представляют собой физические контейнеры для известных виртуальных файлов журнала (VLF). Из значений LSN, возвращаемых sys.dm_hadr_database_replica_states, только last_redone_lsn является настоящим LSN.

Запрос, приведенный в листинге 5, предоставляет моментальный снимок состояния на уровне базы данных для всех групп доступности на экземпляре SQL, обеспечивая преобразование идентификаторов всех объектов в имена.

dm_hadr_database_replica_cluster_states

Тип — динамическое администра­тивное представление. Возвраща­емые столбцы:

  • replica_id;
  • group_database_id;
  • database_name;
  • is_failover_ready;
  • is_pending_secondary_suspend;
  • is_database_joined;
  • recovery_lsn;
  • truncation_lsn.

Это DMV уровня базы данных, как и sys.dm_hadr_database_replica_states, но ориентированное исключительно на готовность к отработке отказа. Оно предоставляет информацию для каждой базы данных, участвующей в AG, и соответствующее состояние готовности к отработке отказа, выраженное значением разряда 1 (готово) и 0 (не готово).

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

Идентификация реплик, приводящих к значению log_reuse_wait_desc, равному AVAILABILITY_REPLICA, когда размеры журналов транзакций выходят из-под контроля из-за невозможности усечения, выполняется кодом, приведенным в листинге 7.

Таким образом, группы доступности — мощная замена для зеркального отображения баз данных SQL Server. Несмотря на сложность настройки и формирования, после настройки вы можете получить сведения об их состоянии благодаря не только объектам графического интерфейса в среде SQL Server Management Studio, но и Transact SQL, и динамическому административному представлению SQL Server. Надеюсь, вам был полезен этот обзор. В дальнейшем мы рассмотрим, как можно использовать каждый из этих DMO вместе и по отдельности, чтобы оценивать и внедрять решения высокой доступности в SQL Server 2016 и последующих версиях.

Листинг 1. Запрос к sys.dm_hadr_cluster_members
SELECT member_name
     , member_type_desc
     , member_state_desc
FROM sys.dm_hadr_cluster_members;
Листинг 2. Обнаружение группы доступности
SELECT node_name
FROM sys.dm_hadr_instance_node_map
GROUP BY node_name
HAVING COUNT(ag_resource_id) > 1;
Листинг 3. Получение полной информации о состоянии реплики для AG
SELECT hadrNIM.ag_name
, hadrARCS.replica_server_name
     , hadrARS.is_local
     , hadrARS.role_desc
     , hadrARS.operational_state_desc
     , hadrARS.connected_state_desc
     , hadrARS.recovery_health_desc
     , hadrARS.synchronization_health_desc
     , hadrARCS.join_state_desc
FROM sys.dm_hadr_availability_replica_states AS hadrARS
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS hadrARCS
          ON hadrARS.replica_id = hadrARCS.replica_id
     INNER JOIN sys. dm_hadr_name_id_map AS hadrNIM
          ON hadrARS.group_id = hadrNIM.ag_id;
Листинг 4. Сведения о работоспособности AG и узла
SELECT ag_name
     , primary_replica
     , primary_recovery_health_desc
     , secondary_recovery_health_desc
     , synchronization_health_desc
FROM sys.dm_hadr_availability_group_states AS hadrAGS
     INNER JOIN sys.dm_hadr_name_id_map AS hadrNIM
          ON hadrAGS.group_id = hadrNIM.ag_id;
Листинг 5. Моментальный снимок состояния на уровне базы данных
SELECT hadrNIM.ag_name
     , hadrARCS.replica_server_name
     , hadrDRCS.[database_name]
     , hadrDRS.is_local
     , hadrDRS.synchronization_state_desc
     , hadrDRS.synchronization_health_desc
     , hadrDRS.database_state_desc
     , hadrDRS.is_suspended
     , hadrDRS.suspend_reason_desc
     , hadrDRS.last_sent_time
     , hadrDRS.last_received_time
     , hadrDRS.last_hardened_time
     , hadrDRS.last_redone_time
     , hadrDRS.last_commit_time
     , hadrDRS.log_send_queue_size
     , hadrDRS.log_send_rate
FROM sys.dm_hadr_database_replica_states AS hadrDRS
     INNER JOIN sys.dm_hadr_name_id_map AS hadrNIM
          ON hadrDRS.group_id = hadrNIM.ag_id
     INNER JOIN sys.dm_hadr_database_replica_cluster_states AS hadrDRCS
          ON hadrDRS.group_database_id = hadrDRCS.group_database_id
     INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS hadrARCS
          ON hadrDRS.replica_id = hadrARCS.replica_id
ORDER BY hadrNIM.ag_name
     , hadrDRCS.[database_name]
     , hadrDRS.is_local;
Листинг 6. Базы данных в AG, не готовые к отработке отказа
SELECT *
FROM sys.dm_hadr_database_replica_cluster_states
WHERE is_failover_ready = 0;
Листинг 7. Идентификация реплик со сбоем в усечении журналов транзакций
SELECT hadrARCS.replica_server_name
     , hadrDRCS.database_name
FROM sys.dm_hadr_database_replica_cluster_states AS hadrDRCS
     INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS hadrARCS
          ON hadrDRCS.replica_id = hadrARCS.replica_id
     INNER JOIN
          (
               SELECT group_database_id
               FROM sys.dm_hadr_database_replica_states
               WHERE is_local = 0
                     AND synchronization_state_desc != 'SYNCHRONIZED'
          ) AS hadrDRS
               ON hadrDRCS.group_database_id = hadrDRS.group_database_id
     INNER JOIN sys.databases AS D
          ON hadrDRCS.database_name = D.name
WHERE D.log_reuse_wait_desc = 'AVAILABILITY_REPLICA'
     AND hadrDRCS.is_database_joined = 1;