Настройка группы доступности SQL Server AlwaysOn
(на уровне баз данных)

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

Экземпляры Группы доступности AlwaysOn и экземпляры отказоустойчивого кластера AlwaysOn используют технологию платформы WSFC и регистрируют компоненты в качестве ресурсов кластера WSFC. Связанные ресурсы объединяются в роль, которую можно сделать зависимой от других ресурсов кластера WSFC. Затем кластер WSFC сможет выявлять необходимость в перезапуске экземпляра SQL Server (и сигнализировать об этой необходимости), а также автоматически выполнять отработку отказа с переходом на другой серверный узел в кластере WSFC.

MSSQL AlwaysOn - часть 1: ​​Предварительные требования 

Требование №1: ​Установка средства отказоустойчивости кластеров 

Требование №1: ​​​Проверка конфигурации

Требование №1: ​​​​Создание отказоустойчивого кластера

Требование №2: ​​​​​Учетные записи служб, управляемых группами 

Требование №2: ​​​​​​​​​Создание gMSA учетной записи 

Требование №2: ​​​​​​​​​​Установка экземпляра SQL Server 

Требование №3: Параметры сортировки сервера в SQL Server​​​​​​​​​​  

Требование №4: ​​Включение функции "AlwaysOn": Использование диспетчера конфигурации SQL Server  

Создание и настройка группы обеспечения доступности

Создание группы доступности AlwaysON (с помощью мастера)

Определения уровня работоспособности базы данных:

Поддержка DTC для DB

Управление неразрешенными транзакциями:

Настройка прослушивателя для группы доступности Always On:

Создание группы доступности AlwaysON (с T-SQL)

Проверка группы доступности AlwaysON

Высокий уровень доступности на уровне баз данных с Группы доступности AlwaysOn

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

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

При отработке отказа вместо переноса владения общих физических ресурсов на другой узел WSFC используется для перенастройки вторичной реплики на другом экземпляре SQL Server в первичную реплику группы доступности. Затем ресурс виртуального сетевого имени группы доступности переводится на этот экземпляр.

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

ПРИМЕЧАНИЕ. ​​​Группы доступности AlwaysOn не требует развертывать экземпляр отказоустойчивого кластера или использовать симметричное общее хранилище (SAN или SMB).

Экземпляр отказоустойчивого кластера (FCI) может использоваться совместно с группой доступности для повышения доступности реплики доступности. Однако во избежание соперничества в кластере WSFC автоматический переход на другой ресурс группы доступности не поддерживается для реплики доступности, размещенной в FCI..

В данной статье мы будем настраивать AlwaysOn высокий уровень доступности на уровне баз данных с использованием двух узлов. 

Подготовим два узла как показано на схеме ниже предварительно введенных в домен Active Directory:

Предварительные требования

Приступим к выполнению требований прежде чем настроим группы доступности SQL Server AlwaysOn

ТРЕБОВАНИЕ 1: Этот компьютер должен быть узлом кластера WSFC. Экземпляры SQL Server, на которых размещаются реплика доступности для определенной группы доступности, находятся на отдельных узлах кластера.

Отказоустойчивый кластер Windows Server (WSFC) — это группа независимых серверов, совместная работа которых позволяет повысить доступность приложений и служб. 

Установка средства отказоустойчивости кластеров

Следующие командлеты Windows PowerShell выполняют те же функции, что и описанные выше процедуры в этом разделе. 
В приведенном ниже примере устанавливается средство отказоустойчивости кластеров.

PS   Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools 

Проверка конфигурации

Перед созданием отказоустойчивого кластера настоятельно рекомендуется проверить конфигурацию, чтобы убедиться в том, что оборудование и его настройки совместимы с отказоустойчивой кластеризацией. Корпорация Майкрософт поддерживает кластерное решение, только если конфигурация прошла все проверочные тесты и все оборудование сертифицировано для версии Windows Server, под управлением которой работают узлы кластера.

Запуск тестов проверки кластера

PS   Test-Cluster –Node SQL01, SQL02 

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

Создание отказоустойчивого кластера

ПРИМЕЧАНИЕ. ​Если вы решили создать кластер сразу после выполнения проверки в процедуре проверки конфигурации, вы не увидите страницу "Выбор серверов". Проверенные узлы автоматически добавляются в мастер создания кластеров, так что вводить их еще раз не нужно.

Создание отказоустойчивого кластера с помощью Windows PowerShell

В приведенном ниже примере создается кластер с именем MSSQL-HA в подразделении Cluster  домена prostep.com.ua.

PS   New-Cluster -Name CN=MSSQL-HA,OU=Cluster,DC=prostep,DC=com,DC=ua -Node SQL01, SQL02  –StaticAddress  10.0.11.10, 10.0.12.10 -NoStorage 

ТРЕБОВАНИЕ 2: ​Все экземпляры сервера, на которых размещена реплика доступности для группы доступности, должны использовать одинаковые учетные записи службы SQL Server. Учетная запись компьютера самого кластера должна иметь разрешения на создание объектов компьютера

Учетные записи служб, управляемых группами 

Групповая управляемая учетная запись службы (gMSA) — это управляемая учетная запись службы для нескольких серверов. Windows управляет такой учетной записью для служб, работающих на группе серверов. Active Directory обновляет пароль групповой управляемой учетной записи службы автоматически, не перезапуская при этом службы. Службы SQL Server можно настроить для использования основного экземпляра групповой управляемой учетной записи службы. Начиная с версии SQL Server 2014, SQL Server поддерживает групповые управляемые учетные записи службы для изолированных экземпляров, а с версии SQL Server 2016 — для экземпляров отказоустойчивого кластера и для групп доступности.

Контроллеры домена (DC) требуют корневого ключа для начала создания паролей gMSA. Групповые управляемые учетные записи служб создаются только через 10 часов после создания ключа, чтобы все контроллеры доменов успели выполнить схождение своих репликаций Active Directory. Ожидание до 10 часов является мерой безопасности, чтобы предотвратить создание паролей до того, как все контроллеры домена в среде могут отвечать на запросы gMSA. Попытка использовать gMSA слишком скоро может завершиться ошибкой, когда узел gMSA пытается получить пароль, так как ключ, возможно, не был реплика добавлен ко всем контроллерам домена. Ошибки извлечения паролей gMSA также могут возникать при использовании контроллеров домена с ограниченными расписаниями реплика или при возникновении проблемы с реплика.

ПРИМЕЧАНИЕ. ​​​Удаление и повторное восстановление корневого ключа может привести к проблемам, когда старый ключ продолжает использоваться после удаления из-за кэширования ключа. Служба распространения ключей (KDC) должна быть перезапущена на всех контроллерах домена, если корневой ключ повторно создан.

Создание корневого ключа KDS:

PS   Add-KdsRootKey -EffectiveImmediately

Создание корневого ключа KDS в тестовой среде для незамедлительного использования

PS   Add-KdsRootKey -EffectiveTime ((get-date).addhours(-10))

Чтоб проверить наличие ключа​ введите следующую команду

PS   Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId

Посмотреть ключа​ можно следующей командой

PS   Get-KdsRootKey

Создание gMSA учетной записи

Чтобы создать gMSA, необходимо обновить схему Active Directory в лесу домена gMSA до Windows Server 2012

Прежде чем создать учетную запись службы gMSA, необходимо создать доменную группу и добавить в нее компьютеры, которым будет разрешено использовать пароль создаваемой gMSA

PS   New-ADGroup "MSA-SQLHOSTS" -path 'OU=Domain Groups,DC=prostep,dc=com,DC=ua' -GroupScope Global -PassThru -Verbose
PS Add-AdGroupMember -Identity ​MSA-SQLHOSTS -Members SQL01$, SQL02$

Создаем​ учетную запись gMSA

PS   New-ADServiceAccount gMSASQLSERVICE -DNSHostName SQL.prostep.com.ua -PrincipalsAllowedToRetrieveManagedPassword ​MSA-SQLHOSTS$ -KerberosEncryptionType RC4, AES128, AES256 -ServicePrincipalNames ​​MSSQLSvc/SQL01:1433, MSSQLSvc/SQL01.prostep.com.ua:1433,  MSSQLSvc/SQL02:1433, MSSQLSvc/SQL02.prostep.com.ua:1433

Периодичность смены пароля в днях (если не указано, используется значение по умолчанию 30)

Для изменения периодичности смены пароля выпоните команду:

PS   Set-ADServiceAccount gMSASQLSERVICE -ManagedPasswordIntervalInDays 60

Чтоб проверить последнее изменение пароля выпоните команду:

PS   Get-ADServiceAccount -Identity gMSASQLSERVICE-Properties passwordlastset

Для обновления состава группы, сервера добавленные в группу необходимо перезагрузить

Для проверки корректности установки gMSA выполните следующую команду:

PS   Add-WindowsFeature RSAT-AD-PowerShell
PS  Test-ADServiceAccount gMSASQLSERVICE
True

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

Посмотреть зарегистрированые SPN, выполните следующую команду

PS   setspn -L ​gMSASQLSERVICE

Зарегистрировать SPN вручную, выполните следующую команду

PS   setspn -L ​setspn -S MSSQLSvc/SQL.prostep.com.ua PROSTEP\​gMSASQLSERVICE$
PS   setspn -L ​setspn -S MSSQLSvc/SQL PROSTEP\​gMSASQLSERVICE$

Добавим разрешения на OU учетной записи компьютера кластера на добавление элемента "Компьютер​"

ПРИМЕЧАНИЕ. ​​​​Если учетная запись имени кластера отсутствует в списке, нажмите «Добавить» и добавьте ее в список.

Использование учетной записи службы в качестве имени субъекта-службы

Учетные записи служб могут быть использованы в качестве имен участников-служб. Они указываются с помощью атрибута соединения для проверки подлинности Kerberos и имеют следующие форматы:

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

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Установка экземпляра SQL Server

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

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

Установка нового экземпляра отказоустойчивого кластера SQL Server

ПРИМЕЧАНИЕ. ​​​​​После создания экземпляра отказоустойчивого кластера в него нельзя добавить компоненты. Например, невозможно добавить компонент PolyBase в существующий экземпляр отказоустойчивого кластера. Перед началом установки экземпляра отказоустойчивого кластера отметьте, какие компоненты вам нужны

Требования для выбранных компонентов показаны на правой панели. Программа установки SQL Server установит обязательный компонент, который еще не был установлен, в шаге установки, описанном ниже в данной процедуре. 
В поле, расположенном в нижней части этой страницы, можно также указать определенный каталог для общих компонентов. Чтобы изменить путь установки для общих компонентов, обновите путь в поле в нижней части диалогового окна или нажмите кнопку с многоточием, чтобы перейти к каталогу установки. Путь установки по умолчанию — C:\Program Files\Microsoft SQL Server\.

Идентификатор экземпляра — по умолчанию в качестве идентификатора экземпляра используется его имя. Используется для идентификации каталогов установки и разделов реестра для экземпляра SQL Server. Действует как для экземпляров по умолчанию, так и для именованных экземпляров. Для экземпляра по умолчанию именем и идентификатором экземпляра является «MSSQLSERVER». Чтобы задать идентификатор экземпляра, отличный от заданного по умолчанию, введите значение в поле Идентификатор экземпляра

На странице Настройка сервера — учетные записи служб укажите учетные записи входа для служб SQL Server. Набор служб, которые можно настроить на этой странице, зависит от компонентов, выбранных при установке. Можно назначить одну учетную запись входа всем службам SQL Server или настроить учетные записи служб индивидуально. Корпорация Майкрософт рекомендует настраивать учетные записи служб индивидуально, чтобы предоставить каждой из служб SQL Server только минимальные разрешения, необходимые для выполнения их задач. Укажем ранее созданного сервисного пользователя gMSASQLSERVICE в поле Account Name (SQL Server Database Engine) 

Чтобы указать одну учетную запись входа для всех учетных записей служб этого экземпляра SQL Server, укажите учетные данные в полях в нижней части страницы. 
Не используйте пустые пароли. Выбирайте надежные пароли. 
После ввода данных входа для служб SQL Server нажмите кнопку "Далее".

На вкладке "Конфигурация сервера" — Параметры сортировки для ядра СУБД и Analysis Services можно задать параметры сортировки, отличные от параметров по умолчанию.

Используйте страницу ядро СУБД конфигурации — подготовка учетной записи, чтобы указать следующее:

Завершив редактирование списка, нажмите кнопку ОК. Проверьте список администраторов в диалоговом окне конфигурации. По завершении работы со списком нажмите кнопку Далее.

На странице "Конфигурация ядра СУБД — каталоги данных" укажите каталоги установки, отличающиеся от заданных по умолчанию. Чтобы выполнить установку в каталог по умолчанию, нажмите кнопку Далее.

ПРИМЕЧАНИЕ. ​​​​​При указании каталогов установки, отличных от используемых по умолчанию, убедитесь в том, что имена папок для установки являются уникальными для этого экземпляра SQL Server. Ни один из каталогов, заданных в этом диалоговом окне, не должен совпадать с каталогами, указанными для других экземпляров SQL Server.

На странице готовности к установке показано представление параметров установки в виде дерева, заданных в программе установки. Чтобы продолжить, щелкните Установить. Программа установки вначале устанавливает требуемые компоненты для выбранных средств, затем устанавливает сами средства.

ТРЕБОВАНИЕ 3: ​​​​Все экземпляры сервера, на которых размещены реплики доступности для одной группы доступности, должны использовать одинаковые параметры сортировки SQL Server.

Параметры сортировки сервера в SQL Server

Параметры сортировки сервера указываются во время установки SQL Server. Параметры сортировки на уровне сервера по умолчанию основываются на языковом стандарте операционной системы. Например, параметры сортировки по умолчанию для систем, использующих английский язык (EN-US):  SQL_Latin1_General_CP1_CI_AS.

Изменение параметров сортировки сервера в SQL Server

ПРИМЕЧАНИЕ. ​​Вместо изменения параметров сортировки по умолчанию экземпляра SQL Server можно указать параметры сортировки по умолчанию для каждой новой базы данных, создаваемой с помощью COLLATE предложения и ALTER DATABASE инструкций CREATE DATABASE.

Изменение параметров сортировки экземпляра включает следующие шаги:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

ТРЕБОВАНИЕ 4: ​​​Включите функцию групп доступности AlwaysOn на каждом экземпляре сервера, где будет размещаться реплика доступности для любой группы доступности.

Включение функции "Группы доступности AlwaysOn"

Использование диспетчера конфигурации SQL Server:

Использование SQL Server PowerShell:

PS Install-Module -Name SqlServer -AllowClobber

PS   foreach ($node in Get-ClusterNode) {Enable-SqlAlwaysOn -ServerInstance $node -Force}
      OR
PS  Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL01\DEFAULT
PS Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL02\DEFAULT

Определите, включены ли группы доступности AlwaysOn:

PS cd SQLSERVER:\SQL\SQL01\DEFAULT
PS  SQLSERVER:\SQL\SQL01\DEFAULT> get-item . | select IsHadrEnabled 
IsHadrEnabled 
------------- 
True

Использование Transact-SQL

SELECT SERVERPROPERTY ('IsHadrEnabled');

Значение свойства сервера IsHadrEnabled указывает, может ли экземпляр SQL Server входить в группы доступности Always On:

Включение сеанса событий AlwaysOn_health:

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

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); 
GO

Создание конечных точек зеркального отображения базы данных во всех репликах:

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

Для отправки и получения сообщений между экземплярами серверов, участвующими в сеансах зеркального отображения базы данных или размещающих реплики доступности, в конечных точках зеркального отображения базы данных используется протокол TCP. Конечная точка зеркального отображения базы данных ожидает передачи данных через порт TCP с уникальным номером. 
Следующий запрос Transact-SQL создает конечную точку прослушивания с именем Hadr_endpoint для группы доступности. Он запускает конечную точку и предоставляет учетной записи службы или имени входа SQL, созданному на предыдущем шаге, разрешение на подключение.

USE [master] 
GO

CREATE LOGIN [PROSTEP\gMSASQLSERVICE$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] 
GO

CREATE ENDPOINT [​hadr_endpoint]  
            STATE=STARTED 
            AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
            FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NTLM
, ENCRYPTION = REQUIRED ALGORITHM AES) 
GO

GRANT CONNECT ON ENDPOINT::[​hadr_endpoint] TO [PROSTEP\gMSASQLSERVICE$] 
GO 

Открываем TCP/UDP-порты в брандмауэре для сервисов:

PS New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow 
PS  New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow 
PS New-NetFirewallRule -DisplayName "​​SQL Server Mirroring" -Direction Inbound -LocalPort 5022 -Protocol TCP -Action Allow

Создание и настройка группы обеспечения доступности

Так как мастер создания группы обеспечения доступности не создает группу, если не была создана резервная копия базы данных, необходимо создать резервную копию базы данных. 

В обозреватель объектов разверните узел Базы данных и щелкните правой кнопкой мыши созданную базу данных. 
Выберите "Задачи", а затем — "Создать резервную копию". 
В диалоговом окне "Создание резервной копии базы данных" нажмите кнопку "ОК", чтобы принять все параметры по умолчанию и создать резервную копию.

Создание резервной копии с помощью T-SQL

USE master 
GO

BACKUP DATABASE DB1 TO DISK = 'C:\temp\db1.bak' WITH COMPRESSION 
GO
 
BACKUP LOG DB1 TO DISK = 'C:\temp\​db1.trn' WITH COMPRESSION  
GO 

Создание группы доступности AlwaysON (с помощью мастера):

Определения уровня работоспособности базы данных:

Параметр определения уровня работоспособности базы данных группы доступности является хорошим рекомендуемым вариантом для обеспечения высокого уровня доступности баз данных. Рекомендуется настроить его для всех групп доступности. Если высокая доступность приложения зависит от нескольких баз данных, объедините их в группу доступности с включенным параметром определения работоспособности базы данных. 
Например, если параметр определения уровня работоспособности базы данных включен и SQL Server не удалось выполнить запись в файл журнала транзакций для одной из баз данных, состояние этой базы данных изменится и будет указывать на сбой. Вскоре произойдет отработка отказа группы доступности, после чего приложение сможет повторно подключиться и продолжить работу с минимальным нарушением после перехода баз данных в сетевой режим.

В журнале ошибок SQL Server (или тексте из sp_readerrorlog) будет отображаться сообщение об ошибке 41653 в случае отработки отказа группы доступности в результате проверок определения уровня работоспособности базы данных.

Отработка отказа базы данных отличается от политики гибкой отработки отказа. Определение уровня работоспособности базы данных реализует политику гибкой отработки отказа, которая настраивает пороговые значения работоспособности процесса SQL Server для политики отработки отказа. Определение уровня работоспособности базы данных настраивается с помощью параметра DB_FAILOVER, тогда как для настройки определения работоспособности процесса SQL Server используется отдельный параметр группы доступности FAILURE_CONDITION_LEVEL. Два параметра являются независимыми.

В системном DMV sys.availability_groups отображается столбец db_failover, который указывает, отключен (0) или включен (1) параметр определения уровня работоспособности базы данных.

select name, db_failover from sys.availability_groups

Включение определения уровня работоспособности базы данных

ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = ON);

ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = OFF);

Библиотека ресурсов группы доступности в WSFC-кластере выполняет проверку исправности первичной реплики путем вызова хранимой процедуры sp_server_diagnostics для экземпляра SQL Server, на котором располагается первичная реплика. sp_server_diagnostics возвращает результаты с интервалом, равным 1/3 порогового значения времени ожидания при проверке работоспособности для группы доступности. Пороговое значение времени ожидания при проверке работоспособности по умолчанию составляет 30 секунд, то есть sp_server_diagnostics возвращает сведения с интервалом в 10 секунд. Если sp_server_diagnostics работает медленно или не возвращает сведения, библиотека ресурсов ожидает, пока истечет полный интервал для описанного порогового значения, и только затем определяет, что первичная реплика не отвечает. Если первичная реплика не отвечает, инициируется автоматический переход на другой ресурс, если она поддерживается.

Уровень условий сбоя для группы доступности определяет, обеспечат ли диагностические данные и сведения о работоспособности, возвращенные командой sp_server_diagnostics , автоматический переход на другой ресурс. Уровень условия сбоя определяет, какие условия сбоя инициируют автоматический переход на другой ресурс. Существует пять уровней условий сбоя, которые варьируются от наименее ограничительного (уровень 1) до наиболее ограничительного (уровень 5). Заданный уровень включает в себя ограничения всех предыдущих уровней. Таким образом, наиболее строгий уровень 5 включает в себя менее строгие уровни ограничений с 1 по 4 и т. д.

ПРИМЕЧАНИЕ. ​​​Ни на одном из уровней условий сбоя не обнаружено поврежденных и подозрительных баз данных. Следовательно, поврежденная или подозрительная база данных (из-за ошибки оборудования, повреждения данных или по другой причине) никогда не вызывает автоматического перехода на другой ресурс.

Уровни условий сбоя описаны в следующей таблице.

Level Условие сбоя Значение Transact-SQL Значение PowerShell
1 При остановке работы сервера. Указывает, что автоматический переход на другой ресурс инициируется при возникновении одной из следующих ситуаций:

Служба SQL Server остановлена.

Аренда группы доступности для подключения к кластеру WSFC истекла, поскольку от экземпляра сервера не было получено сообщение ACK. 		1 OnServerDown
2 При отсутствии ответа от сервера. Указывает, что автоматический переход на другой ресурс инициируется при возникновении одной из следующих ситуаций:

Экземпляр SQL Server не подключается к кластеру, а определяемый пользователем порог времени ожидания проверки исправности для группы доступности превышен.

Реплика доступности находится в неисправном состоянии. 		2 OnServerUnresponsive
3 В случае критической ошибки сервера. Указывает, что автоматическая отработка отказа инициируется при критических SQL Server внутренних ошибках, таких как потерянные спин-блокировки, серьезные нарушения доступа для записи или слишком большое количество дампов памяти, созданных за короткий период времени.

Это уровень, заданный по умолчанию. 		3 OnCriticalServerError
4 В случае ошибки сервера средней значимости. Указывает, что автоматический переход на другой ресурс инициируется в случае появления умеренных внутренних ошибок SQL Server , например устойчивое состояние нехватки памяти в пуле внутренних ресурсов SQL Server . 4 OnModerateServerError
5 При любых подходящих условиях сбоя. Указывает, что автоматический переход на другой ресурс инициируется при любом удовлетворяющем условиям состоянии сбоя, включая:

Обнаружение взаимоблокировки планировщика.

Обнаружение неразрешимой взаимоблокировки.

Это наиболее ограничительный уровень. 		5 OnAnyQualifiedFailureConditions

ПРИМЕЧАНИЕ. ​​​​Отсутствие ответа экземпляра SQL Server на клиентские запросы не является существенным для групп доступности.

Настройка гибкой политики отработки отказа

...WITH (​FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 })

ALTER AVAILABILITY GROUP AGMSSQL SET (FAILURE_CONDITION_LEVEL = 1);

Следующая инструкция Transact-SQL изменяет пороговое значение времени ожидания проверки работоспособности существующей группы доступности, AGMSSQL, на значение 60 000 миллисекунд (одна минута).

ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 60000);

Поддержка DTC для каждой базы данных:

В распределенной транзакции клиентские приложения работают с координатором распределенных транзакций Майкрософт (MS DTC или DTC), что позволяет обеспечить согласованность транзакций между несколькими источниками данных. DTC — это служба, доступная в поддерживаемых операционных системах Windows Server. Для распределенных транзакций DTC выступает как координатор. Экземпляр SQL Server, как правило, служит диспетчером ресурсов. У каждой базы данных, которая не входит в группу доступности, должен быть собственный диспетчер ресурсов.

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

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

Группу доступности для распределенных транзакций можно изменить в SQL Server 2016 (13.x) или более поздней версии. Чтобы создать группу доступности для распределенных транзакций, включите в определении группы доступности DTC_SUPPORT = PER_DB

...WITH (​​DTC_SUPPORT = PER_DB)

Группу доступности для распределенных транзакций можно изменить следующим запросом

ALTER AVAILABILITY GROUP AGMSSQL SET (DTC_SUPPORT = PER_DB);

ПРИМЕЧАНИЕ. ​​​​​Начиная с версии SQL Server 2016 (13.x); с пакетом обновления 2 (SP2), группу доступности для распределенных транзакций можно изменять. В версиях SQL Server 2016 (13.x); до пакета обновления 2 (SP2) потребуется удалить и вновь создать группу доступности с параметром DTC_SUPPORT = PER_DB

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

ALTER AVAILABILITY GROUP AGMSSQL SET (DTC_SUPPORT = NONE);

Распределенная транзакция охватывает две базы данных или больше. Как диспетчер транзакций, DTC координирует транзакцию между экземплярами SQL Server и другими источниками данных. В качестве диспетчера ресурсов может выступать любой экземпляр компонента Database Engine SQL Server. После настройки группы доступности в DTC_SUPPORT = PER_DB в качестве диспетчеров ресурсов могут выступать и базы данных.

Транзакция с двумя или несколькими базами данных в отдельном экземпляре компонента Database Engine, по сути, является распределенной транзакцией. Экземпляр управляет распределенной транзакцией на внутреннем уровне, для пользователя она действует как локальная транзакция. Если базы данных находятся в группе доступности, настроенной с DTC_SUPPORT = PER_DB (даже в пределах одного экземпляра SQL Server), SQL Server 2017 (14.x); передает все межбазовые транзакции в DTC.

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

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

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

Разберем​ подробнее, как приложение взаимодействует с DTC для выполнения распределенных транзакций.

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

Для участия в распределенных транзакциях экземпляр SQL Server присоединяется к DTC. Обычно экземпляр SQL Server присоединяется к DTC на локальном сервере. Каждый экземпляр SQL Server создает диспетчер ресурсов с уникальным идентификатором диспетчера ресурсов (RMID) и регистрирует его в DTC. В конфигурации по умолчанию все базы данных в экземпляре SQL Server используют один и тот же RMID. 
Если база данных находится в группе доступности, копию этой базы данных, доступную для чтения и записи, либо ее первичную реплику можно переместить в другой экземпляр SQL Server. Чтобы обеспечить поддержку распределенных транзакций во время такого перемещения, каждая база данных должна выступать как отдельный диспетчер ресурсов и иметь уникальный номер RMID. Если группа доступности имеет DTC_SUPPORT = PER_DB, SQL Server создает диспетчер ресурсов для каждой базы данных и регистрируется в DTC под уникальным номером RMID. В этой конфигурации базы данных является диспетчером ресурсов для транзакций DTC.

Управление неразрешенными транзакциями:

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

Если в перечисленных выше случаях после отработки отказа первичная реплика переходит в новый экземпляр SQL Server, этот экземпляр пытается связаться с DTC, чтобы определить результат транзакции. При этом DTC не может вернуть результат, поскольку RMID базы данных, используемый для получения результата сомнительной транзакции во время восстановления, ранее не был присоединен. В связи с этим база данных переходит в состояние SUSPECT

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

Microsoft Distributed Transaction Coordinator (MS DTC)
failed to reenlist citing that the database RMID does
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]
associated with the transaction. Please manually resolve
the transaction.

SQL Server detected a DTC/KTM in-doubt transaction with UOW
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it
following the guideline for Troubleshooting DTC Transactions.

Приведенный выше пример показывает, что DTC не может повторно присоединить базу данных из новой первичной реплики к транзакции, созданной после отработки отказа. Экземпляр SQL Server не может определить результат распределенной транзакции и отмечает базу данных как сомнительную. Транзакция помечается как единица работы (UOW) и обозначается с помощью GUID. Чтобы восстановить базу данных, зафиксируйте транзакцию или выполните отказ вручную.

ПРИМЕЧАНИЕ. ​​​​​​Ручная фиксация или откат транзакции могут негативно сказаться на приложении. Убедитесь, что данная операция соответствует требованиям вашего приложения.

Выполните только один из представленных ниже скриптов.

KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT

KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK

После фиксации или отката транзакции базу данных можно перевести в режим онлайн, используя ALTER DATABASE. Обновите и выполните следующий скрипт, указав имя базы данных вместо имени сомнительной базы данных:

ALTER DATABASE [DB1] SET ONLINE

Продолжим Создание группы доступности AlwaysON(с помощью мастера)

Для каждой из перечисленных баз данных столбец Размер отображает размер базы данных, если он известен. Столбец состояния указывает, соответствует ли данная база данных предварительным требованиям для баз данных доступности. Если необходимые условия не выполняются, краткое описание состояния указывает на причину того, почему эта база данных не может использоваться. Например, если для нее не используется модель полного восстановления. Для получения дополнительны сведений щелкните описание состояния.

После изменения базы данных для обеспечения соответствия требованиям щелкните Обновить , чтобы обновить сетку баз данных.

Если база данных содержит главный ключ базы данных, введите пароль для него в столбце Пароль .

На странице Выбор реплик укажите и настройте одну или несколько реплик для новой группы доступности. Страница содержит четыре вкладки. Эти вкладки представлены в следующей таблице.

Вкладка Краткое описание
Реплики Эта вкладка позволяет указать каждый экземпляр SQL Server, на котором будет размещен дополнительный реплика. Обратите внимание, что первичная реплика должна быть размещена на экземпляре сервера, с которым в данный момент установлено соединение.
Конечные точки Эту вкладку можно использовать для проверки существующих конечных точек зеркального отображения баз данных, а также для их автоматического создания в случае, если они отсутствуют на экземпляре сервера, служба которого использует проверку подлинности Windows.

Примечание. Если какой-либо экземпляр сервера запускается под учетной записью, которая не является учетной записью пользователя домена, то необходимо вручную перейти на свой экземпляр сервера, прежде чем продолжать работу с мастером. 
Параметры резервного копирования Эту вкладку можно использовать для задания настроек резервного копирования для группы доступности в целом, а также для задания приоритетов резервного копирования для отдельных реплик доступности.
Прослушиватель Эта вкладка используется для создания прослушивателя группы доступности. По умолчанию мастер не создает прослушиватель.
Маршрутизация только для чтения Эта вкладка относится к способности SQL Server направлять соответствующие запросы на подключение только для чтения к доступной вторичной реплика AlwaysON (т. е. реплика, которая настроена для разрешения рабочих нагрузок только для чтения при выполнении под вторичной ролью). Для поддержки маршрутизации только для чтения группа доступности должна иметь прослушиватель группы доступности.

Режимы доступности:

Режим доступности — это свойство каждой реплики доступности. Режим доступности определяет, ждет ли первичная реплика перед фиксацией транзакций для базы данных, чтобы данная вторичная реплика записала записи журнала транзакций на диск (записала журнал на диск). Группы доступности Always On поддерживают два режима доступности: режим асинхронной фиксации и режим синхронной фиксации. 

Asynchronous-commit mode 

Реплика доступности, которая использует этот режим доступности, называется репликой асинхронной фиксации. В режиме асинхронной фиксации первичная реплика фиксирует транзакции, не ожидая подтверждения записи транзакции в журнал от вторичных реплик асинхронной фиксации. Режим асинхронной фиксации минимизирует задержку транзакций в базах данных-получателях, но позволяет им не успевать за базами данных-источниками, что создает риск возможной потери данных.

Synchronous-commit mode 

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

Настройка прослушивателя для группы доступности Always On:

На странице Выбор реплик перейдите на вкладку "Прослушиватель".

Для каждого прослушивателя группы доступности необходимо имя DNS-узла, уникальное в домене и в NetBIOS. Имя DNS является значением типа string. Это имя может содержать только буквы, цифры, тире и дефисы (-), а также знаки подчеркивания (_) в любом порядке. В именах узлов DNS учитывается регистр. Максимальная длина составляет 63 символа, однако в среде SQL Server Management Studioможно указать длину не более 15 символов.

Указывает имя узла DNS для прослушивателя группы доступности. Имя DNS является строкой и должно быть уникальным в домене и в NetBIOS. Это имя может содержать только буквы, цифры, дефисы (-) и знаки подчеркивания (_) в любом порядке. В именах узлов DNS учитывается регистр. Максимальная длина составляет 15 символов.

Указывает TCP-протокол, используемый прослушивателем

Прослушиватель будет использовать один или несколько статических IP-адресов. Дополнительные IP-адреса являются необязательными. Чтобы создать прослушиватель группы доступности для нескольких подсетей, в конфигурации прослушивателя необходимо задать по одному статическому IP-адресу для каждой подсети.
Если выбрать Статический IP-адрес , под полем Сетевой режим появится сетка подсети. В этой сетке отображаются сведения о каждой подсети, к которой может получить доступ прослушиватель этой группы доступности. Эта сетка остается пустой до тех пор, пока не будет добавлен статический IP-адрес путем нажатия кнопки Добавить.

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

Выберите этот режим, если ваша среда удовлетворяет требованиям для автоматического запуска начальной синхронизации данных

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

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

На этапе проверки мастер выполнит тест, чтобы убедиться в допустимости заданной сетевой папки. В ходе теста на первичной реплике будет создана база данных с именем "BackupLocDb_", за которым следует идентификатор GUID, выполнено резервное копирование в указанную сетевую папку, а затем — восстановление из резервной копии на вторичных репликах. Рекомендуется удалить эту базу данных вместе с ее журналом резервного копирования и файлом резервной копии в случае, если это не удалось сделать мастеру.

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

Выберите этот параметр, если вы хотите использовать собственные резервные копии баз данных-источников и их журналов.

ПРИМЕЧАНИЕ. ​Если учетная запись службы SQL Server экземпляра сервера, на котором будет размещаться новая реплика доступности, еще не существует в качестве имени входа, мастер создания группы доступности должен создать имя входа. На странице Сводка мастер показывает сведения об имени входа, которое будет создано. Если нажать кнопку Готово, мастер создаст это имя входа для учетной записи SQL Server и предоставит ему разрешение CONNECT.

Если параметры выбраны правильно, можно нажать кнопку Скрипт, чтобы создать скрипт шагов, которые будут выполняться мастером. Теперь нажмите кнопку Готово, чтобы создать и настроить новую группу доступности.

По завершении работы мастера нажмите кнопку Закрыть, чтобы выйти из него.

Создание группы доступности AlwaysON (с T-SQL)

Переключение окна редактора запросов ядра СУБД в режим SQLCMD 

CREATE AVAILABILITY GROUP [AGMSSQL]     
        WITH ( AUTOMATED_BACKUP_PREFERENCE = SECONDARY, 
        FAILURE_CONDITION_LEVEL  = 4, 
        DB_FAILOVER  = ON, 
        DTC_SUPPORT  = PER_DB, 
        REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0, 
        CLUSTER_TYPE = WSFC 
        )   
FOR  
        DATABASE DB1, DB2 
REPLICA ON  
        N'SQL01' WITH  
        ( 
        ENDPOINT_URL = N'TCP://SQL01.prostep.com.ua:5022',  
        FAILOVER_MODE = AUTOMATIC,  
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
        BACKUP_PRIORITY = 50,  
        SEEDING_MODE = AUTOMATIC,  
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO) 
        ), 
        N'SQL02' WITH  
        ( 
        ENDPOINT_URL = N'TCP://SQL02.prostep.com.ua:5022',  
        FAILOVER_MODE = AUTOMATIC,  
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
        BACKUP_PRIORITY = 50,  
        SEEDING_MODE = AUTOMATIC,  
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO) 
        ); 
GO   
ALTER AVAILABILITY GROUP [AGMSSQL]   
       ADD LISTENER 'SQL' ( WITH IP ( (N'10.0.11.15', N'255.255.255.0'),(N'10.0.12.15', N'255.255.255.0') ) , PORT = 1433 );  
GO
 :Connect SQL02 
ALTER AVAILABILITY GROUP [AGMSSQL] JOIN; 
GO 
ALTER AVAILABILITY GROUP [AGMSSQL] GRANT CREATE ANY DATABASE; 
GO 

Кластер AlwasOn​ готов к использованию

Проверка группы доступности AlwaysON

Запуск панели мониторинга AlwaysOn:

  1. В обозреватель объектов подключитесь к экземпляру SQL Server, на котором требуется запустить панель мониторинга AlwaysOn.
  2. Разверните узел Высокий уровень доступности AlwaysOn, правой кнопкой мыши щелкните узел Группы доступности, затем пункт Показать панель мониторинга.

Изменение параметров панели мониторинга AlwaysOn:

Диалоговое окно "Параметры SQL Server Management Studio" можно использовать для настройки поведения панели мониторинга AlwaysOn SQL Server для автоматического обновления и включения автоматической политики AlwaysOn.

  1. В меню Сервис щелкните пункт Параметры.
  2. Чтобы панель мониторинга обновлялась автоматически, в диалоговом окне Параметры выберите Включить автоматическое обновление, введите интервал обновления в секундах, после чего введите число попыток подключения.
  3. Чтобы включить определяемую пользователем политику, выберите Включить пользовательские политики AlwaysOn

Подробные сведения о группе доступности:

Для группы доступности, выбранной на экране кратких сведений, отображаются следующие подробные сведения:

Состояние группы доступности
Отображает состояние работоспособности этой группы доступности.

Первичный экземпляр
Имя экземпляра сервера, на котором размещена первичная реплика группы доступности.

Режим отработки отказа
Отображает тип режима отработки отказа, который настроен для реплики. Возможные значения режима отработки отказа:

Состояние кластера
Имя и состояние кластера, для которого экземпляр подключенного сервера и группа доступности являются узлом-участником.

Использование мастера отработки отказа группой доступности:

На данном этапе кластер создан и протестирован