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

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

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

Существует два типа архитектур для AG. В архитектуре для высокого уровня доступности используется диспетчер кластера, позволяющий обеспечить улучшенную непрерывность бизнес-процессов. Сведения о создании архитектуры с высоким уровнем доступности см. в статье Настройка группы доступности Always On SQL Server для обеспечения высокой доступности в Linux.

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

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

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

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

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

1. Обновите имя компьютера для каждого хоста.

   Каждое имя экземпляра SQL Server должно быть следующим:

   • 15 символов или меньше.
   • Уникальный в рамках сети.

   Чтобы указать имя компьютера, измените файл /etc/hostname. Следующий скрипт позволяет изменить /etc/hostname с помощью vi:

   sudo vi /etc/hostname
   

2. Настройте файл hosts.

   Примечание.

   Если имена узлов зарегистрированы с их IP-адресами на DNS-сервере, указанные ниже действия выполнять не нужно. Убедитесь, что все узлы, которые требуется включить в конфигурацию группы доступности, могут взаимодействовать друг с другом. (Запрос ping к узлу должен вернуть соответствующий IP-адрес.) Также убедитесь, что файл /etc/hosts не содержит запись, которая связывает IP-адрес localhost 127.0.0.1 с именем узла.

   Файл hosts на каждом сервере содержит IP-адреса и имена всех серверов, которые будут участвовать в группе доступности.

   Следующая команда возвращает IP-адрес текущего сервера:

   sudo ip addr show
   

   Обновите /etc/hosts. Следующий скрипт позволяет изменить /etc/hosts с помощью vi:

   sudo vi /etc/hosts
   

   В следующем примере показано /etc/hosts на node1 с добавлениями для node1, node2 и node3. В этом примере node1 ссылается на сервер, на котором размещена первичная реплика, а node2 и node3 ссылаются на серверы, на которых размещены вторичные реплики.

   127.0.0.1    localhost localhost4 localhost4.localdomain4
   ::1          localhost localhost6 localhost6.localdomain6
   10.128.18.12 node1
   10.128.16.77 node2
   10.128.15.33 node3
   

Установка SQL Server

Установите SQL Server. Инструкции по установке SQL Server для различных сред см. по указанным ниже ссылкам:

• Краткое руководство. Установка SQL Server и создание базы данных в Red Hat
• Краткое руководство. Установка SQL Server и создание базы данных на SUSE Linux Enterprise Server
• Краткое руководство. Установка SQL Server и создание базы данных в Ubuntu

Включите группы доступности Always On

Включите группы доступности Always On на каждом узле с экземпляром SQL Server, а затем перезапустите mssql-server. Запустите следующий сценарий:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

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

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

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

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

Создание сертификата

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<private-key-password>'
);

На этом этапе первичная реплика SQL Server имеет сертификат в файле /var/opt/mssql/data/dbm_certificate.cer и закрытый ключ в файле var/opt/mssql/data/dbm_certificate.pvk. Скопируйте эти два файла в одно и то же место на всех серверах, которые будут размещать реплики доступности. Выберите пользователя mssql или предоставьте пользователю mssql разрешения на доступ к этим файлам.

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

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

На каждом целевом сервере предоставьте пользователю mssql разрешение на доступ к сертификату.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

Создайте сертификат на вторичных серверах

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<private-key-password>'
);

В предыдущем примере замените <private-key-password> тот же пароль, который использовался при создании сертификата на первичной реплике.

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

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

Следующий запрос Transact-SQL создает конечную точку прослушивания с именем Hadr_endpoint для группы доступности. Он запускает конечную точку и предоставляет созданному вами сертификату разрешение на подключение. Перед выполнением данного сценария замените значения между < ... >. При необходимости можно включить 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;

Примечание.

Если вы используете выпуск SQL Server Express на одном узле для размещения реплики, которая является только конфигурационной, единственным допустимым значением для ROLE является WITNESS. Выполните следующий скрипт в выпуске SQL Server Express:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP
(
            LISTENER_PORT = 5022
)
    FOR DATABASE_MIRRORING
(
            ROLE = WITNESS,
            AUTHENTICATION = CERTIFICATE dbm_certificate,
            ENCRYPTION = REQUIRED ALGORITHM AES
);

ALTER ENDPOINT [Hadr_endpoint]
    STATE = STARTED;

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

Внимание

Для SQL Server 2017 (14.x) используется CERTIFICATEединственный метод проверки подлинности, поддерживаемый для конечной точки зеркального отображения базы данных. Параметр WINDOWS недоступен.

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

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

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

Следующий сценарий Transact-SQL создает AG с именем ag1. Сценарий настраивает реплики AG с параметром 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 для присоединения к группе доступности (Availability Group):

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

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

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

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
    SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
    TO DISK = N'/var/opt/mssql/data/db1.bak';

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

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

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

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

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;
GO

Эта AG не является конфигурацией высокой доступности. Если вам нужна высокая доступность, следуйте инструкциям по настройке группы доступности AlwaysOn SQL Server для обеспечения высокой доступности в Linux. В частности, создайте группу доступности (AG) с параметром CLUSTER_TYPE=WSFC (в Windows) или CLUSTER_TYPE=EXTERNAL (в Linux). Затем вы можете выполнить интеграцию с диспетчером кластеров с помощью либо отказоустойчивой кластеризации Windows Server в Windows, либо Pacemaker в Linux.

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

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

• Перенос рабочей нагрузки только для чтения на вторичную реплику группы доступности Always On
• Маршрутизация в режиме только чтения

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

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

Есть два способа переключения первичной реплики в группе доступности с типом кластера NONE.

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

Ручной отказоустойчивый переход без потери данных

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

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

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

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

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

   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.

3. Обновите 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);
   

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

   Примечание.

   Этот параметр не относится к отработке отказа и должен быть задан в зависимости от требований среды.

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

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. Повысьте целевую вторичную реплику до первичной.

   ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
   

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

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (ROLE = SECONDARY); 
   

   Примечание.

   Для удаления группы доступности используйте DROP AVAILABILITY GROUP. Для группы доступности, созданной с типом кластера NONE или EXTERNAL, выполните команду на всех репликах, входящих в группу доступности.

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

   ALTER DATABASE [db1]
        SET HADR RESUME
   

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

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

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

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

1. На вторичной реплике (N2) инициируйте принудительное переключение.

   ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

2. На новой первичной реплике (N2) удалите исходную первичную реплику (N1).

   ALTER AVAILABILITY GROUP [AGRScale]
   REMOVE REPLICA ON N'N1';
   

3. Убедитесь, что весь трафик приложения направляется на прослушиватель и (или) новую первичную реплику.

4. Если исходная первичная реплика (N1) переходит в сетевой режим, немедленно переведите группу доступности AGRScale в автономный режим на исходной первичной реплике (N1).

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

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

6. Затем удалите группу доступности из исходной первичной реплики (N1).

   DROP AVAILABILITY GROUP [AGRScale];
   

7. Удалите базу данных группы доступности на исходной первичной реплике (N1).

   USE [master]
   GO
   DROP DATABASE [AGDBRScale]
   GO
   

8. (Необязательно) При необходимости можно добавить N1 обратно в группу доступности AGRScale в качестве новой вторичной реплики.

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

• Распределенные группы доступности
• Что такое группа доступности AlwaysOn?
• Выполнение принудительной отработки отказа вручную группы доступности AlwaysOn (SQL Server)