Creación de un grupo de disponibilidad Always On mediante Transact-SQL (T-SQL)

Se aplica a: SQL Server

En este tema se describe cómo usar Transact-SQL para crear y configurar un grupo de disponibilidad en las instancias de SQL Server en que se habilita la característica de grupos de disponibilidad Always On. Un grupo de disponibilidad define un conjunto de bases de datos de usuario que realizarán la conmutación por error como una sola unidad y un conjunto de asociados de conmutación por error, conocido como réplicas de disponibilidad, que admiten la conmutación por error.

Requisitos previos, restricciones y recomendaciones

• Antes de crear un grupo de disponibilidad, compruebe que las instancias de SQL Server que hospedan réplicas de disponibilidad residen en otro nodo (WSFC) de clúster de conmutación por error de Windows Server en el mismo clúster de conmutación por error de WSFC. Además, compruebe que cada una de las instancias del servidor cumple los requisitos previos de Grupos de disponibilidad AlwaysOn . Para más información, recomendamos encarecidamente que lea Requisitos previos, restricciones y recomendaciones para grupos de disponibilidad AlwaysOn (SQL Server).

Permisos

Se requiere la pertenencia al rol fijo de servidor sysadmin y el permiso de servidor CREATE AVAILABILITY GROUP, el permiso ALTER ANY AVAILABILITY GROUP o el permiso CONTROL SERVER.

Usar Transact-SQL para crear y configurar un grupo de disponibilidad

Resumen de las tareas e instrucciones Transact-SQL correspondientes

En la tabla siguiente se enumeran las tareas básicas relacionadas con la creación y configuración de un grupo de disponibilidad, y se indican las instrucciones Transact-SQL que han de utilizarse para estas tareas. Las tareas de Grupos de disponibilidad AlwaysOn se deben realizar en la secuencia en que se muestran en la tabla.

*Para realizar una tarea determinada, conéctese a la instancia o instancias del servidor indicadas.

Usar Transact-SQL

1. Conéctese a la instancia del servidor que va a hospedar la réplica principal.

2. Cree el grupo de disponibilidad mediante la instrucción de Transact-SQL CREATE AVAILABILITY GROUP.

3. Una la nueva réplica secundaria al grupo de disponibilidad. Para más información, consulte Unión de una réplica secundaria con un grupo de disponibilidad (SQL Server).

4. Para cada base de datos del grupo de disponibilidad, cree una base de datos secundaria restaurando las copias de seguridad recientes de la base de datos principal, utilizando RESTORE WITH NORECOVERY. Para más información, vea Ejemplo: Configuración de un grupo de disponibilidad mediante la Autenticación de Windows (Transact-SQL), comenzando por el paso que restaura la copia de seguridad de la base de datos.

5. Una cada nueva base de datos secundaria al grupo de disponibilidad. Para más información, consulte Unión de una réplica secundaria con un grupo de disponibilidad (SQL Server).

Ejemplo: configurar un grupo de disponibilidad que use la Autenticación de Windows

En este ejemplo se crea un procedimiento de configuración de grupos de disponibilidad Always On de ejemplo que utiliza Transact-SQL para configurar los puntos de conexión para la creación de reflejo de la base de datos con autenticación de Windows, y para crear y configurar un grupo de disponibilidad y sus bases de datos secundarias.

Este ejemplo contiene las siguientes secciones:

• Requisitos previos para utilizar el procedimiento de configuración de ejemplo

• Procedimiento de configuración de ejemplo

• Ejemplo completo de código del procedimiento de configuración de ejemplo

Requisitos previos para utilizar el procedimiento de configuración de ejemplo

Este procedimiento de ejemplo tiene los requisitos siguientes:

• Las instancias del servidor deben admitir Grupos de disponibilidad AlwaysOn. Para más información, consulte Requisitos previos, restricciones y recomendaciones para grupos de disponibilidad AlwaysOn (SQL Server).

• Debe haber dos bases de datos, MyDb1 y MyDb2, en la instancia del servidor que va a hospedar la réplica principal. En los siguientes ejemplos de código se crean y configuran estas dos bases de datos y se crea una copia de seguridad completa de cada una. Ejecute estos ejemplos de código en la instancia del servidor en que desea crear el grupo de disponibilidad de ejemplo. Esta instancia del servidor hospedará la réplica principal inicial del grupo de disponibilidad de ejemplo.

  1. En el siguiente ejemplo de Transact-SQL se crean estas bases de datos y se modifican para utilizar el modelo de recuperación 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. En el ejemplo de código siguiente se crea una copia de seguridad completa de las bases de datos MyDb1 y MyDb2. En este ejemplo de código se usa un recurso compartido ficticio de copia de seguridad, \\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  
     

[Principio del ejemplo]

Procedimiento de configuración de ejemplo

En esta configuración de ejemplo, la réplica de disponibilidad se creará en dos instancias del servidor independientes cuyas cuentas de servicio se ejecutan en diferentes dominios de confianza (DOMAIN1 y DOMAIN2).

En la siguiente tabla se resumen los valores utilizados en esta configuración de ejemplo.

1. Cree un punto de conexión de creación de reflejo de la base de datos denominado dbm_endpoint en la instancia del servidor en la que planea crear el grupo de disponibilidad (se trata de una instancia llamada AgHostInstance en COMPUTER01). Este punto de conexión usa el puerto 7022. Tenga en cuenta que la instancia del servidor en que se crea el grupo de disponibilidad hospedará la réplica principal.

   -- 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. Cree un punto de conexión dbm_endpoint en la instancia del servidor que hospedará la réplica secundaria (se trata de la instancia del servidor predeterminada en COMPUTER02). Este extremo usa el puerto 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. Nota

   Si las cuentas de servicio de las instancias del servidor que van a hospedar las réplicas de disponibilidad se ejecutan en la misma cuenta de dominio, este paso no es necesario. Omítalo y vaya directamente al paso siguiente.

   Si las cuentas de servicio de las instancias del servidor se ejecutan en usuarios de dominio diferentes, en cada instancia del servidor, cree un inicio de sesión para la otra instancia del servidor y conceda este permiso de inicio de sesión para tener acceso al extremo de creación de reflejo de la base de datos local.

   En el ejemplo de código siguiente se muestran las instrucciones Transact-SQL para crear un inicio de sesión y concederle permiso en un punto de conexión. La cuenta de dominio de la instancia del servidor remoto se representa aquí 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. En la instancia del servidor donde residen las bases de datos de usuario, cree el grupo de disponibilidad.

   En el ejemplo de código siguiente se crea un grupo de disponibilidad denominado MyAG en la instancia del servidor en la que se crearon las bases de datos de ejemplo, MyDb1 y MyDb2. La instancia del servidor local, AgHostInstance, en COMPUTER01 se especifica primero. Esta instancia hospedará la réplica principal inicial. Una instancia del servidor remoto, la instancia del servidor predeterminada en COMPUTER02, se especifica para hospedar una réplica secundaria. Ambas réplicas de disponibilidad están configuradas para usar el modo de confirmación asincrónica con conmutación por error manual (para las réplicas de confirmación asincrónica, la conmutación por error manual significa una conmutación por error forzada con posible pérdida de datos).

   -- 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 obtener ejemplos adicionales de código para la creación de un grupo de disponibilidad, consulte CREATE AVAILABILITY GROUP (Transact-SQL).

5. En la instancia del servidor que hospeda la réplica secundaria, combine la réplica secundaria con el grupo de disponibilidad.

   En el ejemplo de código siguiente se une la réplica secundaria de COMPUTER02 al grupo de disponibilidad 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. En la instancia del servidor que hospeda la réplica secundaria, cree las bases de datos secundarias.

   En el ejemplo de código siguiente se crean las bases de datos secundarias MyDb1 y MyDb2 mediante la restauración de las copias de seguridad de base de datos con 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. En la instancia del servidor que hospeda la réplica principal, realice una copia de seguridad del registro de transacciones en cada una de las bases de datos principales.

   Importante

   Cuando configure un grupo de disponibilidad real, conviene que, antes de realizar la copia de seguridad de registros, suspenda las tareas de copia de seguridad de registros para las bases de datos principales hasta haber combinado las bases de datos secundarias con el grupo de disponibilidad.

   En el ejemplo de código siguiente se crea una copia de seguridad del registro de transacciones en MyDb1 y en 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
   

   Sugerencia

   Normalmente, debe realizarse una copia de seguridad de registros en cada base de datos principal y, después, restaurarse en la base de datos secundaria correspondiente (utilizando WITH NORECOVERY). Sin embargo, puede que no se necesite esta copia de seguridad de registros si la base de datos se ha creado recientemente y no se ha realizado todavía ninguna copia de seguridad de registros, o si el modelo de recuperación ha cambiado recientemente de SIMPLE a FULL.

8. En la instancia del servidor que hospeda la réplica secundaria, aplique las copias de seguridad de registros a las bases de datos secundarias.

   En el ejemplo de código siguiente se aplican copias de seguridad a las bases de datos secundarias MyDb1 y MyDb2 mediante la restauración de las copias de seguridad de base de datos con RESTORE WITH NORECOVERY.

   Importante

   Cuando se prepara una base de datos secundaria real, es necesario aplicar cada copia de seguridad de registros desde la copia de seguridad de base de datos a partir de la cual se creó la base de datos secundaria, empezando por la más temprana y utilizando siempre RESTORE WITH NORECOVERY. Por supuesto, si restaura tanto la copia de seguridad diferencial como la copia de seguridad completa de bases de datos, solo tendría que aplicar las copias de seguridad de registros realizadas después de la copia de seguridad 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. En la instancia del servidor que hospeda la réplica secundaria, combine las nuevas bases de datos secundarias al grupo de disponibilidad.

   En el ejemplo de código siguiente se une la base de datos secundaria MyDb1 y luego la base de datos secundaria MyDb2 al grupo de disponibilidad 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  
   

Ejemplo completo de código del procedimiento de configuración de ejemplo

En el ejemplo siguiente se unen los ejemplos de código de todos los pasos del procedimiento de configuración de ejemplo. En la tabla siguiente se resumen los valores de marcador de posición utilizados en este ejemplo de código. Para obtener más información acerca de los pasos de este ejemplo de código, vea Requisitos previos para usar el procedimiento de configuración de ejemplo y Procedimiento de configuración de ejemplo, anteriormente en este tema.

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

Related Tasks

Para configurar el grupo de disponibilidad y las propiedades de réplica

• Cambiar el modo de disponibilidad de una réplica de disponibilidad (SQL Server)

• Cambiar el modo de conmutación por error de una réplica de disponibilidad (SQL Server)

• Configuración de un agente de escucha para un grupo de disponibilidad Always On (SQL Server)

• Configurar la directiva de conmutación por error flexible para controlar las condiciones de la conmutación automática por error (grupos de disponibilidad AlwaysOn)

• Especificar la dirección URL del extremo al agregar o modificar una réplica de disponibilidad (SQL Server)

• Configurar la copia de seguridad en réplicas de disponibilidad (SQL Server)

• Configurar el acceso de solo lectura en una réplica de disponibilidad (SQL Server)

• Configuración del enrutamiento de solo lectura para un grupo de disponibilidad Always On (SQL Server)

• Cambiar el tiempo de espera de la sesión en una réplica de disponibilidad (SQL Server)

Para completar la configuración del grupo de disponibilidad

• Combinar una réplica secundaria con un grupo de disponibilidad (SQL Server)

• Preparar manualmente una base de datos secundaria para un grupo de disponibilidad (SQL Server)

• Combinar una base de datos secundaria con un grupo de disponibilidad (SQL Server)

• Configuración de un agente de escucha para un grupo de disponibilidad Always On (SQL Server)

Maneras alternativas de crear un grupo de disponibilidad

• Usar el Asistente para grupo de disponibilidad (SQL Server Management Studio)

• Usar el cuadro de diálogo Nuevo grupo de disponibilidad (SQL Server Management Studio)

• Crear un grupo de disponibilidad (SQL Server PowerShell)

Para habilitar los grupos de disponibilidad AlwaysOn

• Habilitar y deshabilitar grupos de disponibilidad AlwaysOn (SQL Server)

Para configurar un extremo de creación del reflejo de la base de datos

• Crear un punto de conexión de creación de reflejo de la base de datos para grupos de disponibilidad AlwaysOn (SQL Server PowerShell)

• Crear un extremo de reflejo de la base de datos para la autenticación de Windows (Transact-SQL)

• Usar certificados para un punto de conexión de creación de reflejo de la base de datos (Transact-SQL)

• Especificar la dirección URL del extremo al agregar o modificar una réplica de disponibilidad (SQL Server)

Para solucionar problemas de configuración de grupos de disponibilidad AlwaysOn

• Solucionar problemas de configuración de grupos de disponibilidad AlwaysOn (SQL Server)

• Solucionar problemas relativos a una operación de agregar archivos con error (grupos de disponibilidad AlwaysOn)

Contenido relacionado

• Blogs:

  Series de aprendizaje de AlwaysOn - HADRON: uso del grupo de trabajo para las bases de datos compatibles con HADRON

  Blogs del equipo de AlwaysOn de SQL Server: blog oficial del equipo de AlwaysOn de SQL Server

  Blogs de los ingenieros de SQL Server de CSS

• Notas del producto:

  Guía de soluciones AlwaysOn de Microsoft SQL Server para lograr alta disponibilidad y recuperación ante desastres

  Notas del producto del equipo de asesoramiento al cliente de SQL Server

Consulte también

El extremo de creación de reflejo de la base de datos (SQL Server)
Información general de los grupos de disponibilidad AlwaysOn (SQL Server)
Agentes de escucha del grupo de disponibilidad, conectividad de cliente y conmutación por error de una aplicación (SQL Server)
Requisitos previos, restricciones y recomendaciones para Grupos de disponibilidad AlwaysOn (SQL Server)