Crear un grupo de disponibilidad (Transact-SQL)

En este tema se describe cómo usar Transact-SQL para crear y configurar un grupo de disponibilidad en las instancias de SQL Server 2012 en que se habilita la característica de Grupos de disponibilidad AlwaysOn. 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.

[!NOTA]

Para obtener una introducción a los grupos de disponibilidad, vea Información general de los grupos de disponibilidad AlwaysOn (SQL Server).

• Antes de empezar:  

  Requisitos previos

  Seguridad

  Resumen de las tareas e instrucciones Transact-SQL correspondientes

• Para crear y configurar un grupo de disponibilidad mediante:  Transact-SQL

• **Ejemplo: ** Configurar un grupo de disponibilidad que usa la Autenticación de Windows

• Tareas relacionadas

• Contenido relacionado

[!NOTA]

Como alternativa al uso de Transact-SQL, puede usar el Asistente para crear grupo de disponibilidad o cmdlets de SQL Server PowerShell. Para obtener más información, vea Usar el Asistente para nuevo grupo de disponibilidad (SQL Server Management Studio), Usar el cuadro de diálogo Nuevo grupo de disponibilidad (SQL Server Management Studio) o Crear un grupo de disponibilidad (SQL Server PowerShell).

Antes de empezar

Se recomienda encarecidamente leer esta sección antes de intentar crear el primer grupo de disponibilidad.

Requisitos previos, restricciones y recomendaciones

• Antes de crear una disponibilidad de grupo, 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 obtener más información, le recomendamos encarecidamente que consulte Requisitos previos, restricciones y recomendaciones para Grupos de disponibilidad AlwaysOn (SQL Server).

Seguridad

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.

[Arriba]

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.

[Principio]

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

[!NOTA]

Para consultar un procedimiento de configuración de ejemplo que contiene ejemplos de código de cada una de estas instrucciones Transact-SQL, vea Ejemplo: configurar un grupo de disponibilidad que utilice la Autenticación de Windows.

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

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

3. Una la nueva réplica secundaria al grupo de disponibilidad. Para obtener más información, vea Combinar 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 obtener más información, vea Crear un grupo de disponibilidad (Transact-SQL), empezando 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 obtener más información, vea Combinar una réplica secundaria con un grupo de disponibilidad (SQL Server).

[Arriba]

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

En este ejemplo se crea un procedimiento de configuración de Grupos de disponibilidad AlwaysOn de ejemplo que utiliza Transact-SQL para configurar los extremos de creación de reflejo de la base de datos que utilizan la 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 obtener más información, vea Requisitos previos, restricciones y recomendaciones para Grupos de disponibilidad AlwaysOn (SQL Server).

• Debe haber dos bases de datos de ejemplo, MyDb1 y MyDb2, en la instancia del servidor que 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 MyDb1 y MyDb2. En este ejemplo de código se usa un recurso compartido de copia de seguridad ficticio, \\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 extremo de creación de reflejo de la base de datos denominado dbm_endpoint en la instancia del servidor en la que va a crear el grupo de disponibilidad (esta es una instancia denominada AgHostInstance en COMPUTER01). Este extremo 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 extremo dbm_endpoint en la instancia del servidor que hospedará la réplica secundaria (esta es 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 extremo. 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 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 Transact-SQL para la creación de un grupo de disponibilidad, vea 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 restaurando las copias de seguridad de base de datos mediante 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' 
       WITHNOFORMAT
   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 las copias de seguridad a las bases de datos secundarias MyDb1 y MyDb2 restaurando las copias de seguridad de base de datos mediante 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 siguiente ejemplo de código se combina la base de datos secundaria MyDb1 y, a continuación, 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
   

[Principio del ejemplo]

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.

[!NOTA]

Para obtener ejemplos adicionales de código Transact-SQL para la creación de un grupo de disponibilidad, vea 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' 
    WITHNOFORMAT
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

[Principio del ejemplo]

Tareas relacionadas

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)

• Crear o configurar un agente de escucha del grupo de disponibilidad (SQL Server)

• Configurar la directiva de conmutación por error flexible para las condiciones de control 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)

• Configurar el enrutamiento de solo lectura para un grupo de disponibilidad (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)

• Crear o configurar un agente de escucha del grupo de disponibilidad (SQL Server)

Maneras alternativas de crear un grupo de disponibilidad

• Usar el Asistente para nuevo 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 de AlwaysOn

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

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

• Crear un extremo 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 extremo 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 de problemas de configuración de grupos de disponibilidad de 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)

[Arriba]

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: el blog oficial del equipo de AlwaysOn de SQL Server

  Blogs de los ingenieros de SQL Server de CSS

• **Vídeos: **

  Microsoft SQL Server Code-Named "Denali", Serie AlwaysOn, parte 1: Introducción a la solución de alta disponibilidad de siguiente generación

  Microsoft SQL Server Code-Named "Denali", Serie AlwaysOn, parte 2: Crear una solución esencial de alta disponibilidad utilizando AlwaysOn

• **Notas del producto: **

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

  Notas del producto de Microsoft para SQL Server 2012

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

[Arriba]

Vea también

Conceptos

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)