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

Применимо к: даSQL Server (все поддерживаемые версии)

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

Примечание

Базовые сведения о группах доступности см. в разделе Обзор групп доступности AlwaysOn (SQL Server).

Примечание

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

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

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

Permissions

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

  • Экземпляры сервера должны поддерживать Группы доступности AlwaysOn. Дополнительные сведения см. в разделе Предварительные требования, ограничения и рекомендации для групп доступности AlwaysOn (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  
      

[В начало примера]

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

В этом образце настройки реплика доступности будет создана на двух изолированных экземплярах сервера, у которых учетные записи служб выполняются в разных доверенных доменах (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-адрес конечной точки экземпляра 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)
Обзор групп доступности AlwaysOn (SQL Server)
Прослушиватели групп доступности, возможность подключения клиентов и отработка отказа приложений (SQL Server)
Предварительные требования, ограничения и рекомендации для групп доступности AlwaysOn (SQL Server)