Настройка групп доступности Always On в SQL Server

Настройка групп доступности Always On в SQL Server

В этой статье мы рассмотрим пошаговую установку и настройку групп доступности Always On в SQL Server в Windows Server 2019, рассмотрим сценарии отработки отказов и ряд других смежных вопросов.

Always On Availability Groups” или “Группы доступности Always On” это технология для обеспечения высокой доступности в SQL Server. Always On появились в релизе Microsoft SQL Server 2012.

Особенности групп доступности Always On в SQL Server

Для чего могут использоваться группы доступности SQL Server?

  • Высокая доступность MS SQL и автоматическая отработка отказа;
  • Балансировка нагрузки select запросов между узлами (вторичные реплики могут быть доступны для чтения);
  • Резервное копирование с вторичных реплик;
  • Избыточность данных. Каждая реплика хранит копии баз данных группы доступности.

Always On работает на платформе Windows Server Failover Cluster (WSFC). WSFC обеспечивает мониторинг узлов участвующих в группе доступности и может осуществлять автоматическую отработку отказа посредством голосования между узлами. Начиная с MS SQL Server 2017 появилась возможность использовать Always On без WSFC, в том числе на Linux системах. При построении кластера на Linux можно использовать Pacemaker как альтернативу WSFC.

Always On доступен в Standard редакции, но с некоторыми ограничениями:

  • Лимит на 2 реплики (основную и вторичную);
  • Вторичная реплика не может быть использована для read доступа;
  • Вторичная реплика не может быть использована для резервного копирования MS SQL;
  • Поддержка только 1 базы данных на группу доступности.

В редакции Enterprise ограничений нет.

Разберемся в терминологии:

  • Группу доступности Always ON – это набор реплик и баз данных;
  • Реплика – это экземпляр SQL Server находящийся в группе доступности. Реплика может быть основная (primary) и вторичная (secondary). Каждая реплика может содержать одну или более баз данных.

В основе Always On лежит WSFC. Каждый узел группы доступности должен быть членом отказоустойчивого кластера Windows. Каждый экземпляр SQL Server может иметь несколько групп доступности. В каждой группе доступности может быть до 8 вторичных реплик.

При отказе основой реплики, кластер проголосует за новую основную реплику и Always On переведёт одну из вторичных реплик в статус основной. Так как при работе с Always On пользователи соединяются с прослушивателем кластера (или Listener, то есть специальный IP адрес кластера и соответствующее ему DNS имя), то возможность выполнять write запросы полностью восстановится. Прослушиватель также отвечает за балансировку select запросов между вторичными репликами.

Настройка Windows Server Failover Cluster для Always On

Прежде всего нам нужно настроить отказоустойчивый кластер на всех узлах, которые будут участвовать в Always On.

Моя конфигурация:

  • 2 виртуальных машины на Hyper-V с Windows Server 2019;
  • 2 экземпляра SQL Server 2019 редакции Enterprise;
  • Hostname узлов – testnode1 и testnode2. Имя экземпляров node1 и node2.

В Server Manager добавляем роль Failover Clustering, или установите компонент с помощью PowerShell:

Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools

установка failover clustering в windows server 2019

Установка автоматическая, ничего настраивать пока не нужно. После окончания установки запустите оснастку Failover Cluster Manager (FailoverClusters.SnapInHelper.msc).

оснастка Failover Cluster Manager

Создаём новый кластер.

создать новый кластер WSFC

Добавляем имена серверов, которые будут участвовать в кластере.

добавить сервера в кластер

Дальше мастер предлагает пройти тесты. Не отказываемся, выбираем первый пункт.

валидация кластера

Указываем имя кластера, выбираем сеть и IP адрес кластера. Имя кластера автоматически появится в DNS, прописывать его специально не нужно. В моём случае имя кластера – ClusterAG.

ip адрес и имя кластера

Убираем чебокс “Add all eligible storage to the cluster”, так как диски мы сможем добавить позже.

Add all eligible storage to the cluster

Узлов в кластере всего 2, поэтому необходимо настроить Cluster Quorum. Кворум кластера — это “решающий голос”. Например, если один из узлов кластера становится недоступен, кластеру необходимо определить какие узлы на самом деле доступны и могут видеть друг друга. Кворум нужен для согласованности кластера (Cluster -> More Actions -> Configure Cluster Quorum Settings).

настройка кворума для кластера

Выберите тип кворума со свидетелем (quorum witness).

quorum witness

Затем выбираем тип свидетеля – сетевая папка (file share witness).

настройка файлового свидетеля file share witness

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

путь к сетевой папке свидетелю

При настройке кластера вы можете получить ошибку:

There was an error configuring the file share witness. Unable to save property changes for File Share Witness. The system cannot find the file specified.

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

There was an error configuring the file share witness. Unable to save property changes for File Share Witness. The system cannot find the file specified

На этом базовая конфигурация кластера закончена. Убедимся, что DNS кластера прописан и отдаёт правильный IP

проверка доступности кластерного имени

Настройка Always On в MS SQL Server

После стандартной установки экземпляра SQL Server вы можете включить и настроить группы доступности Always On. Их нужно включить в SQL Server Configuration Manager в свойствах экземпляра. Как видно на скриншоте, SQL Server уже определил, что он является участником кластера WSFC. Поставьте чекбокс “Enable Always On Availability Groups” и перезагрузите службу экземпляра MSSQL. Выполните те же действия на втором экземпляре.

включить Always On Availability Groups

Совет.. Перед настройкой Always On убедитесь, что службы SQL Server работают не из-под локального аккаунта системы. Рекомендуется использовать Group Managed Service Accounts или обычный доменный аккаунт. В противном случае вы не сможете завершить настройку Always On.

В SQL Server Management Studio щелкните по узлу “Always On High Availability” и запустите мастер настройки группы доступности (New Availability Group Wizard).

мастер создания SQL Always On - New Availability Group Wizard

Укажите имя группы доступности Always On и выберите опцию “Database Level Health Detection”. С этой опцией Always On сможет определять, когда база данных находится в нездоровом состоянии.

Database Level Health Detection

Выберите базы данных SQL Server, которые будут участвовать в группе доступности Always On.

выбрать базы данных MS SQL для добавления в группу доступности always on

Нажмите “Add Replica…” и подключитесь к второму серверу SQL. Таким образом можно добавить до 8 серверов.

  • Initial Role – роль реплики на момент создания группы. Может быть Primary и Secondary;
  • Automatic Failover – если база данных станет недоступна, Always On переведёт primary роль на другую реплику. Отмечаем чекбокс;
  • Availability Mode – возможно выбрать Synchronous Commit или Asynchronous Commit. При выборе синхронного режима, транзакции, поступающие на primary реплику, будут отправлены на все остальные вторичные реплики с синхронным режимом. Primary реплика завершит транзакцию только после того, как реплики запишут транзакцию на диск. Таким образом исключается возможность потери данных при сбое primary реплики. При асинхронном режиме основная реплика сразу записывает изменения, не дожидаясь ответа от вторичных реплик;
  • Readable Secondary – параметр задающий возможность делать select запросы к вторичным репликам. При значении yes, клиенты даже при соединении без ApplicationIntent=readonly смогут получить read-only доступ;
  • Required synchronized secondaries to commit – число синхронизированных вторичных реплик для завершения транзакции. Нужно выставлять в зависимости от количества реплик, я поставлю 1. Имейте в виду, что, если вторичных синхронизированных реплик станет меньше указанного числа (например, при аварии), базы данных группы доступности станут недоступны даже для чтения.

настройка реплик always on

Вкладку Endpoints не трогаем.

На вкладке Backup Preferences можно выбрать откуда будут делаться бекапы. Оставляем всё по умолчанию – Prefer Secondary.

Prefer Secondary для резевного копирования

Указываем имя слушателя группы доступности (availability group listener), порт и IP адрес.

availability group listener - настройка параметров слушателя для группы always on sql

Вкладку Read-Only Routing оставляем без изменений.

Выбираем каким образом будут синхронизироваться реплики. Я оставляю первый пункт – автоматическую синхронизацию (Automatic seeding).

sql server always on - Automatic seeding

После этого ваши настройки должны пройти валидацию. Если ошибок нет, нажмите Finish для применения изменений.

В моём случае все тесты прошли успешно, но после установки на шаге Results, мастер сообщил об ошибке при создании слушателя группы доступности. В логах кластера была такая ошибка:

Cluster network name resource failed to create its associated computer object in domain.

ошибка Cluster network name resource failed to create its associated computer object in domain.

Это означает, что у кластера недостаточно прав для создания слушателя. В документации написано, что достаточно дать разрешение на создание объектов типа “компьютер” объекту вашего кластера. Проще всего это сделать через делегирование полномочий в AD (или, быстрый но плохой вариант — временно добавить объект CLUSTERAG$ в группу Domain Admins).

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

Sql server добавить listener

Укажите IP адрес, порт и DNS имя слушателя.

параметры availability group listener: имя, TCP порт, IP адрес

Проверьте, что Listener появился во разделе доступных слушателей группы Always On.

always on Listener

На этом базовая настройка группы доступности Always On закончена.

Always On: проверка работы, автоматическая отработка отказа

Посмотрим на панель мониторинга групп доступности (Show Dashboard).

always on dashboard

Все OK, группа доступности создана и работает.

диагностика always on - запушена и работает

Попробуем перевести основную роль на экземпляр node2 в ручном режиме. Щелкните ПКМ по группе доступности и выберите Failover.

failover - смена активной копии SQL в always on

Стоит обратить внимание на пункт Failover Readiness. Значение No data loss значит, что потеря данных при переходе исключена.

sql server No data loss

Соединяемся с node2.

подключение к реплике ms sql server

Жмём Finish.

мастер failover

Проверяем, что node2 стал основной репликой в группе доступности (Primary Instance).

изменилась Primary Instance

Убедимся, что слушатель работает как надо. В SSMS укажите DNS имя слушателе и порт через запятую:
ag1-listener-1,1445

SQL Server подключение к листенеру Always on

Сделаем простые insert, select и update запросы в нашу базу SQL Server.

тестовый sql запрос в always on

Теперь проверим автоматическую отработку отказа основной реплики. Просто завершите процесс sqlservr.exe на TESTNODE2.

завершить процесс sqlserver.exe

Проверяем состояние группы доступности на оставшемся узле – TESTNODE1NODE1.

сбой одного из узлов always on

Кластер автоматически перевёл статус реплики testnode1node1 в primary, так как testnode2node2 стал недоступен.

Проверим состояние слушателя, потому что соединения клиентов будут поступать именно на него.

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

Unable to access database 'TestDatabase' because it lacks a quorum of nodes for high availability. Try the operation again later.

Unable to access database 'TestDatabase' because it lacks a quorum of nodes for high availability. Try the operation again later

Эта ошибка возникла из-за параметра “Required synchronized secondaries to commit”. Так как при настройке мы выставляли это значение в 1, Always On не даёт подключиться к базе данных, потому что у нас осталась всего одна primary реплика.

уменьшить значение Required synchronized secondaries to commit

Установим это значение в 0 и попробуем снова.

тестовый sql запрос, проверка always on

Включаем testnode2 и проверяем статус группы.

кластер always on sql server собран

Статус Primary реплики остался у testnode1, а testnode2 стал вторичной репликой. Данные, которые мы меняли на testnode1 при выключенной testnode2 успешно синхронизировались после включения машины.

На этом тестирование закончено. Мы убедились всё работает корректно и при критическом сбое данные останутся доступны для read/write доступа.

Группы доступности Always On достаточно просты в настройке. Если перед вами стоит задача построить отказоустойчивое решение на базе SQL Server, то группы доступности отлично справятся с этой задачей.

С выпуском SQL Server 2017 и SQL Server 2019 в SQL Server Management Studio 18.x появились настройки Always On, которые раньше были доступны только через T-SQL, поэтому рекомендуется пользоваться последней версией SSMS.

Windows Server 2019
Настройка групп доступности Always On в SQL Server