Создание группы доступности Always On с помощью Transact-SQL (T-SQL)

  • Статья

Применимо к:SQL Server

В данном разделе описывается использование Transact-SQL для создания и настройки группы доступности на основе экземпляров SQL Server, на которых включена функция групп доступности Always On. Группа доступности определяет набор пользовательских баз данных, которые будут действовать при сбое как единое целое, и набор партнеров по обеспечению отработки отказа, называемых репликами доступностии поддерживающих отработку отказа.

Примечание.

Базовые сведения о группах доступности см. в статье Что такое группа доступности Always On?.

Примечание.

Вместо Transact-SQL можно использовать мастер создания групп доступности или командлеты SQL Server PowerShell. Дополнительные сведения см. в статьях Использование мастера групп доступности (среда SQL Server Management Studio), Использование диалогового окна "Создание группы доступности" (среда SQL Server Management Studio) или Создание группы доступности (SQL Server PowerShell).

Предварительные условия, ограничения и рекомендации

  • Перед созданием группы доступности убедитесь, что экземпляры SQL Server, на которых размещена доступность реплика, находятся на разных узлах отказоустойчивой кластеризации Windows Server (WSFC) в одном отказоустойчивом кластере WSFC. Кроме того, убедитесь, что каждый экземпляр сервера соответствует всем другим предварительным требованиям групп доступности AlwaysOn. Для получения дополнительных сведений настоятельно рекомендуется изучить статью Предварительные требования, ограничения и рекомендации для групп доступности Always On (SQL Server).

Разрешения

Требуется членство в фиксированной роли сервера sysadmin и одно из разрешений: CREATE AVAILABILITY GROUP, ALTER ANY AVAILABILITY GROUP или CONTROL SERVER.

Создание и настройка группы доступности с помощью Transact-SQL

Сводка задач и соответствующих инструкций Transact-SQL

В следующей таблице перечислены основные задачи, связанные с созданием и настройкой группы доступности, а также инструкции Transact-SQL, используемые при выполнении этих задач. Задачи групп доступности AlwaysOn должны выполняться в последовательности, в которой они представлены в таблице.

Задача Инструкции Transact-SQL Место выполнения задачи*****
Создание конечной точки зеркало базы данных (один раз на экземпляр SQL Server) CREATE ENDPOINTendpointName ... FOR DATABASE_MIRRORING Выполнить на каждом экземпляре сервера, у которого нет конечной точки зеркального отображения базы данных.
Создание группы доступности. CREATE AVAILABILITY GROUP Выполнить на экземпляре сервера, где будет размещена исходная первичная реплика.
Присоединить вторичную реплику к группе доступности ALTER AVAILABILITY GROUPgroup_name JOIN Выполнить на каждом экземпляре сервера, размещающем вторичную реплику.
Подготовьте базу данных-получатель BACKUP и RESTORE. Создайте резервные копии на экземпляре сервера, размещающем первичную реплику.

Восстановить резервные копии на каждом экземпляре сервера, размещающем вторичную реплику, используя инструкцию RESTORE WITH NORECOVERY.
Запуск синхронизации данных с помощью присоединения каждой базы данных-получателя к группе доступности ALTER DATABASEdatabase_name SET HADR AVAILABILITY GROUP = group_name Выполнить на каждом экземпляре сервера, размещающем вторичную реплику.

*Для выполнения данной задачи подключитесь к указанным экземплярам сервера.

Использование Transact-SQL

Примечание.

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

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

  2. Создайте группу доступности с помощью инструкции CREATE AVAILABILITY GROUPTransact-SQL.

  3. Присоедините новую вторичную реплику к группе доступности. Дополнительные сведения см. в статье Присоединение вторичной реплики к группе доступности Always On.

  4. Для каждой базы данных в группе доступности создайте базу данных-получатель путем восстановления последней резервной копии базы данных-источника с помощью инструкции RESTORE WITH NORECOVERY. Дополнительные сведения см. в разделе Пример. Настройка группы доступности с использованием проверки подлинности Windows (Transact-SQL), начиная с шага восстановления резервной копии базы данных.

  5. Присоедините каждую новую базу данных-получатель к группе доступности. Дополнительные сведения см. в статье Присоединение вторичной реплики к группе доступности Always On.

Пример. Настройка группы доступности, использующей проверку подлинности Windows

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

Этот пример содержит следующие разделы:

Предварительные требования для использования процедуры настройки образца

Этот образец процедуры имеет следующие требования.

  • Экземпляры сервера должны поддерживать группы доступности AlwaysOn. Дополнительные сведения см. в статье Предварительные требования, ограничения и рекомендации для групп доступности Always On (SQL Server).

  • Оба образца баз данных, MyDb1 и MyDb2, должны существовать на экземпляре сервера, где будет размещаться первичная реплика. В следующем примере кода создаются и настраиваются эти две базы данных и создается полная резервная копия каждой из них. Выполните эти примеры кода на экземпляре сервера, где планируется создавать образец группы доступности. На этом экземпляре сервера будет размещаться первоначальная первичная реплика образца группы доступности.

    1. В следующем примере Transact-SQL эти базы данных создаются и переключаются на модель полного восстановления:

      -- Create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  

CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO

    2. В следующем примере кода создается полная резервная копия баз данных MyDb1 и MyDb2. В этом примере кода используется вымышленная общая папка резервных копий \\FILESERVER\SQLbackups.

      -- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  

BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO

[TopOfExample]

Образец процедуры настройки

В этом образце настройки реплика доступности будет создана на двух изолированных экземплярах сервера, у которых учетные записи служб выполняются в разных доверенных доменах (DOMAIN1 и DOMAIN2).

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

Первоначальная роль Системные Узел экземпляра SQL Server
Основной COMPUTER01 AgHostInstance
Вторичные COMPUTER02 Экземпляр по умолчанию.

  1. Создайте конечную точку зеркального отображения базы данных с именем dbm_endpoint в экземпляре сервера, где планируется создать группу доступности (это экземпляр с именем AgHostInstance на компьютере COMPUTER01). Эта конечная точка использует порт 7022. Обратите внимание, что на экземпляре сервера, где создается группа доступности, будет размещаться первичная реплика.

    -- Create endpoint on server instance that hosts the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  2. Создайте конечную точку dbm_endpoint в экземпляре сервера, где будет размещаться вторичная реплика (это экземпляр сервера по умолчанию на компьютере COMPUTER02). Эта конечная точка использует порт 5022.

    -- Create endpoint on server instance that hosts the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  3. Примечание.

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

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

    В следующем примере кода приведены инструкции Transact-SQL для создания имени входа и предоставления ему разрешения для конечной точки. Учетная запись домена на удаленном экземпляре сервера представлена здесь как имя_домена\имя_пользователя.

    -- If necessary, create a login for the service account, domain_name\user_name  
-- of the server instance that will host the other replica:  
USE master;  
GO  
CREATE LOGIN [domain_name\user_name] FROM WINDOWS;  
GO  
-- And Grant this login connect permissions on the endpoint:  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [domain_name\user_name];  
GO

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

    В следующем примере кода создается группа доступности с именем MyAG на экземпляре сервера, на котором были созданы образцы баз данных MyDb1 и MyDb2. Сначала указывается локальный экземпляр сервера, AgHostInstance, размещенный на компьютере COMPUTER01 . На этом экземпляре сервера будет размещаться первоначальная первичная реплика. Указано, что на удаленном экземпляре сервера, являющемся экземпляром сервера по умолчанию, размещенном на компьютере COMPUTER02, размещена вторичная реплика. Обе реплики доступности настроены для использования асинхронного режима фиксации с переходом на другой ресурс вручную (для реплик с асинхронной фиксацией переход на другой ресурс вручную означает принудительное переключение с возможной потерей данных).

    -- Create the availability group, MyAG:   
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );   
GO

    Дополнительные примеры кода Transact-SQL, предназначенного для создания группы доступности, см. в статье CREATE AVAILABILITY GROUP (Transact-SQL).

  5. На экземпляре сервера, размещающем вторичную реплику, присоедините ее к группе доступности.

    В следующем примере кода вторичная реплика на компьютере COMPUTER02 присоединяется к группе доступности MyAG .

    -- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO

  6. На экземпляре сервера, размещающем вторичную реплику, создайте базы данных-получатели.

    В следующем примере кода создаются базы данных-получатели MyDb1 и MyDb2 путем восстановления резервных копий с помощью инструкции RESTORE WITH NORECOVERY.

    -- On the server instance that hosts the secondary replica,   
-- Restore database backups using the WITH NORECOVERY option:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  

RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO

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

    Важно!

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

    В следующем примере кода создается резервная копия журнала транзакций для баз данных MyDb1 и MyDb2.

    -- On the server instance that hosts the primary replica,   
-- Backup the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  

BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT;  
GO

    Совет

    Как правило, резервная копия журнала должна создаваться для каждой базы данных-источника а затем восстанавливаться в соответствующей базе данных-получателе (с помощью инструкции WITH NORECOVERY). Однако в этой резервной копии журнала может не быть необходимости, если база данных только что создана и резервной копии журнала еще нет либо модель восстановления только что сменили с SIMPLE на FULL.

  8. На экземпляре сервера, размещающем вторичную реплику, примените резервные копии журналов к базам данных-получателям.

    В следующем примере кода показано применение резервных копий к базам данных-получателям MyDb1 и MyDb2 путем восстановления резервных копий с помощью инструкции RESTORE WITH NORECOVERY.

    Важно!

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

    -- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO

  9. На экземпляре сервера, размещающем вторичную реплику, присоедините новую базу данных-получателя к группе доступности.

    В следующем примере кода к группе доступности MyDb1 присоединяется база данных-получатель MyDb2 , а затем база данных-получатель MyAG .

    -- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Выполните пример кода для процедуры настройки образца

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

Заполнитель Description
\\FILESERVER\SQLbackups Вымышленная общая папка резервных копий.
\\FILESERVER\SQLbackups\MyDb1.bak Файл резервной копии для базы данных MyDb1.
\\FILESERVER\SQLbackups\MyDb2.bak Файл резервной копии для базы данных MyDb2.
7022 Номер порта, присвоенный каждой конечной точке зеркального отображения базы данных.
COMPUTER01\AgHostInstance Экземпляр сервера, на котором размещается первоначальная первичная реплика.
COMPUTER02 Экземпляр сервера, на котором размещается первоначальная вторичная реплика. Это экземпляр сервера по умолчанию на компьютере COMPUTER02.
dbm_endpoint Имя, заданное для каждой конечной точки зеркального отображения базы данных.
MyAG Имя образца группы доступности.
MyDb1 Имя первого образца базы данных.
MyDb2 Имя второго образца базы данных.
Домен1\пользователь1 Учетная запись службы экземпляра сервера, на котором планируется размещать первоначальную основную реплику.
Домен2\пользователь2 Учетная запись службы экземпляра сервера, на котором планируется размещать первоначальную вторичную реплику.
TCP://COMPUTER01.Adventure-Works.com:7022 URL-адрес конечной точки экземпляра AgHostInstance SQL Server в COMPUTER01.
TCP://COMPUTER02.Adventure-Works.com:5022 URL-адрес конечной точки экземпляра SQL Server по умолчанию на COMPUTER02.

Примечание.

Дополнительные примеры кода Transact-SQL, предназначенного для создания группы доступности, см. в статье CREATE AVAILABILITY GROUP (Transact-SQL).

-- on the server instance that will host the primary replica,   
-- create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  
  
CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO  
  
-- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  
  
BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO  
  
-- Create the endpoint on the server instance that will host the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- Create the endpoint on the server instance that will host the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the primary replica,   
-- create a login for the service account   
-- of the server instance that will host the secondary replica, DOMAIN2\user2,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN2\user2];  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the secondary replica,  
-- create a login for the service account   
-- of the server instance that will host the primary replica, DOMAIN1\user1,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
  
CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN1\user1];  
GO  
  
-- On the server instance that will host the primary replica,   
-- create the availability group, MyAG:  
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         );   
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO  
  
-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  
  
RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO  
  
-- Back up the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  
  
BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT  
GO  
  
-- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  
  
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Связанные задачи

Настройка свойств группы доступности и реплики

Завершение настройки группы доступности

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

Включение функции "Группы доступности AlwaysOn"

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

Устранение неполадок с конфигурацией групп доступности AlwaysOn

См. также

См. также

Конечная точка зеркального отображения базы данных (SQL Server)
Обзор групп доступности Always On (SQL Server)
Прослушиватели групп доступности, возможность подключения клиентов и отработка отказа приложений (SQL Server)
Предварительные требования, ограничения и рекомендации для групп доступности AlwaysOn (SQL Server)