Referencia de scripts de objetos utilitarios de SQL Server

Material de referencia de acceso para el script de objetos de utilidad de SQL Server, incluidos componentes, parámetros y solución de problemas.

Información general

El script instala procedimientos almacenados de utilidad y funciones versionados para configurar tu base de datos de SQL Server para el proceso de ingesta en Lakeflow Connect. Las tareas de instalación incluyen:

• Administración de permisos
• Configurar el seguimiento de cambios (CT)
• Configuración de captura de datos de cambios (CDC)
• Detección de plataformas
• Compatibilidad de DDL con la creación de objetos para el seguimiento de cambios de esquema

Información de versión

• Versión actual: 1.7
• Versión principal: 1
• Versión menor: 7
• Función de versión: lakeflowUtilityVersion()

Novedades en la versión 1.7

Highlights

• Corrección de un error de pérdida de datos en la evolución del esquema. En versiones anteriores, añadir una columna a una tabla que también tuviera una instancia de captura preexistente (no Lakeflow) podía ingerir la nueva columna como NULL. Esto se corrige, junto con varios fallos relacionados en la evolución de esquemas (véase Correcciones de errores).
• Los objetos de soporte DDL son ahora opcionales para el seguimiento de cambios. La tabla de auditoría DDL y el disparador ya no son necesarios para la evolución automática del esquema y están desactivados por defecto. Ponlo @CreateDdlSupportingObjects = 1lakeflowSetupChangeTracking solo si aún los quieres.
• Evolución de esquemas más fiable. Los cambios de restricción se clasifican para que los que no se rompen, como las teclas extranjeras, ya no desencadenan refrescos completos innecesarios.

Correcciones de errores

• Se corrigieron varios ADD COLUMN fallos de evolución de esquemas, incluyendo tablas que usan enmascaramiento dinámico de datos, colaciones sensibles a mayúsculas minúsculas o binarias, y un bucle de reinicialización o ingestión interrumpida cuando hay una instancia de captura preexistente.
• Se corrigieron errores de conflicto de clasificación en bases de datos con una clasificación de servidor no predeterminada.
• Se corrigieron los disparadores DDL que podían fallar al ejecutarse desde una sesión con opciones no predeterminadas SET .
• Corregido lakeflowFixPermissions para conceder los permisos requeridos con alcance de servidor desde la base de datos maestra.
• Se solucionó la desinstalación (CLEANUP modo) dejando disparadores DDL huérfanos que podían bloquear ALTER TABLE.

Otros cambios

• Añadido @AllowDisablePreExistingCaptureInstances en lakeflowSetupChangeDataCapture. Configúralo para 1 que Lakeflow Connect se haga cargo y gestione la instancia de captura CDC preexistente (no Lakeflow) de una mesa en lugar de dejarla en su lugar.

Componentes claves

Functions

lakeflowDetectPlatform()

Detecta el tipo de plataforma de SQL Server.

Devuelve: 'AZURE_SQL_DATABASE', 'AZURE_SQL_MANAGED_INSTANCE', 'AMAZON_RDS', 'ON_PREMISES'o 'UNKNOWN'

lakeflowUtilityVersion()

Detecta la versión de objetos de utilidad.

Devuelve: '1.7'

Procedimientos almacenados

lakeflowFixPermissions

Concede permisos necesarios a los usuarios para las operaciones de ingesta.

Parameters:

@Tables opciones de parámetros:

Lo que hace:

• Concesión de permisos SELECT en las vistas del sistema requeridas (sys.objects, sys.tables, sys.columns, etc.)
• Permisos EXECUTE en procedimientos almacenados del sistema (sp_tables, sp_columns_100, etc.)
• Opcionalmente concede permisos en SELECT sobre tablas de usuario basándose en el parámetro @Tables.
• Controla las diferencias específicas de la plataforma (Azure SQL Database, Instancia administrada, RDS, Local)

lakeflowSetupChangeTracking

Permite el seguimiento de cambios a nivel de base de datos y tabla, con soporte opcional para DDL (opt-in).

Parameters:

@Tables opciones de parámetros:

Lo que hace:

• Habilita el seguimiento de cambios en el nivel de base de datos si aún no está habilitado
• Cuando @CreateDdlSupportingObjects = 1, crea una tabla de auditoría DDL versionada (lakeflowDdlAudit_1_7) y un disparador para capturar los cambios de esquema
• Habilita CT en tablas especificadas (omite tablas sin claves principales)
• Concede VIEW CHANGE TRACKING permisos al usuario especificado
• CLEANUP mode: quita objetos de soporte de DDL

Comportamientos importantes:

• Omite automáticamente las tablas sin claves principales (se recomienda CDC para estas)
• Detección inteligente con el 'ALL' parámetro
• Idempotente: seguro para ejecutarse varias veces

lakeflowSetupChangeDataCapture

Habilita CDC en los niveles de base de datos y tablas con compatibilidad con DDL y administración de instancias de captura.

Parameters:

@Tables opciones de parámetros:

Lo que hace:

• Habilita CDC en el nivel de base de datos si aún no está habilitado
• Crea una tabla de seguimiento de instancias de captura (lakeflowCaptureInstanceInfo_1_7)
• Crea procedimientos auxiliares para la administración de instancias de captura:
  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7
• Crea un ALTER TABLE desencadenador para el control automático de cambios de esquema
• Habilita CDC en tablas especificadas
• Concede permisos CDC necesarios al usuario especificado.
• CLEANUP mode: elimina todos los objetos de soporte de CDC DDL

Comportamientos importantes:

• Funciona con tablas con o sin claves principales
• Controla automáticamente la rotación de instancias de captura en los cambios de esquema.
• Idempotente: seguro para ejecutarse varias veces

Compatibilidad con plataformas

• SQL Server en las instalaciones (EngineEdition 1-4)
• Azure SQL Database (EngineEdition 5)
• Instancia administrada de Azure SQL (EngineEdition 8)
• Amazon RDS para SQL Server (detectado por el patrón de nombre de servidor)

Prerrequisitos

• 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 dicha plataforma
• ** Para la configuración de CDC, la captura de cambios de datos debe estar habilitada en la plataforma.

Instrucciones de instalación

Descarga y ejecución del script

1. Descargue la versión más reciente del script:

   Descargar utility_script.sql

2. Ejecute el script:

   1. Abra el script descargado en SQL Server Management Studio (SSMS), Azure Data Studio o su cliente SQL preferido.
   2. Conéctese a la instancia de SQL Server.
   3. Confirme que está conectado a la base de datos de destino donde desea instalar los objetos de utilidad.
   4. Ejecute el script.

3. Compruebe la instalación:

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;
   

Alternativa: Ejecutar con la línea de comandos

Si prefiere usar sqlcmd:

sqlcmd -S YourServerName -d YourDatabase -E -i utility_script.sql

Ejemplo: Corrección de permisos (solo sistema)

-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser';

Ejemplo: Corrección de permisos (con acceso a tablas)

-- Grant system permissions plus access to all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'ALL';

-- Grant permissions for specific schemas
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'SCHEMAS:Sales,HR,Production';

-- Grant permissions for specific tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'Sales.Orders,HR.Employees';

Ejemplos: Configuración del seguimiento de cambios

Solo a nivel de base de datos

-- Setup CT infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = NULL,
    @User = 'myuser';

Habilitar en todas las tablas

-- Enable CT on all user tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

Configuración basada en esquemas

-- Enable CT on all tables in specific schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'myuser',
    @Retention = '3 DAYS';

Tablas específicas

-- Enable CT on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'dbo.Table1,Sales.Orders,HR.Employees',
    @User = 'myuser';

Crear objetos de soporte DDL

-- Enable CT and create the DDL audit table and trigger for schema-change capture
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser',
    @CreateDdlSupportingObjects = 1;

Ejemplos: configuración CDC

Solo a nivel de base de datos

-- Setup CDC infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = NULL,
    @User = 'myuser';

Habilitar en todas las tablas

-- Enable CDC on all user tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Tablas específicas

-- Enable CDC on specific tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'dbo.Table1,Sales.Orders',
    @User = 'myuser';

Toma posesión de instancias de captura preexistentes

-- Allow Lakeflow to disable pre-existing (non-Lakeflow) capture instances
-- during schema change handling
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser',
    @AllowDisablePreExistingCaptureInstances = 1;

Ejemplo: Enfoque híbrido

-- Step 1: Enable CT on tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

-- Step 2: Enable CDC on remaining tables (without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Ejemplo: Limpieza

-- Remove CT DDL support objects
EXEC dbo.lakeflowSetupChangeTracking
    @Mode = 'CLEANUP';

-- Remove CDC DDL support objects
EXEC dbo.lakeflowSetupChangeDataCapture
    @Mode = 'CLEANUP';

Objetos de compatibilidad con DDL creados

Se crean los siguientes objetos de soporte DDL, en función de si utiliza el seguimiento de cambios o CDC.

Para el seguimiento de cambios

Para CDC

Limitaciones del seguimiento de cambios

• Requiere claves principales: las tablas sin claves principales no pueden usar el seguimiento de cambios.
• El script omite automáticamente las tablas sin PKs y recomienda usar CDC como alternativa.

Comportamiento específico de la plataforma

• Azure SQL Database: los procedimientos almacenados del sistema son accesibles de forma predeterminada (sin EXECUTE concesiones necesarias).
• Vistas con ámbito de servidor: acceso limitado en Azure SQL Database para vistas como sys.change_tracking_databases.

Ruta de actualización

Para actualizar, vuelve a ejecutar el script de objetos de utilidad y luego vuelve a ejecutar los procedimientos de configuración. El script recrea las funciones de utilidad y elimina solo objetos heredados replicantcon prefijo de versiones anteriores. Deja tus objetos de soporte DDL de Lakeflow en su lugar, así que el disparador DDL actual sigue capturando cambios en el esquema hasta que vuelves a ejecutar los procedimientos de configuración. Para las instrucciones, consulta el Paso 1: Instalar o actualizar objetos de utilidad.

Qué ocurre durante una actualización:

• Las funciones de utilidad y los procedimientos de configuración definidos por el script se eliminan y se recrean.
• Se eliminan los objetos heredados replicantcon prefijo -de versiones anteriores.
• Los objetos de soporte existentes de Lakeflow DDL (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_*, y procedimientos y disparadores relacionados) permanecen en su lugar. Al volver a ejecutar los procedimientos de configuración, se trasladan a la nueva versión. Los objetos de la instancia de captura se mueven cada vez que vuelves a ejecutar lakeflowSetupChangeDataCapture. La tabla de auditoría DDL y el trigger solo se mueven cuando vuelves a ejecutar lakeflowSetupChangeTracking con @CreateDdlSupportingObjects = 1, porque son opt-in.
• El seguimiento de cambios y CDC permanecen habilitados en las tablas. La actualización no las deshabilita.
• Las instancias de captura CDC no se ven afectadas por el script de actualización.

Después de ejecutar el script de actualización, vuelva a ejecutar los procedimientos de instalación para volver a crear objetos de compatibilidad con DDL para la nueva versión:

• lakeflowSetupChangeTracking: con @CreateDdlSupportingObjects = 1, lleva la tabla de auditoría DDL y se reenvía a lakeflowDdlAudit_1_7. Pasa esta bandera si tu instalación usa captura de cambio de esquema DDL (tiene una lakeflowDdlAudit tabla), o si los objetos permanecen en la versión antigua.
• lakeflowSetupChangeDataCapture: vuelve a crear lakeflowCaptureInstanceInfo_1_7 y los procedimientos y desencadenadores relacionados.

Ambos procedimientos son idempotentes y seguros para volver a ejecutarse con los parámetros de configuración originales.

Esquema de control de versiones: objectName_majorVersion_minorVersion. Los objetos actuales usan el _1_7 sufijo.

procedimientos recomendados

• Ejecute siempre como db_owner o un usuario con privilegios equivalentes.
• Pruebe primero en bases de datos que no son de producción.
• Use el enfoque híbrido para una cobertura completa.
• Ejecute lakeflowFixPermissions después de la instalación para garantizar el acceso adecuado al usuario.
• Considere los períodos de retención en función de la frecuencia de ingesta.

Solución de problemas

"El usuario que ejecuta este script no es un miembro de rol "db_owner"

Solución: Ejecutar como usuario con db_owner rol

"El seguimiento de cambios no está habilitado en el catálogo"

Solución: habilite CT en el nivel de base de datos o deje que el procedimiento lo controle automáticamente.

"La captura de datos modificados no está habilitada en el catálogo"

Solución: habilite CDC en el nivel de base de datos o permita que el procedimiento lo controle automáticamente.

"Tablas omitidas debido a la falta de claves principales"

Solución: use lakeflowSetupChangeDataCapture en su lugar para estas tablas.

Integración de validación

El marco de validación de Java valida los siguientes objetos de utilidad:

Notas de migración

Si la actualización desde versiones anteriores de DDL admite objetos (era de objetos anteriores a la utilidad):

• El script limpia automáticamente los objetos heredados.
• No se requiere ninguna limpieza manual.
• La versión 1.1 consolida toda la funcionalidad en procedimientos unificados.

Recursos adicionales

• Preparación de SQL Server para la ingesta mediante el script de objetos de utilidad
• 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
• Acerca de Change Tracking (SQL Server) en la documentación de SQL Server
• ¿Qué es la captura de datos modificados (CDC)? en la documentación de SQL Server