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

Статья
04/26/2023

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

Группу доступности AlwaysOn SQL Server для рабочих нагрузок чтения и масштабирования можно настроить в Windows. Существует два типа архитектур для групп доступности:

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

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

Примечание.

В группу доступности с CLUSTER_TYPE = NONE могут входить реплики, размещенные на различных платформах операционных систем. Она не поддерживает высокий уровень доступности. Сведения об ОС Linux см. в статье Настройка группы доступности SQL Server для чтения и масштабирования в Linux.

Необходимые компоненты

Перед созданием группы доступности необходимо выполнить следующие действия:

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

Включение групп доступности AlwaysOn и перезапуск mssql-server

Примечание.

В приведенной ниже команде используются командлеты из модуля sqlserver, опубликованного в коллекции PowerShell. Этот модуль можно установить с помощью команды Install-Module.

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

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

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

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

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

Дополнительные сведения о сеансах расширенных событий см. в статье Расширенные события для групп доступности AlwaysOn.

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

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

Организация сервиса

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

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

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

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Проверка подлинности на основе сертификата

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

На этом этапе первичная реплика SQL Server имеет сертификат в файле c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer и закрытый ключ в файле c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk. Скопируйте эти файлы в одно и то же место на всех серверах, где будут размещаться реплики доступности.

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

Создание сертификата на серверах-получателях

Следующий сценарий Transact-SQL создает главный ключ и сертификат из резервной копии, созданной в первичной реплике SQL Server. Эта команда также разрешает пользователям доступ к сертификату. Обновите сценарий, задав надежные пароли. Для расшифровки используется тот же пароль, что и при создании PVK-файла в предыдущем шаге. Чтобы создать сертификат, выполните следующий сценарий на всех вторичных репликах:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

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

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

Следующий запрос Transact-SQL создает конечную точку прослушивания с именем Hadr_endpoint для группы доступности. Он запускает конечную точку и предоставляет учетной записи службы или имени входа SQL, созданному на предыдущем шаге, разрешение на подключение. Перед выполнением данного сценария замените значения между **< ... >**. При необходимости можно включить IP-адрес LISTENER_IP = (0.0.0.0). IP-адрес прослушивателя должен быть IPv4-адресом. Также можно использовать 0.0.0.0.

Обновите следующий сценарий Transact-SQL для среды на всех экземплярах SQL Server:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

TCP-порт в брандмауэре должен быть открыт для порта прослушивателя.

Дополнительные сведения см. в статье Конечная точка зеркального отображения базы данных (SQL Server).

Создание группы доступности

Создайте группу доступности. Задайте CLUSTER_TYPE = NONE. Кроме того, задайте для каждой реплики FAILOVER_MODE = NONE. Клиентские приложения с рабочими нагрузками аналитики и отчетности могут подключаться к базам данных — получателям напрямую. Кроме того, можно создать список маршрутизации только для чтения. Подключения к первичной реплике будут переадресовывать запросы на подключение для чтения к каждой из содержащихся в нем вторичных реплик по принципу циклического перебора.

Приведенный ниже скрипт Transact-SQL создает группу доступности с именем ag1. Он настраивает реплики группы доступности с параметром SEEDING_MODE = AUTOMATIC. Если этот параметр задан, SQL Server будет автоматически создавать базы данных на каждом сервере-получателе после его добавления в группу доступности.

Обновите следующий сценарий для своей среды. Замените значения <node1> и <node2> на имена экземпляров SQL Server, где размещаются реплики. Замените значение <5022> на порт, заданный для конечной точки. Выполните следующий сценарий Transact-SQL на первичной реплике SQL Server:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Присоединение вторичных экземпляров SQL Server к группе доступности

Приведенный ниже скрипт Transact-SQL присоединяет сервер к группе доступности с именем ag1. Обновите сценарий для своей среды. Для присоединения к группе доступности в каждой вторичной реплике SQL Server выполните следующий скрипт Transact-SQL:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

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

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

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

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

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Убедитесь, что база данных создана на вторичных серверах.

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

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

У этой группы доступности нет конфигурации высокого уровня доступности. Если вам требуется высокий уровень доступности, следуйте инструкциям в статье Настройка группы доступности AlwaysOn для SQL Server в Linux или Создание и настройка групп доступности в Windows.

Подключение к вторичным репликам только для чтения

Подключаться к вторичным репликам только для чтения можно двумя способами.

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

Отработка отказа первичной реплики в группе доступности для чтения и масштабирования

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

Существует два способа отработки отказа первичной реплики в группе доступности с типом кластера NONE.

Переход на другой ресурс вручную без потери данных
Принудительный переход на другой ресурс вручную с потерей данных

Переход на другой ресурс вручную без потери данных

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

Чтобы перейти на другой ресурс вручную без потери данных, выполните следующие действия.

Сделайте текущую основную и целевую вторичную реплику SYNCHRONOUS_COMMIT.

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Чтобы определить, что активные транзакции фиксируются в первичной реплике и по меньшей мере в одной синхронной вторичной реплике, выполните следующий запрос:
```
SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 
```
Вторичная реплика синхронизируется, если synchronization_state_desc имеет значение SYNCHRONIZED.
Обновите REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT до 1.

Следующий скрипт задает для REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT значение 1 в группе доступности ag1. Перед запуском скрипта замените ag1 именем группы доступности.
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
Этот параметр означает, что все активные транзакции фиксируются на первичной реплике и по меньшей мере на одной синхронной вторичной реплике.

Примечание.

Этот параметр не относится к отработке отказа и должен быть задан в зависимости от требований среды.
Задайте первичную реплику и вторичные реплики, не участвующие в отработке отказа в автономном режиме, чтобы подготовиться к изменению роли:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Повысьте уровень целевой вторичной реплики до первичной.
```
ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
```
Обновите роль старых первичных и других вторичных файлов SECONDARY, чтобы выполнить следующую команду в экземпляре SQL Server, на котором размещена старая первичная реплика:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
Примечание.

Для удаления группы доступности используйте DROP AVAILABILITY GROUP. Для группы доступности, созданной с типом кластера NONE или EXTERNAL, выполните команду на всех репликах, входящих в группу доступности.
Возобновите перемещение данных, выполните следующую команду для каждой базы данных в группе доступности на экземпляре SQL Server, на котором размещена первичная реплика:
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
Повторно создайте прослушиватель, созданный для масштабирования для чтения, который не управляется диспетчером кластеров. Если исходный прослушиватель указывает на старую основную реплику, удалите его и создайте заново, чтобы он указывал на новую первичную реплику.

Принудительный переход на другой ресурс вручную с потерей данных

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

Для принудительной отработки отказа вручную с потерей данных с первичной реплики N1 на вторичную реплику N2 выполните следующие действия.

На вторичной реплике (N2) инициируйте принудительную отработку отказа.
```
ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
```
На новой первичной реплике (N2) удалите исходную первичную реплику (N1).
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
Убедитесь, что весь трафик приложения направляется на прослушиватель и (или) новую первичную реплику.
Если исходная первичная реплика (N1) переходит в сетевой режим, немедленно переведите группу доступности AGRScale в автономный режим на исходной первичной реплике (N1).
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Если имеются данные или несинхронизированные изменения, сохраните эти данные с помощью резервного копирования или других возможностей репликации данных в соответствии с вашими бизнес-потребностями.
Затем удалите группу доступности из исходной первичной реплики (N1).
```
DROP AVAILABILITY GROUP [AGRScale];
```
Удалите базу данных группы доступности на исходной первичной реплике (N1).
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(Необязательно) При необходимости можно добавить N1 обратно в группу доступности AGRScale в качестве новой вторичной реплики.

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

Поделиться через

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

Необходимые компоненты

Включение групп доступности AlwaysOn и перезапуск mssql-server

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

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

Организация сервиса

Проверка подлинности на основе сертификата

Создание сертификата на серверах-получателях

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

Создание группы доступности

Присоединение вторичных экземпляров SQL Server к группе доступности

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

Убедитесь, что база данных создана на вторичных серверах.

Подключение к вторичным репликам только для чтения

Отработка отказа первичной реплики в группе доступности для чтения и масштабирования

Переход на другой ресурс вручную без потери данных

Принудительный переход на другой ресурс вручную с потерей данных

Следующие шаги

Обратная связь

Дополнительные ресурсы

Поделиться через

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

Необходимые компоненты

Включение групп доступности AlwaysOn и перезапуск mssql-server

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

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

Организация сервиса

Проверка подлинности имени входа SQL

Проверка подлинности на основе сертификата

Создание сертификата на серверах-получателях

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

Создание группы доступности

Присоединение вторичных экземпляров SQL Server к группе доступности

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

Убедитесь, что база данных создана на вторичных серверах.

Подключение к вторичным репликам только для чтения

Отработка отказа первичной реплики в группе доступности для чтения и масштабирования

Переход на другой ресурс вручную без потери данных

Принудительный переход на другой ресурс вручную с потерей данных

Следующие шаги

Обратная связь

Дополнительные ресурсы