Uso de la propagación automática para inicializar un grupo de disponibilidad Always OnUse automatic seeding to initialize an Always On availability group

SE APLICA A: síSQL Server noAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Con SQL Server 2016, se introduce la propagación automática de grupos de disponibilidad.SQL Server 2016 introduced automatic seeding of availability groups. Cuando se crea un grupo de disponibilidad con propagación automática, SQL Server crea automáticamente las réplicas secundarias para cada base de datos del grupo.When you create an availability group with automatic seeding, SQL Server automatically creates the secondary replicas for every database in the group. Ya no es necesario realizar copias de seguridad de las réplicas secundarias ni restaurarlas de forma manual.You no longer have to manually back up and restore secondary replicas. Para habilitar la propagación automática, cree el grupo de disponibilidad con T-SQL o use la versión más reciente de SQL Server Management Studio.To enable automatic seeding, create the availability group with T-SQL or use the latest version of SQL Server Management Studio.

Para obtener información general, consulte Propagación automática de réplicas secundarias.For background information, see Automatic seeding for secondary replicas.

PrerequisitesPrerequisites

En SQL Server 2016, la propagación automática exige que la ruta de acceso del archivo de datos y de registro sea la misma en cada instancia de SQL Server que participe en el grupo de disponibilidad.In SQL Server 2016, automatic seeding requires that the data and log file path is the same on every SQL Server instance participating in the availability group. En SQL Server 2017, puede usar rutas de acceso diferentes. En cambio, Microsoft recomienda usar las mismas rutas cuando todas las réplicas se hospeden en la misma plataforma (por ejemplo, Windows o Linux).In SQL Server 2017, you can use different paths, however Microsoft recommends using the same paths when all replicas are hosted on the same platform (for example either Windows or Linux). Los grupos de disponibilidad multiplataforma usan rutas de acceso diferentes para las réplicas.Cross-platform availability groups have different paths for the replicas. Para obtener más información, consulte Diseño de disco.For details, see Disk layout.

La propagación del grupo de disponibilidad se comunica a través del punto de conexión de creación de reflejo de la base de datos.Availability group seeding communicates over the database mirroring endpoint. Abra las reglas de firewall de entrada al puerto del punto de conexión de creación de reflejo en cada servidor.Open inbound firewall rules to the mirroring endpoint port on each server.

Las bases de datos de un grupo de disponibilidad deben estar en modelo de recuperación completa.Databases in an availability group must be in full recovery model. La base de datos debe tener una copia de seguridad completa actual y una copia de seguridad de registro de transacciones.The database needs to have a current full backup and transaction log backup. Estos archivos de copia de seguridad no se usan para la propagación automática, pero son necesarios para incluir la base de datos en un grupo de disponibilidad.These backup files are not used for automatic seeding, but they are required before including the database in an availability group.

Crear grupos de disponibilidad con propagación automáticaCreate availability group with automatic seeding

Para crear un grupo de disponibilidad con propagación automática, establezca SEEDING_MODE=AUTOMATIC.To create an availability group with automatic seeding, set SEEDING_MODE=AUTOMATIC.

En el ejemplo siguiente se crea un grupo de disponibilidad en un clúster de conmutación por error de dos nodos de Windows Server.The following example creates an availability group on a two node windows server failover cluster. Antes de ejecutar los scripts, actualice los valores del entorno.Before running the scripts, update the values for your environment.

  1. Cree los puntos de conexión.Create the endpoints. Cada servidor necesita un punto de conexión.Each server needs an endpoint. El script siguiente crea un punto de conexión que usa el puerto TCP 5022 del agente de escucha.The following script creates an endpoint that uses TCP port 5022 for the listener. Establezca <endpoint_name> y LISTENER_PORT de modo que coincidan con el entorno y ejecute el script en ambos servidores:Set <endpoint_name> and LISTENER_PORT to match your environment and run the script on both servers:

    CREATE ENDPOINT [<endpoint_name>] 
        STATE=STARTED
        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
        FOR DATA_MIRRORING (
            ROLE = ALL, 
            AUTHENTICATION = WINDOWS NEGOTIATE, 
            ENCRYPTION = REQUIRED ALGORITHM AES
            )
    GO
    
  2. Cree el grupo de disponibilidad.Create the availability group. El siguiente script crea el grupo de disponibilidad.The following script creates the availability group. Actualice los valores en corchetes angulares <> del nombre del grupo, los nombres de servidores y los nombres de dominios y ejecútelos en la instancia principal de SQL Server.Update the values in angle brackets <> for the group name, server names, and domain names, and run it on the primary instance of SQL Server.

    CREATE AVAILABILITY GROUP [<availability_group_name>]
        FOR DATABASE db1
        REPLICA ON'<*primary_server*>'
        WITH (ENDPOINT_URL = N'TCP://<primary_server>.<fully_qualified_domain_name>:5022', 
            FAILOVER_MODE = AUTOMATIC, 
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
            BACKUP_PRIORITY = 50, 
            SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), 
            SEEDING_MODE = AUTOMATIC),
        N'<secondary_server>' WITH (ENDPOINT_URL = N'TCP://<secondary_server>.<fully_qualified_domain_name>:5022', 
            FAILOVER_MODE = AUTOMATIC, 
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
            BACKUP_PRIORITY = 50, 
            SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), 
            SEEDING_MODE = AUTOMATIC);
    GO
    
  3. Una la instancia del servidor secundario al grupo de disponibilidad y conceda permiso al grupo de disponibilidad para crear bases de datos.Join the secondary server instance to the availability group and grant create database permission to the availability group. Actualice el script siguiente; sustituya los valores en corchetes angulares <> de su entorno y ejecútelo en la instancia de réplica de SQL Server:Update the following script, replace the values in angle brackets <> for your environment, and run it on the secondary replica instance of SQL Server:

    ALTER AVAILABILITY GROUP [<availability_group_name>] JOIN
    GO  
    ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
    GO
    

SQL Server creará automáticamente la réplica de base de datos en el servidor secundario.SQL Server automatically creates the database replica on the secondary server. Si la base de datos es grande, completar la sincronización de la base de datos puede llevar algún tiempo.If the database is large, it may take some time to complete synchronization of the database. Si una base de datos está en un grupo de disponibilidad configurado para la propagación automática, puede consultar la vista del sistema sys.dm_hadr_automatic_seeding para supervisar el progreso de la propagación.If a database is in an availability group that is configured for automatic seeding, you can query sys.dm_hadr_automatic_seeding system view to monitor the seeding progress. La siguiente consulta devuelve una fila por cada base de datos que esté en un grupo de disponibilidad configurado para la propagación automática.The following query returns one row for every database that is in an availability group configured for automatic seeding.

SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos 
    JOIN sys.availability_databases_cluster db 
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag 
        ON autos.ag_id = ag.group_id

Evitar la propagación automática después de un grupo de disponibilidadPrevent automatic seeding after an availability group

Para evitar temporalmente que la réplica principal propague más bases de datos a la réplica secundaria, puede denegar el permiso para crear bases de datos al grupo de disponibilidad.To temporarily prevent the primary replica from seeding more databases to the secondary replica, you can deny the availability group permission to create databases. Ejecute la consulta siguiente en la instancia que hospeda la réplica secundaria para denegar al grupo de disponibilidad el permiso para crear bases de datos de réplica.Run the following query on the instance that hosts the secondary replica in order to deny the availability group permission to create replica databases.

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    DENY CREATE ANY DATABASE
GO

Habilitar la propagación automática en un grupo de disponibilidad existenteEnable automatic seeding on an existing availability group

Puede establecer la propagación automática en una base de datos existente.You can set automatic seeding on an existing database. El siguiente comando modificará un grupo de disponibilidad para usar la propagación automática.The following command changes an availability group to use automatic seeding. Ejecute el comando siguiente en la réplica principal.Run the following command on the primary replica.

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    MODIFY REPLICA ON '<secondary_node>' 
    WITH (SEEDING_MODE = AUTOMATIC)
GO

El comando anterior obliga a una base de datos a reiniciar la propagación si es necesario.The preceding command forces a database to restart seeding if needed. Por ejemplo, si se produce un error de propagación debido a una insuficiencia de espacio en disco en la réplica secundaria, puede ejecutar ALTER AVAILABILITY GROUP ... WITH (SEEDING_MODE=AUTOMATIC) para reiniciar la propagación después de agregar espacio libre.For example, if seeding fails because of insufficient disk space on the secondary replica, run ALTER AVAILABILITY GROUP ... WITH (SEEDING_MODE=AUTOMATIC) to restart seeding after you have added free space.

Detener la propagación automáticaStop automatic seeding

Para detener la propagación automática de un grupo de disponibilidad, ejecute el script siguiente en la réplica principal:To stop automatic seeding for an availability group, run the following script on the primary replica:

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    MODIFY REPLICA ON '<secondary_node>'   
    WITH (SEEDING_MODE = MANUAL)
GO

Con el script anterior, se cancelarán las réplicas que se estén propagando en ese momento y se evitará que SQL Server inicialice automáticamente cualquier réplica en este grupo de disponibilidad.The preceding script cancels any replicas that are currently seeding, and prevents SQL Server from automatically initializing any replicas in this availability group. No se detendrá la sincronización de las réplicas que ya se hayan inicializado.It does not stop synchronization for any replicas that are already initialized.

Supervisar grupos de disponibilidad de propagación automáticaMonitor automatic seeding availability group

Usar vistas de administración dinámica del sistema para supervisar la propagaciónUse system dynamic management views to monitor seeding

Las siguientes vistas del sistema muestran el estado de la propagación automática de SQL Server.The following system views show the status of SQL Server automatic seeding.

sys.dm_hadr_automatic_seedingsys.dm_hadr_automatic_seeding

En la réplica principal, consulte sys.dm_hadr_automatic_seeding para comprobar el estado del proceso de propagación automática.On the primary replica, query sys.dm_hadr_automatic_seeding to check the status of the automatic seeding process. La vista devuelve una fila por cada proceso de propagación.The view returns one row for each seeding process. Por ejemplo:For example:

SELECT start_time, 
    completion_time
    is_source,
    current_state,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding

sys.dm_hadr_physical_seeding_statssys.dm_hadr_physical_seeding_stats

En la réplica principal, consulte la vista de administración dinámica sys.dm_hadr_physical_seeding_stats para ver las estadísticas físicas de cada proceso de propagación que se esté ejecutando en ese momento.On the primary replica, query sys.dm_hadr_physical_seeding_stats DMV to see the physical statistics for each seeding process that is currently running. La siguiente consulta devuelve filas cuando se está ejecutando la propagación:The following query returns rows when seeding is running:

SELECT * FROM sys.dm_hadr_physical_seeding_stats;

Las columnas total_disk_io_wait_time_ms y total_network_wait_time_ms se pueden usar para determinar el cuello de botella del rendimiento en el proceso Propagación automática.The two columns total_disk_io_wait_time_ms and the total_network_wait_time_ms can be used to determinte performance bottleneck in the Automatic seeding process. Las dos columnas también están presentes en el evento extendido hadr_physical_seeding_progress.The two columns are also present in the hadr_physical_seeding_progress extended event.

total_disk_io_wait_time_ms representa el tiempo empleado por el subproceso de copia de seguridad/restauración durante la espera en el disco.total_disk_io_wait_time_ms represents the time spent by the backup/restore thread while waiting on the disk. Este valor es acumulativo desde el inicio de la operación de propagación.This value is cummulative since the start of the seeding operation. Si los discos no están listos para leer o escribir la secuencia de copia de seguridad, el subproceso de copia de seguridad/restauración pasará a un estado de suspensión y se reactivará cada segundo para comprobar si el disco está listo.If the disks are not ready for reading or writing the backup stream, the backup/restore thread transitions into a sleep state and wakes up every one second to check if the disk is ready.

total_network_wait_time_ms se interpreta de otra manera para las réplicas principal y secundaria.total_network_wait_time_ms is interpreted diffrently for the Primary and the Secondary replica. En la réplica principal, este contador representa el tiempo de control del flujo de red.At the primary replica this counter represents the network flow control time. En la réplica secundaria, representa el tiempo que espera el subproceso de restauración hasta que haya un mensaje disponible para escribirlo en el disco.On the secondary replica this represents the time the restore thread is waiting for a message to be availabile for writing to the disk.

Diagnosticar la inicialización de la base de datos mediante la propagación automática en el registro de erroresDiagnose database initialization using automatic seeding in the error log

Al agregar una base de datos a un grupo de disponibilidad configurado para la propagación automática, SQL Server realiza una copia de seguridad VDI a través del punto de conexión del grupo de disponibilidad.When you add a database to an availability group configured for automatic seeding, SQL Server performs a VDI backup over the availability group endpoint. Revise el registro de errores de SQL Server para obtener información sobre cuándo se completó la copia de seguridad y cuándo se sincronizó la secundaria.Review the SQL Server error log for information on when the backup completed and the secondary was synchronized.

Diagnosticar el estado de nivel de base de datos con eventos extendidosDiagnose database level health with extended events

La propagación automática tiene nuevos eventos extendidos para realizar el seguimiento de las modificaciones de estado, los errores y las estadísticas de rendimiento durante la inicialización.Automatic seeding has new extended events for tracking state change, failures, and performance statistics during initialization.

Por ejemplo, este script crea una sesión de eventos extendidos que capture eventos relacionados con la propagación automática:For example, this script creates an extended events session that captures events related to automatic seeding:

CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER 
    ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
    ADD EVENT sqlserver.hadr_automatic_seeding_timeout,
    ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,
    ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_failure,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_progress,
    ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_submit_callback
    ADD TARGET package0.event_file(
        SET filename=N'autoseed.xel',
            max_file_size=(5),
            max_rollover_files=(4)
        )
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    )
GO 

ALTER EVENT SESSION AlwaysOn_autoseed ON SERVER STATE=START
GO 

La tabla siguiente enumera los eventos extendidos relacionados con la propagación automática:The following table lists extended events related to automatic seeding:

NombreName DescripciónDescription
hadr_db_manager_seeding_request_msghadr_db_manager_seeding_request_msg Mensaje de solicitud propagación.Seeding request message.
hadr_physical_seeding_backup_state_changehadr_physical_seeding_backup_state_change Cambio de estado del lado de copia de seguridad de propagación física.Physical seeding backup side state change.
hadr_physical_seeding_restore_state_changehadr_physical_seeding_restore_state_change Cambio de estado del lado de restauración de propagación física.Physical seeding restore side state change.
hadr_physical_seeding_forwarder_state_changehadr_physical_seeding_forwarder_state_change Cambio de estado del lado de reenviador de propagación física.Physical seeding forwarder side state change.
hadr_physical_seeding_forwarder_target_state_changehadr_physical_seeding_forwarder_target_state_change Cambio de estado del lado de destino de reenviador de propagación física.Physical seeding forwarder target side state change.
hadr_physical_seeding_submit_callbackhadr_physical_seeding_submit_callback Evento de devolución de llamada del envío de propagación física.Physical seeding submit callback event.
hadr_physical_seeding_failurehadr_physical_seeding_failure Evento de error de propagación física.Physical seeding failure event.
hadr_physical_seeding_progresshadr_physical_seeding_progress Evento de progreso de propagación física.Physical seeding progress event.
hadr_physical_seeding_schedule_long_task_failurehadr_physical_seeding_schedule_long_task_failure Evento de error de tarea larga de programación de propagación física.physical seeding schedule long task failure event.
hadr_automatic_seeding_starthadr_automatic_seeding_start Se produce cuando se envía una operación de propagación automática.Occurs when an automatic seeding operation is submitted.
hadr_automatic_seeding_state_transitionhadr_automatic_seeding_state_transition Se produce cuando cambia el estado de una operación de propagación automática.Occurs when an automatic seeding operation changes state.
hadr_automatic_seeding_successhadr_automatic_seeding_success Se produce cuando se realiza correctamente una operación de propagación automática.Occurs when an automatic seeding operation succeeds.
hadr_automatic_seeding_failurehadr_automatic_seeding_failure Se produce cuando se realiza una operación de propagación automática incorrectamente.Occurs when an automatic seeding operation fails.
hadr_automatic_seeding_timeouthadr_automatic_seeding_timeout Se produce cuando se agota el tiempo de espera de una operación de propagación automática.Occurs when an automatic seeding operation times out.

Otras consideraciones de solución de problemasOther troubleshooting considerations

Supervisar la propagación automáticaMonitor when automatic seeding

Consulte sys.dm_hadr_physical_seeding_stats para ver los procesos de propagación automática en ejecución.Query sys.dm_hadr_physical_seeding_stats for currently running automatic seeding processes. La vista devuelve una fila por cada base de datos.The view returns one row for each database. Por ejemplo:For example:

SELECT local_database_name, 
    role_desc, 
    internal_state_desc, 
    transfer_rate_bytes_per_second, 
    transferred_size_bytes, 
    database_size_bytes, 
    start_time_utc, 
    end_time_utc, estimate_time_complete_utc, 
    total_disk_io_wait_time_ms, 
    total_network_wait_time_ms, 
    is_compression_enabled 
FROM sys.dm_hadr_physical_seeding_stats

Solucionar el problema de que una base de datos no aparezca en un grupo de disponibilidad configurado para la propagación automáticaTroubleshoot why a database fails to appear in an availability group configured for automatic seeding

Cuando una base de datos no aparece como parte de un grupo de disponibilidad con la propagación automática habilitada, es probable que hubiera un error en la propagación automática.When a database fails to appear as part of an availability group with automatic seeding enabled, the automatic seeding likely failed. Esto evita la adición de la base de datos al grupo de disponibilidad en la réplica principal y secundaria.This prevents addition of the database to the availability group on either the primary and secondary replica. Consulte sys.dm_hadr_automatic_seeding en las réplicas principal y secundaria.Query sys.dm_hadr_automatic_seeding on both the primary and secondary replicas. Por ejemplo, ejecute la consulta siguiente para identificar el estado de error de propagación automática.For example, run the following query to identify failure state of automatic seeding.

SELECT start_time, 
    completion_time, 
    is_source, 
    current_state, 
    failure_state, 
    failure_state_desc, 
    error_code 
FROM sys.dm_hadr_automatic_seeding

Propagación automática y consideraciones de rendimientoAutomatic seeding and performance considerations

SQL Server usa un número fijo de subprocesos para la propagación automática.SQL Server uses a fixed number of threads for automatic seeding. En la instancia principal, SQL Server usa un subproceso por LUN para leer los cambios.On the primary instance, SQL Server uses one thread per LUN to read changes. En la instancia secundaria, SQL Server usa un subproceso por LUN para inicializar la base de datos.On the secondary instance SQL Server uses one thread per LUN to initialize database.

Establezca la marca de seguimiento 9567 en la réplica principal para habilitar la compresión del flujo de datos durante la propagación automática.Set trace flag 9567 on the primary replica to enable compression of the data stream during automatic seeding. Esto puede reducir considerablemente el tiempo de transferencia de propagación automática, aunque también aumentará el uso de CPU.This can significantly reduce the transfer time of automatic seeding, however it also increases the CPU usage. Para obtener más información, consulte Tune compression for availability group (Optimizar la compresión para el grupo de disponibilidad).For more information, see Tune compression for availability group.

Cuándo no usar la propagación automáticaWhen not to use automatic seeding

En algunos casos, la propagación automática puede no ser óptima para inicializar una réplica secundaria.In some scenarios automatic seeding may not be optimal for initializing a secondary replica. Durante la propagación automática, SQL Server realiza una copia de seguridad a través de la red para la inicialización.During automatic seeding, SQL Server performs a backup over the network for initialization. Este proceso puede ser lento si las bases de datos son muy grandes o si la réplica secundaria es remota.This process can be slow if databases are very large, or the secondary replica is remote. No se puede truncar el registro de transacciones de estas bases de datos durante el proceso de copia de seguridad, por lo que un proceso de inicialización prolongado en una base de datos ocupada puede dar lugar a un crecimiento considerable del registro de transacciones.The transaction log for these databases cannot be truncated during the backup process, so a prolonged initialization process on a busy database can result in significant transaction log growth. Antes de agregar una base de datos a un grupo de disponibilidad con propagación automática, evalúe el tamaño de la base de datos, la carga y la distancia al sitio entre réplicas.Before adding a database to an availability group with automatic seeding, evaluate the database size, load, and site distance between replicas.

RecursosResources

CREATE AVAILABILITY GROUP (Transact-SQL)CREATE AVAILABILITY GROUP (Transact-SQL)

AlwaysOn Availability Groups Troubleshooting and Monitoring Guide (Guía de solución de problemas y supervisión de grupos de disponibilidad AlwaysOn)AlwaysOn Availability Groups Troubleshooting and Monitoring Guide