Создание группы доступности 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 ENDPOINT endpointName ... FOR DATABASE_MIRRORING	Выполнить на каждом экземпляре сервера, на котором отсутствует конечная точка зеркалирования базы данных.
Создать группу доступности	CREATE AVAILABILITY GROUP	Выполнить на экземпляре сервера, где будет размещена исходная первичная реплика.
Присоединить вторичную реплику к группе доступности	ALTER AVAILABILITY GROUP group_name ПРИСОЕДИНЕНИЕ	Выполнить на каждом экземпляре сервера, размещающем вторичную реплику.
Подготовьте вторичную базу данных	BACKUP и RESTORE.	Создайте резервные копии на экземпляре сервера, размещающем первичную реплику. Восстановление резервных копий на каждом экземпляре сервера, на котором размещена вторичная реплика, с помощью RESTORE WITH NORECOVERY.
Запустите синхронизацию данных, присоединив каждую вторичную базу данных к группе доступности	ALTER DATABASE SET HADR AVAILABILITY GROUP database_name = group_name	Выполнить на каждом экземпляре сервера, размещающем вторичную реплику.

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

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

Примечание.

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

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

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

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

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

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

Пример. Настройка группы доступности, использующей проверку подлинности 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  
   

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

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

Заполнитель	Описание
\\ 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-адрес конечной точки экземпляра SQL Server AgHostInstance на компьютере 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  

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

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

• Изменение режима доступности реплики доступности (SQL Server)

• Изменение режима отработки отказа для реплики доступности (SQL Server)

• Создание или настройка прослушивателя группы доступности (SQL Server)

• Настройка гибкой политики отработки отказа для управления условиями автоматической отработки отказа (группы доступности AlwaysOn)

• Укажите URL-адрес конечной точки при добавлении или изменении реплики доступности (SQL Server)

• Настройка резервного копирования на репликах доступности (SQL Server)

• Настройка доступа только для чтения в реплике доступности (SQL Server)

• Настройка маршрутизации только для чтения в группе доступности (SQL Server)

• Изменение периода времени ожидания сеанса для реплики доступности (SQL Server)

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

• Присоединение вторичной реплики к группе доступности (SQL Server)

• Подготовка вторичной базы данных для группы доступности вручную (SQL Server)

• Присоединение вторичной базы данных к группе доступности (SQL Server)

• Создание или настройка прослушивателя группы доступности (SQL Server)

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

• Использование мастера групп доступности (SQL Server Management Studio)

• Использование диалогового окна "Создание группы доступности" (SQL Server Management Studio)

• Создание группы доступности (SQL Server PowerShell)

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

• Включение и отключение групп доступности AlwaysOn (SQL Server)

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

• Создание конечной точки зеркального отображения базы данных для групп доступности AlwaysOn (SQL Server PowerShell)

• Создать конечную точку зеркального отображения базы данных для проверки подлинности Windows (Transact-SQL)

• Использование сертификатов для конечной точки зеркального отображения базы данных (Transact-SQL)

• Укажите URL-адрес конечной точки при добавлении или изменении реплики доступности (SQL Server)

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

• Поиск и устранение неисправностей конфигурации групп доступности Always On (SQL Server)

• Устранение неполадок при сбое операции добавления файла (группы доступности Always On)

Связанные материалы

• Блоги

  Обучающая серия AlwaysOn — HADRON: использование рабочего пула для баз данных с поддержкой HADRON

  Блоги команды разработчиков SQL Server Always On: официальный блог разработчиков SQL Server Always On

  Блоги инженеров CSS SQL Server

• Технические документы

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

См. также

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