Partilhar via

Criar um grupo de disponibilidade Always On usando Transact-SQL (T-SQL)

Aplica-se a:SQL Server

Este tópico descreve como usar Transact-SQL para criar e configurar um grupo de disponibilidade em instâncias do SQL Server nas quais o recurso de grupos de disponibilidade Always On está habilitado. Um grupo de disponibilidade define um conjunto de bancos de dados de usuário que farão failover como uma única unidade e um conjunto de parceiros de failover, conhecidos como réplicas de disponibilidade , que oferecem suporte a failover.

Observação

Para obter uma introdução aos grupos de disponibilidade, consulte Visão geral dos grupos de disponibilidade Always On (SQL Server).

Observação

Como alternativa ao uso do Transact-SQL, você pode usar o assistente Criar Grupo de Disponibilidade ou cmdlets do SQL Server PowerShell. Para obter mais informações, consulte Usar o Assistente de Grupo de Disponibilidade (SQL Server Management Studio),Usar a Caixa de Diálogo Novo Grupo de Disponibilidade (SQL Server Management Studio) ou Criar um Grupo de Disponibilidade (SQL Server PowerShell).

Pré-requisitos, restrições e recomendações

  • Antes de criar um grupo de disponibilidade, verifique se as instâncias do SQL Server que hospedam réplicas de disponibilidade residem em diferentes nós do WSFC (Cluster de Failover do Windows Server) dentro do mesmo cluster de failover WSFC. Além disso, verifique se cada instância do servidor atende a todos os outros pré-requisitos de grupos de disponibilidade Always On. Para obter mais informações, recomendamos fortemente que leia Pré-requisitos, restrições e recomendações para grupos de disponibilidade Always On (SQL Server).

Permissões

Requer associação à função de servidor fixa sysadmin e uma das seguintes permissões: permissão de servidor CREATE AVAILABILITY GROUP, permissão ALTER ANY AVAILABILITY GROUP ou permissão CONTROL SERVER.

Usando Transact-SQL para criar e configurar um grupo de disponibilidade

Resumo das tarefas e declarações de Transact-SQL correspondentes

A tabela a seguir lista as tarefas básicas envolvidas na criação e configuração de um grupo de disponibilidade e indica quais instruções Transact-SQL usar para essas tarefas. As tarefas dos grupos de disponibilidade Always On devem ser executadas na sequência em que são apresentadas na tabela.

Tarefa Transact-SQL Declaração(ões) Onde executar a tarefa*****
Configurar ponto de extremidade de espelhamento de banco de dados (uma vez por instância do SQL Server) CREATE ENDPOINTendpointName ... PARA ESPELHAMENTO DE BASE DE DADOS Execute em cada instância de servidor que não tenha endpoint de espelhamento de base de dados.
Criar grupo de disponibilidade CRIAR GRUPO DE DISPONIBILIDADE Execute na instância do servidor que hospedará a réplica primária inicial.
Adicionar réplica secundária ao grupo de disponibilidade ALTERAR GRUPO DE DISPONIBILIDADEgroup_name JUNTAR-SE A Execute em cada instância do servidor que hospeda uma réplica secundária.
Preparar o banco de dados secundário Backup e Restaurar Crie backups na instância do servidor que hospeda a réplica primária.

Restaure backups em cada instância do servidor que hospeda uma réplica secundária, usando RESTORE WITH NORECOVERY.
Inicie a sincronização de dados associando cada banco de dados secundário ao grupo de disponibilidade ALTERAR BASE DE DADOS database_name DEFINIR HADR GRUPO DE DISPONIBILIDADE = group_name Execute em cada instância do servidor que hospeda uma réplica secundária.

*Para executar uma determinada tarefa, conecte-se à instância ou instâncias do servidor indicadas.

Usando Transact-SQL

Observação

Para obter um procedimento de configuração de exemplo contendo exemplos de código de cada uma dessas instruções Transact-SQL, consulte Exemplo: Configurando um grupo de disponibilidade que usa a autenticação do Windows.

  1. Conecte-se à instância do servidor que hospedará a réplica primária.

  2. Crie o grupo de disponibilidade usando a instrução Transact-SQL CREATE AVAILABILITY GROUP .

  3. Adicione a nova réplica secundária ao grupo de disponibilidade. Para obter mais informações, consulte Adicionar uma Réplica Secundária a um Grupo de Disponibilidade (o SQL Server).

  4. Para cada banco de dados no grupo de disponibilidade, crie um banco de dados secundário restaurando backups recentes do banco de dados primário, usando RESTORE WITH NORECOVERY. Para obter mais informações, consulte Exemplo: Configurando um grupo de disponibilidade usando a autenticação do Windows (Transact-SQL), começando com a etapa que restaura o backup do banco de dados.

  5. Junte cada novo banco de dados secundário ao grupo de disponibilidade. Para obter mais informações, consulte Adicionar uma Réplica Secundária a um Grupo de Disponibilidade (o SQL Server).

Exemplo: Configurando um grupo de disponibilidade que usa a autenticação do Windows

Este exemplo cria um procedimento de configuração de grupos de disponibilidade Always On que utiliza Transact-SQL para configurar endpoints de espelhamento de bases de dados que usam Autenticação do Windows, e para criar e configurar um grupo de disponibilidade e as suas bases de dados secundárias.

Este exemplo contém as seguintes seções:

Pré-requisitos para utilizar o procedimento de configuração exemplo

Este procedimento de exemplo tem os seguintes requisitos:

  • As instâncias do servidor devem oferecer suporte a grupos de disponibilidade Always On. Para obter mais informações, consulte Pré-requisitos, restrições e recomendações para grupos de disponibilidade Always On (SQL Server).

  • Dois bancos de dados de exemplo, MyDb1 e MyDb2, devem existir na instância do servidor que hospedará a réplica primária. Os exemplos de código a seguir criam e configuram esses dois bancos de dados e criam um backup completo de cada um. Execute esses exemplos de código na instância do servidor na qual você pretende criar o grupo de disponibilidade de exemplo. Esta instância do servidor hospedará a réplica primária inicial do grupo de exemplo de disponibilidade.

    1. O exemplo de Transact-SQL a seguir cria esses bancos de dados e os altera para usar o modelo de recuperação completa:

      -- 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. O exemplo de código a seguir cria um backup de banco de dados completo de MyDb1 e MyDb2. Este exemplo de código usa um compartilhamento de backup fictício, \\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]

Procedimento de configuração de exemplo

Nesta configuração de exemplo, a réplica de disponibilidade será criada em duas instâncias de servidor autônomas cujas contas de serviço são executadas em domínios diferentes, mas confiáveis (DOMAIN1 e DOMAIN2).

A tabela a seguir resume os valores usados nesta configuração de exemplo.

Papel inicial Sistema Instância do Host SQL Server
Primário COMPUTER01 AgHostInstance
Secundária COMPUTER02 Instância padrão.

  1. Crie um endpoint de espelhamento de base de dados chamado dbm_endpoint na instância do servidor na qual planeia criar o grupo de disponibilidade (esta é uma instância nomeada AgHostInstance em COMPUTER01). Este ponto de extremidade usa a porta 7022. Observe que a instância do servidor na qual você cria o grupo de disponibilidade hospedará a réplica primária.

    -- 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. Crie um dbm_endpoint na instância do servidor que hospedará a réplica secundária (esta é a instância padrão do servidor em COMPUTER02). Este ponto de extremidade usa a porta 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. Observação

    Se as contas de serviço das instâncias do servidor que devem hospedar suas réplicas de disponibilidade forem executadas na mesma conta de domínio, esta etapa será desnecessária. Ignore isso e vá diretamente para a próxima etapa.

    Se as contas de serviço das instâncias do servidor operarem sob usuários de domínio diferentes, em cada instância do servidor, crie um login para a outra instância do servidor e conceda permissão a este login para acessar o ponto de extremidade de espelhamento da base de dados local.

    O exemplo de código a seguir mostra as instruções Transact-SQL para criar um login e conceder-lhe permissão em um endpoint. A conta de domínio da instância do servidor remoto é representada aqui como domain_name\user_name.

    -- 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. Na instância do servidor onde residem os bancos de dados do usuário, crie o grupo de disponibilidade.

    O exemplo de código a seguir cria um grupo de disponibilidade chamado MyAG na instância do servidor na qual os bancos de dados de exemplo, MyDb1 e MyDb2, foram criados. A instância do servidor local, AgHostInstance, em COMPUTER01 é especificada primeiro. Esta instância hospedará a réplica primária inicial. Uma instância de servidor remoto, a instância de servidor padrão em COMPUTER02, é especificada para hospedar uma réplica secundária. Ambas as réplicas de disponibilidade estão configuradas para usar o modo de confirmação assíncrona com failover manual (para réplicas de confirmação assíncrona, o failover manual significa um failover forçado com possível perda de dados).

    -- 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

    Para obter exemplos de código Transact-SQL adicionais de criação de um grupo de disponibilidade, consulte CREATE AVAILABILITY GROUP (Transact-SQL).

  5. Na instância do servidor que hospeda a réplica secundária, associe a réplica secundária ao grupo de disponibilidade.

    O exemplo de código a seguir une a réplica secundária ao COMPUTER02MyAG grupo de disponibilidade.

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

  6. Na instância do servidor que hospeda a réplica secundária, crie os bancos de dados secundários.

    O exemplo de código a seguir cria os bancos de dados secundários MyDb1 e MyDb2 restaurando backups de banco de dados usando 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. Na instância do servidor que hospeda a réplica primária, faça backup do log de transações em cada um dos bancos de dados primários.

    Importante

    Ao configurar um grupo de disponibilidade real, recomendamos que, antes de fazer esse backup de log, você suspenda as tarefas de backup de log para seus bancos de dados primários até ter unido os bancos de dados secundários correspondentes ao grupo de disponibilidade.

    O exemplo de código a seguir cria um backup de log de transações em MyDb1 e em 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

    Sugestão

    Normalmente, um backup de log deve ser feito em cada banco de dados primário e, em seguida, restaurado no banco de dados secundário correspondente (usando WITH NORECOVERY). No entanto, esse backup de log pode ser desnecessário se o banco de dados tiver acabado de ser criado e nenhum backup de log tiver sido feito ainda ou se o modelo de recuperação tiver acabado de ser alterado de SIMPLE para FULL.

  8. Na instância do servidor que hospeda a réplica secundária, aplique backups de log aos bancos de dados secundários.

    O exemplo de código a seguir aplica backups a bancos de dados secundários MyDb1 e MyDb2 restaurando backups de banco de dados usando RESTORE WITH NORECOVERY.

    Importante

    Ao preparar um banco de dados secundário real, você precisa aplicar todos os backups de log feitos desde o backup do banco de dados a partir do qual você criou o banco de dados secundário, começando com o mais antigo e sempre usando RESTORE WITH NORECOVERY. É claro que, se você restaurar backups de banco de dados completos e diferenciais, só precisará aplicar os backups de log feitos após o backup diferencial.

    -- 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. Na instância do servidor que hospeda a réplica secundária, associe os novos bancos de dados secundários ao grupo de disponibilidade.

    O exemplo de código a seguir une o banco de dados secundário MyDb1 e, em seguida, os bancos de dados secundários MyDb2 ao grupo de disponibilidade 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

Exemplo Completo de Código para Procedimento de Configuração de Amostra

O exemplo a seguir mescla os exemplos de código de todas as etapas do procedimento de configuração de exemplo. A tabela a seguir resume os valores de espaços reservados usados neste exemplo de código. Para obter mais informações sobre as etapas neste exemplo de código, consulte Pré-requisitos para usar o procedimento de configuração de exemplo e oprocedimento de configuração de exemplo, anteriormente neste tópico.

Marcador de posição Descrição
\\ SERVIDOR DE ARQUIVOS\SQLbackups Compartilhamento de backup fictício.
\\ SERVIDOR DE ARQUIVOS\SQLbackups\MyDb1.bak Arquivo de backup para MyDb1.
\\ SERVIDOR\ DE FICHEIROSSQLbackups\MyDb2.bak Arquivo de backup para MyDb2.
7022 Número da porta atribuído a cada ponto de extremidade de espelhamento de banco de dados.
COMPUTER01\AgHostInstance Instância do servidor que hospeda a réplica primária inicial.
COMPUTER02 Instância do servidor que hospeda a réplica secundária inicial. Esta é a instância padrão do servidor no COMPUTER02.
dbm_endpoint Nome especificado para cada interface de espelhamento de base de dados.
MyAG Nome do grupo de disponibilidade de amostra.
MyDb1 Nome do primeiro banco de dados de exemplo.
MyDb2 Nome do segundo banco de dados de exemplo.
DOMAIN1\utilizador1 Conta de serviço da instância do servidor que deve hospedar a réplica primária inicial.
DOMAIN2\user2 Conta de serviço da instância do servidor que deve hospedar a réplica secundária inicial.
TCP:// COMPUTER01.Adventure-Works.com:7022 URL do endpoint da instância AgHostInstance do SQL Server no COMPUTER01.
TCP:// COMPUTER02.Adventure-Works.com:5022 URL do ponto de extremidade da instância padrão do SQL Server no COMPUTER02.

Observação

Para obter exemplos de código Transact-SQL adicionais de criação de um grupo de disponibilidade, consulte 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

Tarefas relacionadas

Para configurar o grupo de disponibilidade e as propriedades de réplica

Para concluir a configuração do grupo de disponibilidade

Formas alternativas de criar um grupo de disponibilidade

Para habilitar os Grupos de Disponibilidade Always On

Para configurar um ponto de extremidade de espelhamento de banco de dados

Para solucionar problemas de configuração de Grupos de Disponibilidade Always On

Conteúdo relacionado

Ver também

O endpoint de espelhamento de base de dados (SQL Server)
Visão geral dos grupos de disponibilidade Always On (SQL Server)
Ouvintes de Grupos de Disponibilidade, Conectividade de Clientes e Failover de Aplicações (SQL Server)
Pré-requisitos, restrições e recomendações para os Grupos de Disponibilidade Always On (SQL Server)

  • Last updated on