Preparación de SQL Server para la ingesta mediante el script de objetos de utilidad

Complete las tareas de configuración de la base de datos de SQL Server para ingerir en Azure Databricks mediante Lakeflow Connect.

Requisitos

• El usuario que ejecuta el script debe ser miembro del db_owner rol.
• Para la configuración de CT: el seguimiento de cambios debe estar disponible en la plataforma.
• Para la configuración CDC: la captura de datos modificados debe estar disponible en la plataforma.

Paso 1: Instalar objetos de utilidad

En este paso se instalan los procedimientos almacenados de la utilidad y las funciones necesarias para la instalación de SQL Server. Para obtener más información sobre lo que se instala, consulte la referencia de scripts de objetos de utilidad de SQL Server.

1. Descargue el script: utility_script.sql

2. Abra el script en SQL Server Management Studio (SSMS), Azure Data Studio o en el cliente SQL preferido.

3. Conéctese a la instancia de SQL Server como usuario que tenga el rol db_owner.

4. Asegúrese de que está conectado a la base de datos de destino.

5. Ejecute el script.

6. Compruebe la instalación:

   SELECT dbo.lakeflowUtilityVersion_1_1() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;
   

Paso 2: Habilitación del seguimiento de cambios (para tablas con claves principales)

El seguimiento de cambios es un mecanismo ligero que realiza un seguimiento de los cambios en las filas de la tabla. Este paso habilita CT en el nivel de base de datos en tablas especificadas y configura objetos de compatibilidad de DDL para controlar los cambios de esquema. Para obtener más información, consulte lakeflowSetupChangeTracking en la referencia de scripts de objetos de utilidad de SQL Server.

-- Enable change tracking on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'Sales.Orders,Production.Products,HR.Employees',
    @User = 'your_ingestion_user',
    @Retention = '2 DAYS';

Opciones alternativas:

• Para todas las tablas con claves principales: @Tables = 'ALL'
• Para esquemas específicos: @Tables = 'SCHEMAS:Sales,HR,Production'
• Solo para la configuración de nivel de base de datos (sin habilitación de tabla): @Tables = NULL

Paso 3: Habilitación de la captura de datos modificados (para tablas sin claves principales)

CDC captura la actividad de inserción, actualización y eliminación, y es especialmente útil para las tablas sin claves principales. Este paso habilita CDC en el nivel de base de datos, configura la administración de instancias de captura y crea desencadenadores para el control automático de cambios de esquema. Para obtener más información, consulte lakeflowSetupChangeDataCapture en la referencia de scripts de objetos de utilidad de SQL Server.

-- Enable CDC on specific tables (particularly those without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'your_ingestion_user';

Opciones alternativas:

• Para todas las tablas: @Tables = 'ALL'
• Para esquemas específicos: @Tables = 'SCHEMAS:Sales,HR'
• Solo para la configuración de nivel de base de datos: @Tables = NULL

Gestión de instancias de captura

Lakeflow Connect usa una convención de nomenclatura basada en prefijos para administrar instancias de captura CDC sin afectar a las instancias de captura preexistentes creadas por otros sistemas o procesos.

Nomenclatura de instancias de captura de Lakeflow

Lakeflow Connect crea y administra instancias de captura mediante el siguiente patrón de nomenclatura:

• lakeflow_<schema>_<table>_1
• lakeflow_<schema>_<table>_2

Lakeflow Connect solo administra las instancias de captura que coinciden con este patrón de nomenclatura. Las instancias de captura preexistentes con nombres diferentes se conservan y no se ven afectadas por las operaciones de Lakeflow.

Requisitos de ranura de la instancia de captura

SQL Server permite un máximo de 2 instancias de captura por tabla. Para que Lakeflow Connect funcione con CDC:

• Al menos una de las dos ranuras de instancia de captura debe estar disponible para que Lakeflow cree su instancia prefijada lakeflow_.
• Si ambas ranuras ya están ocupadas por instancias de captura que no son de Lakeflow, Lakeflow Connect no puede crear y administrar su propia instancia de captura. Aunque Lakeflow puede leer desde una instancia de captura preexistente, no puede realizar operaciones completas de actualización o evolución del esquema.

La coexistencia con otros consumidores de CDC

Lakeflow Connect puede coexistir de forma segura con otros consumidores CDC en la misma tabla:

• Las instancias de captura preexistentes se conservan durante todas las operaciones de Lakeflow (por ejemplo, actualización completa y evolución del esquema).
• Lakeflow solo elimina y vuelve a crear sus propias instancias prefijadas lakeflow_ cuando es necesario.
• Otros sistemas que consumen datos CDC de instancias de captura que no son de Lakeflow siguen funcionando sin interrupciones.

Operaciones que vuelven a crear instancias de captura de Lakeflow:

Las siguientes operaciones hacen que Lakeflow elimine y vuelva a crear sus instancias de captura que tienen el prefijo lakeflow_ (pero no otras):

• Operaciones de actualización completa
• Agregar columnas a tablas (ADD COLUMN)

Escenario de ejemplo:

Si una tabla tiene una instancia de captura preexistente denominada my_app_cdc:

1. Lakeflow Connect crea lakeflow_schema_table_1.
2. Ambas instancias de captura coexisten de forma segura.
3. Cuando Lakeflow realiza una actualización completa o una evolución del esquema, solo vuelve a recrear lakeflow_schema_table_1.
4. La my_app_cdc instancia permanece intacta y continúa funcionando para el otro sistema.

Paso 4: Conceder permisos adicionales (si es necesario)

Este paso concede los permisos del sistema y de nivel de tabla necesarios para el usuario de ingestión. Aunque los pasos 2 y 3 conceden permisos específicos de CT y CDC, este paso garantiza que el usuario tenga todos los permisos necesarios SELECT . Para obtener más información, consulte lakeflowFixPermissions en la referencia de scripts de objetos de utilidad de SQL Server.

-- Grant system-level and table-level permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'Sales.Orders,Production.Products,HR.Employees';

Opciones alternativas:

• Para todas las tablas: @Tables = 'ALL'
• Solo permisos del sistema: @Tables = NULL
• Esquemas específicos: @Tables = 'SCHEMAS:Sales,HR'

Paso 5: Comprobar la configuración

Ejecute las siguientes consultas para confirmar que el seguimiento de cambios y CDC están configurados correctamente en la base de datos y las tablas:

-- Check Change Tracking status
SELECT
    d.name AS DatabaseName,
    ctd.is_auto_cleanup_on,
    ctd.retention_period,
    ctd.retention_period_units_desc
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON ctd.database_id = d.database_id
WHERE d.name = DB_NAME();

-- Check tables with Change Tracking enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.begin_version,
    ct.cleanup_version
FROM sys.change_tracking_tables ct
INNER JOIN sys.tables t ON ct.object_id = t.object_id;

-- Check CDC status
SELECT
    DB_NAME() AS DatabaseName,
    is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

-- Check tables with CDC enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.capture_instance,
    ct.start_lsn,
    ct.create_date
FROM cdc.change_tables ct
INNER JOIN sys.tables t ON ct.source_object_id = t.object_id;

Ejemplo: Enfoque híbrido

-- Step 1: Already completed (script installed)

-- Step 2 & 3: Enable both CT and CDC
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'lakeflow_user';

-- Step 4: Grant all necessary permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Escenarios frecuentes

Escenario 1: Solo seguimiento de cambios (esquemas específicos)

EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,Production',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'SCHEMAS:Sales,Production';

Escenario 2: CDC solo (tablas específicas)

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords',
    @User = 'lakeflow_user';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords';

Escenario 3: Enfoque híbrido (CT para algunos esquemas, CDC para tablas específicas)

-- Enable CT on transactional schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'lakeflow_user',
    @Retention = '3 DAYS';

-- Enable CDC on specific staging tables without primary keys
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'lakeflow_user';

-- Grant permissions on all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Recursos adicionales

• Referencia de scripts de objetos utilitarios de SQL Server
• Configuración de Microsoft SQL Server para la ingesta en Azure Databricks
• Requisitos de usuario de base de datos de Microsoft SQL Server
• Seguimiento de los cambios de datos (SQL Server) en la documentación de SQL Server