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

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

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

Существует два типа архитектур для групп доступности. В архитектуре для высокого уровня доступности используется диспетчер кластера, позволяющий обеспечить улучшенную непрерывность бизнес-процессов. Сведения о создании архитектуры с высоким уровнем доступности см. в статье Настройка группы доступности 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-сервере, указанные ниже действия выполнять не нужно. Убедитесь, что все узлы, которые требуется включить в конфигурацию группы доступности, могут взаимодействовать друг с другом. (Команда проверки связи с узлом должна возвращать ответ с соответствующим 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).

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

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

Следующий сценарий 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;

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

Убедитесь, что база данных, добавленная в группу доступности, находится в полной модели восстановления и имеет действительную резервную копию журнала. Если база данных является тестовой базой данных или недавно созданной базой данных, создайте резервную копию базы данных. На основном сервере 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

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

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

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

• Разгрузка рабочей нагрузки только для чтения в вторичную реплику группы доступности AlwaysOn
• Маршрутизация только для чтения

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

Каждая группа доступности имеет только одну первичную реплику. Первичная реплика позволяет выполнять операции чтения и записи. Чтобы изменить первичную реплику, можно выполнить переход на другой ресурс. В обычной группе доступности диспетчер кластеров автоматизирует процесс отработки отказа. В группе доступности с типом кластера 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)