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

Artículo
04/26/2023

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.

Nota

Para ver una introducción a los grupos de disponibilidad, consulte Introducción a los grupos de disponibilidad AlwaysOn (SQL Server).

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, consulte 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) o Crear un grupo de disponibilidad (SQL Server PowerShell).

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.

Tarea	Instrucciones Transact-SQL	Dónde realizar la tarea*****
Crear extremo de creación de reflejo de la base de datos (una vez por instancia de SQL Server )	CREATE ENDPOINT endpointName ... FOR DATABASE_MIRRORING	Se ejecuta en cada instancia del servidor que carece de extremo de creación de reflejo de la base de datos.
Crear grupo de disponibilidad	CREATE AVAILABILITY GROUP	Se ejecuta en la instancia del servidor que va a hospedar la réplica principal inicial.
Unir la réplica secundaria al grupo de disponibilidad	ALTER AVAILABILITY GROUP group_name JOIN	Se ejecuta en cada una de las instancias del servidor que hospedan una réplica secundaria.
Preparar la base de datos secundaria	BACKUP y RESTORE.	Se crean las copias de seguridad de la instancia del servidor que hospeda la réplica principal. Se restauran las copias de seguridad en cada una de las instancias del servidor que hospedan una réplica secundaria utilizando RESTORE WITH NORECOVERY.
Iniciar la sincronización de datos uniendo cada base de datos secundaria al grupo de disponibilidad	ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name	Se ejecuta en cada una de las instancias del servidor que hospedan una réplica secundaria.

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

Usar Transact-SQL

Nota

Para obtener un procedimiento de configuración de ejemplo que contiene ejemplos de código de cada una de estas instrucciones Transact-SQL, vea Ejemplo: Configuración de un grupo de disponibilidad que usa la autenticación de Windows.

Conéctese a la instancia del servidor que va a hospedar la réplica principal.
Cree el grupo de disponibilidad mediante la instrucción de Transact-SQL CREATE AVAILABILITY GROUP.
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).
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.
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.

Rol inicial	Sistema	Hospeda la instancia de SQL Server
Principal	`COMPUTER01`	`AgHostInstance`
Secundario	`COMPUTER02`	instancia predeterminada.

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

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

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  
```
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).
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  
```

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

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

Marcador de posición	Descripción
\\FILESERVER\SQLbackups	Recurso compartido de copia de seguridad ficticio.
\\FILESERVER\SQLbackups\MyDb1.bak	Archivo de copia de seguridad de MyDb1.
\\FILESERVER\SQLbackups\MyDb2.bak	Archivo de copia de seguridad de MyDb2.
7022	Número de puerto asignado a cada extremo de creación de reflejo de la base de datos.
COMPUTER01\AgHostInstance	Instancia del servidor que hospeda la réplica principal inicial.
COMPUTER02	Instancia del servidor que hospeda la réplica secundaria inicial. Esta es la instancia del servidor predeterminada en `COMPUTER02`.
dbm_endpoint	Nombre especificado para cada extremo de creación de reflejo de la base de datos.
MyAG	Nombre del grupo de disponibilidad de ejemplo.
MyDb1	Nombre de la primera base de datos de ejemplo.
MyDb2	Nombre de la segunda base de datos de ejemplo.
DOMAIN1\user1	Cuenta de servicio de la instancia del servidor que va a hospedar la réplica principal inicial.
DOMAIN2\user2	Cuenta de servicio de la instancia del servidor que va a hospedar la réplica secundaria inicial.
TCP://COMPUTER01.Adventure-Works.com:7022	Dirección URL de la instancia AgHostInstance de SQL Server en COMPUTER01.
TCP://COMPUTER02.Adventure-Works.com:5022	Dirección URL del extremo de la instancia predeterminada de SQL Server en COMPUTER02.

Nota

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

Related Tasks

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

Para completar la configuración del grupo de disponibilidad

Maneras alternativas de crear un grupo de disponibilidad

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

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

Contenido relacionado

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)

Compartir a través de

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

Requisitos previos, restricciones y recomendaciones

Permisos

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

Resumen de las tareas e instrucciones Transact-SQL correspondientes

Usar Transact-SQL

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

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

Related Tasks

Contenido relacionado

Consulte también

Comentarios

Recursos adicionales