Automatización de la replicación de los cambios de esquema en Azure SQL Data Sync

Se aplica a: Azure SQL Database

Importante

SQL Data Sync se retirará el 30 de septiembre de 2027. Considere la posibilidad de migrar a soluciones alternativas de replicación o sincronización de datos.

SQL Data Sync permite que los usuarios sincronicen datos entre bases de datos de Azure SQL Database e instancias de SQL Server en una dirección o en ambas. Una de las limitaciones actuales de SQL Data Sync es la falta de compatibilidad con la replicación de cambios de esquema. Cada vez que cambia el esquema de tabla, debe aplicar los cambios de manera manual en todos los puntos de conexión, incluida la central y todos los miembros, y luego actualizar el esquema de sincronización.

En este artículo se presenta una solución para replicar de manera automática los cambios de esquema en todos los puntos de conexión de SQL Data Sync.

1. Esta solución usa un desencadenador DDL para realizar el seguimiento de cambios de esquema.
2. El desencadenador inserta los comandos de cambios de esquema en una tabla de seguimiento.
3. Esta tabla de seguimiento se sincroniza con todos los puntos de conexión mediante el servicio Data Sync.
4. Después de la inserción, los desencadenadores DML se usan para aplicar los cambios de esquema en los otros puntos de conexión.

En este artículo se usa ALTER TABLE como ejemplo de un cambio de esquema, pero esta solución también sirve para otros tipos de cambios de esquema.

Lea este artículo con atención, especialmente las secciones Solución de problemas y Otras consideraciones, antes de empezar a implementar la replicación automatizada de cambios de esquema en el entorno de sincronización. Algunas operaciones de base de datos pueden interrumpir la solución que se describe en este artículo. Puede ser necesario tener conocimiento de dominio adicional de SQL Server y Transact-SQL para solucionar esos problemas.

Configuración de la replicación automatizada de cambios de esquema

Creación de una tabla para hacer seguimiento de los cambios de esquema

Cree una tabla para hacer seguimiento de los cambios de esquema en todas las bases de datos del grupo de sincronización:

CREATE TABLE SchemaChanges (
ID bigint IDENTITY(1,1) PRIMARY KEY,
SqlStmt nvarchar(max),
[Description] nvarchar(max)
)

Esta tabla tiene una columna de identidad para hacer seguimiento del orden de los cambios de esquema. Si es necesario, puede agregar más campos para registrar más información.

Creación de una tabla para hacer seguimiento del historial de los cambios de esquema

En todos los puntos de conexión, cree una tabla para hacer seguimiento del identificador del comando de cambios de esquema aplicado más recientemente.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Creación de un desencadenador ALTER TABLE DDL en la base de datos cuando se realizan cambios de esquema

Cree un desencadenador DDL para las operaciones ALTER TABLE. Solo debe crear este desencadenador en la base de datos donde se hicieron los cambios de esquema. Para evitar conflictos, solo permita cambios de esquema en una base de datos en un grupo de sincronización.

CREATE TRIGGER AlterTableDDLTrigger
ON DATABASE
FOR ALTER_TABLE
AS

-- You can add your own logic to filter ALTER TABLE commands instead of replicating all of them.

IF NOT (EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(512)') like 'DataSync')

INSERT INTO SchemaChanges (SqlStmt, Description)
    VALUES (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), 'From DDL trigger')

El desencadenador inserta un registro en la tabla de seguimiento de cambios de esquema para cada comando ALTER TABLE. En este ejemplo se agrega un filtro para evitar replicar los cambios que se hicieron en el esquema DataSync, porque probablemente los hizo el servicio Data Sync. Agregue más filtros si solo quiere replicar ciertos tipos de cambios de esquema.

También puede agregar más desencadenadores para replicar otros tipos de cambios de esquema. Por ejemplo, cree los desencadenadores CREATE_PROCEDURE, ALTER_PROCEDURE y DROP_PROCEDURE para replicar los cambios de procedimientos almacenados.

Creación de un desencadenador en otros puntos de conexión para aplicar los cambios de esquema durante la inserción

Este desencadenador ejecuta el comando de cambios de esquema cuando se sincroniza con otros puntos de conexión. Debe crear este desencadenador en todos los puntos de conexión, excepto en el que se hacen los cambios de esquema (es decir, en la base de datos donde el desencadenador DDL AlterTableDDLTrigger se creó en el paso anterior).

CREATE TRIGGER SchemaChangesTrigger
ON SchemaChanges
AFTER INSERT
AS
DECLARE @lastAppliedId bigint
DECLARE @id bigint
DECLARE @sqlStmt nvarchar(max)
SELECT TOP 1 @lastAppliedId=LastAppliedId FROM SchemaChangeHistory
SELECT TOP 1 @id = id, @SqlStmt = SqlStmt FROM SchemaChanges WHERE id > @lastAppliedId ORDER BY id
IF (@id = @lastAppliedId + 1)
BEGIN
    EXEC sp_executesql @SqlStmt
        UPDATE SchemaChangeHistory SET LastAppliedId = @id
    WHILE (1 = 1)
    BEGIN
        SET @id = @id + 1
        IF exists (SELECT id FROM SchemaChanges WHERE ID = @id)
            BEGIN
                SELECT @sqlStmt = SqlStmt FROM SchemaChanges WHERE ID = @id
                EXEC sp_executesql @SqlStmt
                UPDATE SchemaChangeHistory SET LastAppliedId = @id
            END
        ELSE
            BREAK;
    END
END

Este desencadenador se ejecuta después de la inserción y comprueba si el comando actual se debe ejecutar a continuación. La lógica de código garantiza que no se omita ninguna instrucción de cambios de esquema y que se apliquen todos los cambios incluso si la inserción no funciona.

Sincronización de la tabla de seguimiento de cambios de esquema en todos los puntos de conexión

Puede sincronizar la tabla de seguimiento de cambios de esquema en todos los puntos de conexión mediante el grupo de sincronización existente o uno nuevo. Asegúrese de que todos los cambios de la tabla de seguimiento se pueden sincronizar en todos los puntos de conexión, sobre todo cuando usa la sincronización en una dirección.

No sincronice la tabla del historial de cambios de esquema, porque esa tabla conserva un valor distinto en los distintos puntos de conexión.

Aplicación de los cambios de esquema en un grupo de sincronización

Solo se replican los cambios de esquema que se hacen en la base de datos donde se creó el desencadenador DDL. No se replican los cambios de esquema hechos en otras bases de datos.

Una vez que los cambios de esquema se replican a todos los puntos de conexión, también debe realizar pasos adicionales para actualizar el esquema de sincronización a fin de empezar o detener la sincronización de las columnas nuevas.

Agregar columnas nuevas

1. Realice el cambio de esquema.

2. Evite cualquier cambio de datos en los que participen las columnas nuevas hasta que haya completado el paso que crea el desencadenador.

3. Espere hasta que los cambios de esquema se apliquen a todos los puntos de conexión.

4. Actualice el esquema de base de datos y agregue la columna nueva al esquema de sincronización.

5. Los datos de la columna nueva se sincronizan durante la operación de sincronización siguiente.

Quitar columnas

1. Quite las columnas del esquema de sincronización. Data Sync detiene la sincronización de los datos de estas columnas.

2. Realice el cambio de esquema.

3. Actualice el esquema de la base de datos.

Actualizar tipos de datos

1. Realice el cambio de esquema.

2. Espere hasta que los cambios de esquema se apliquen a todos los puntos de conexión.

3. Actualice el esquema de la base de datos.

4. Si los tipos de datos nuevos y anteriores no son totalmente compatibles (por ejemplo, si cambia de int a bigint), puede producirse un error de sincronización antes de completar los pasos que crean los desencadenadores. La sincronización se completa correctamente después de un reintento.

Cambiar el nombre de las columnas o tablas

Cambiar el nombre de las columnas o tablas hace que Data Sync deje de funcionar. Cree una tabla o columna nueva, reponga los datos y elimine la tabla o columna anterior en lugar de cambiar el nombre.

Otros tipos de cambios de esquema

Para otros tipos de cambios de esquema (por ejemplo, crear procedimientos almacenados o colocar un índice), no es necesario actualizar el esquema de sincronización.

Solución de problemas de la replicación automatizada de cambios de esquema

En este artículo se describe una lógica de replicación que deja de trabajar en ciertas situaciones, por ejemplo, si hace un cambio de esquema en una base de datos local no compatible con Azure SQL Database. En ese caso, no se puede sincronizar la tabla de seguimiento de cambios de esquema. Debe corregir manualmente este problema:

1. Deshabilite el desencadenador DDL y evite cualquier cambio de esquema hasta que se corrija el problema.

2. En la base de datos del punto de conexión donde se produce el problema, deshabilite el desencadenador AFTER INSERT en el punto de conexión donde no se puede hacer el cambio de esquema. Esta acción permite sincronizar el comando de cambios de esquema.

3. Desencadene la sincronización para sincronizar la tabla de seguimiento de cambios de esquema.

4. En la base de datos del punto de conexión donde se produce el problema, consulte la tabla del historial de cambios de esquema para obtener el identificador del último comando de cambios de esquema aplicado.

5. Consulte la tabla de seguimiento de cambios de esquema para mostrar todos los comandos que tengan un identificador mayor que el valor del identificador que recuperó en el paso anterior.

   a. Omita los comandos que no se pueden ejecutar en la base de datos del punto de conexión. Tiene que resolver la incoherencia del esquema. Revierta los cambios al esquema original si la incoherencia afecta la aplicación.

   b. Aplique manualmente los comandos que se deben aplicar.

6. Actualice la tabla del historial de cambios de esquema y establezca el último identificador aplicado en el valor correcto.

7. Revise si el esquema está actualizado.

8. Vuelva a habilitar el desencadenador AFTER INSERT que se deshabilitó en el segundo paso.

9. Vuelva a habilitar el desencadenador DDL que se deshabilitó en el primer paso.

Si quiere limpiar los registros de la tabla de seguimiento de cambios de esquema, use DELETE en lugar de TRUNCATE. Nunca use DBCC CHECKIDENT para propagar la columna de identidad en la tabla de seguimiento de cambios de esquema. Si es necesario realizar una propagación, puede crear tablas de seguimiento de cambios de esquema nuevas y actualizar el nombre de la tabla en el desencadenador DDL.

Otras consideraciones

• Los usuarios de la base de datos que configuran la central y las bases de datos miembro deben tener los permisos suficientes para ejecutar los comandos de cambios de esquema.

• Puede agregar más filtros en el desencadenador DDL para replicar solo los cambios de esquema en operaciones o tablas seleccionadas.

• Solo puede hacer cambios de esquema en la base de datos donde se creó el desencadenador DDL.

• Si hace un cambio en una base de datos de SQL Server, asegúrese de que el cambio de esquema es compatible con Azure SQL Database.

• Si se hacen cambios de esquema en bases de datos distintas de la base de datos donde se creó el desencadenador DDL, no se replican esos cambios. Para evitar este problema, puede crear desencadenadores DDL para bloquear los cambios en otros puntos de conexión.

• Si tiene que cambiar el esquema de la tabla de seguimiento de cambios de esquema, deshabilite el desencadenador DDL antes de hacer el cambio y aplique manualmente el cambio en todos los puntos de conexión. No funciona actualizar el esquema de un desencadenador AFTER INSERT en la misma tabla.

• No use DBCC CHECKIDENT para propagar la columna de identidad.

• No use TRUNCATE para limpiar los datos en la tabla de seguimiento de cambios de esquema.

Contenido relacionado

• ¿Qué es SQL Data Sync para Azure?
• Tutorial: Configuración de SQL Data Sync entre Azure SQL Database y SQL Server
• Uso de PowerShell para sincronizar datos entre varias bases de datos en Azure SQL Database
• Uso de PowerShell para sincronizar datos entre SQL Database y SQL Server
• Data Sync Agent para SQL Data Sync
• Procedimientos recomendados para SQL Data Sync de Azure
• Supervisión y ajuste del rendimiento en base de datos de Azure SQL e instancia administrada de Azure SQL
• Solución de problemas de SQL Data Sync
• Usar PowerShell para actualizar el esquema de sincronización en un grupo de sincronización existente