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

Область применения: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 команды.

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

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

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

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

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

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

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

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

Сервисная учетная запись

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

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

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

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

CREATE LOGIN dbm_login WITH PASSWORD = '<password>';
CREATE USER dbm_user FOR LOGIN dbm_login;

Аутентификация по сертификату

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<dmk-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= '<dmk-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:

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

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

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

Чтобы вручную выполнить аварийное переключение без потери данных:

  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 в качестве новой вторичной реплики.

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

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

  • Last updated on