Nota
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
Se aplica a:
SQL ServerAzure SQL Managed Instance
Los datos modificados están a disposición de los consumidores de capturas de datos modificados a través de las funciones con valores de tabla (TVF). Todas las consultas de estas funciones requieren dos parámetros para definir el intervalo de números de flujo de registro (LSN) que se pueden elegir al desarrollar el conjunto de resultados devuelto. Se considera que los valores superior e inferior de LSN que limitan el intervalo están incluidos dentro del intervalo.
Se proporcionan varias funciones que ayudan a determinar los valores de LSN adecuados para utilizarlos al consultar una TVF. La función sys.fn_cdc_get_min_lsn devuelve el LSN más pequeño asociado a un intervalo de validez de la instancia de captura. El intervalo de validez es el intervalo de tiempo durante el cual los datos modificados están actualmente disponibles en sus instancias de captura. La función sys.fn_cdc_get_max_lsn devuelve el LSN más grande del intervalo de validez. Las funciones sys.fn_cdc_map_time_to_lsn y sys.fn_cdc_map_lsn_to_time están disponibles para ayudar a ubicar los valores LSN en una escala de tiempo convencional.
Dado que la captura de datos modificados utiliza intervalos de consulta cerrados, a veces es necesario generar el valor de LSN siguiente en un flujo para garantizar que los cambios no estén duplicados en ventanas de consulta consecutivas. Las funciones sys.fn_cdc_increment_lsn y sys.fn_cdc_decrement_lsn son útiles cuando es necesario realizar un ajuste incremental en un valor LSN.
Validar límites de LSN
Se recomienda validar los límites de LSN que se van a utilizar en una consulta de TVF antes de su uso. Los valores límite nulos, o los que queden fuera del intervalo de validez de una instancia de captura, harán que una TVF de captura de datos de cambios devuelva un error.
Por ejemplo, el error siguiente se devuelve en una consulta de todos los cambios cuando un parámetro que se utiliza para definir el intervalo de la consulta no es válido o está fuera del intervalo, o bien cuando la opción de filtro de filas no es válida.
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...
El error correspondiente devuelto para una consulta net changes es el siguiente:
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...
Nota:
Es obvio que el mensaje 313 es confuso y no comunica la causa real del error. Este uso poco natural se debe a la imposibilidad de generar un error explícito desde dentro de una TVF. No obstante, se consideró que era preferible devolver un error reconocible, aunque inexacto, a devolver simplemente un resultado vacío. Un conjunto de resultados vacío no sería discernible de una consulta válida que no devolviese ningún cambio.
Los errores de autorización devolverán errores al consultar todos los cambios, como se muestra a continuación:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.
Lo mismo ocurre al consultar los cambios netos:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.
En SQL Server Management Studio, vea la plantilla Enumerar cambios de red mediante TRY CATCH para obtener una demostración de cómo interceptar estos errores conocidos de TVF y devolver información más significativa sobre el error.
Sugerencia
Para buscar plantillas de captura de datos modificados en SQL Server Management Studio, en el menú Ver , seleccione Explorador de plantillas, expanda Plantillas de SQL Server y, a continuación, expanda la carpeta Captura de datos modificados .
Funciones de consulta
Según sean las características de la tabla de origen que se someta a seguimiento y la manera en que se configure su instancia de captura, se generarán una o las dos funciones TVF para la consulta de datos modificados.
La función cdc.fn_cdc_get_all_changes_<capture_instance> devuelve todos los cambios producidos durante el intervalo especificado. Siempre se genera esta función. Las entradas siempre se devuelven ordenadas, primero por el SLN de confirmación de la transacción del cambio y, a continuación, por un valor que secuencia el cambio dentro de su transacción. Según la opción de filtro de filas elegida, al actualizar se devuelve la fila final (opción de filtro de filas "all") o se devuelven tanto los valores nuevos como los antiguos (opción de filtro de filas "all update old").
La función cdc.fn_cdc_get_net_changes_<capture_instance> se genera cuando el parámetro
@supports_net_changesse establece en1al habilitarse la tabla de origen.Nota:
Solo se admite esta opción si la tabla de origen tiene definida una clave principal o si el parámetro @index_name se ha usado para identificar un índice único.
La
netchangesfunción devuelve un cambio por fila de tabla de origen modificada. Si se registra más de un cambio para la fila durante el intervalo especificado, los valores de la columna reflejarán el contenido final de la fila. Para identificar correctamente la operación necesaria para actualizar el entorno de destino, la función TVF debe considerar tanto la operación inicial realizada en la fila en el intervalo como la operación final realizada en la fila. Cuando se especifica la opción de filtro de filas “all”, las operaciones que devuelva una consulta net changes podrán ser inserciones, eliminaciones o actualizaciones (valores nuevos). Esta opción siempre devuelve el valor NULL para la máscara de actualización porque hay un costo asociado al cálculo de una máscara agregada. Si necesita una máscara agregada que refleje todos los cambios realizados en una fila, use la opción «todo con máscara». Si el procesamiento posterior no requiere distinguir entre inserciones y actualizaciones, utilice la opción «todo con combinación». En este caso, el valor de la operación aceptará solo dos valores: 1 para la eliminación y 5 para una operación que podría ser una inserción o una actualización. Esta opción elimina el procesamiento adicional necesario para determinar si la operación derivada debía ser una inserción o una actualización, y puede mejorar el rendimiento de la consulta cuando no sea necesaria esta diferenciación.
La máscara de la actualización devuelta por una función de consulta es una representación compacta que identifica todas las columnas que han cambiado en una fila de datos modificados. Normalmente, esta información solo es necesaria para un pequeño subconjunto de las columnas capturadas. Hay funciones que se pueden usar para ayudar a extraer información de la máscara de una forma que sea más fácilmente utilizable por las aplicaciones. La función sys.fn_cdc_get_column_ordinal devuelve la posición ordinal de una columna con nombre de una determinada instancia de captura, mientras que la función sys.fn_cdc_is_bit_set devuelve la paridad del bit de la máscara proporcionada en función del ordinal que se pasó en la llamada a la función. La combinación de estas dos funciones permite extraer eficazmente información de la máscara de actualización y devolverla con la solicitud de datos modificados. En SQL Server Management Studio, vea la plantilla Enumerate Net Changes Using All With Mask para ver una demostración de cómo se usan estas funciones.
Escenarios de funciones de consulta
En las secciones siguientes se describen escenarios comunes para consultar datos de captura de datos modificados mediante las funciones cdc.fn_cdc_get_all_changes_<capture_instance> de consulta y cdc.fn_cdc_get_net_changes_<capture_instance>.
Consulta de todos los cambios dentro del intervalo de validez de la instancia de captura
La solicitud más directa de datos de cambios es la que devuelve todos los datos de cambios actuales dentro del intervalo de validez de una instancia de captura. Para realizar esta solicitud, primero debe determinarse el límite inferior y el límite superior de LSN del intervalo de validez. A continuación, use estos valores para identificar los parámetros @from_lsn y @to_lsn pasar a la función cdc.fn_cdc_get_all_changes_<capture_instance> de consulta o cdc.fn_cdc_get_net_changes_<capture_instance>. Use la función sys.fn_cdc_get_min_lsn para obtener el límite inferior y la función sys.fn_cdc_get_max_lsn para obtener el límite superior. En SQL Server Management Studio, vaya a la plantilla Enumerar todos los cambios del intervalo válido para ver código de ejemplo para consultar todos los cambios vigentes mediante la función de consulta cdc.fn_cdc_get_all_changes_<capture_instance>. En SQL Server Management Studio, vea la plantilla Enumerar cambios de red para el intervalo válido para obtener un ejemplo similar del uso de la función cdc.fn_cdc_get_net_changes_<capture_instance>.
Consulta de todos los cambios nuevos desde el último conjunto de cambios
En las aplicaciones típicas, la consulta de los datos modificados constituye un proceso continuo en el que se realizan solicitudes periódicas de todos los cambios acaecidos desde la última solicitud. En este tipo de consultas, puede usar la función sys.fn_cdc_increment_lsn para derivar el límite inferior de la consulta actual del límite superior de la consulta anterior. Con este método se asegura de que ninguna fila se repite, porque el intervalo de búsqueda se trata siempre como un intervalo cerrado en el que se incluyen los dos extremos. Luego, use la función sys.fn_cdc_get_max_lsn para obtener el extremo superior del nuevo intervalo de la solicitud. En SQL Server Management Studio, vea la plantilla Enumerar todos los cambios desde la solicitud anterior para el código de ejemplo para mover sistemáticamente la ventana de consulta para obtener todos los cambios desde la última solicitud.
Consulta de todos los cambios nuevos hasta ahora
Una restricción habitual que se aplica a los cambios devueltos por una función de consulta es que solo se incluyen los cambios producidos entre la solicitud anterior y la fecha y hora actual. Para esta consulta, aplique la función sys.fn_cdc_increment_lsn al @from_lsn valor que se usó en la solicitud anterior para determinar el límite inferior. Dado que el límite superior del intervalo de tiempo se expresa como un punto concreto en el tiempo, debe convertirse en un valor LSN antes de poder utilizarse en una función de consulta. Antes de que el valor de fecha y hora se pueda convertir en el valor LSN correspondiente, debe asegurarse de que el proceso de captura ha procesado todos los cambios que se confirmaron a través del límite superior especificado. Esto es necesario para garantizar que todos los cambios que cumplen los requisitos se hayan propagado a la tabla de cambios. Una forma de hacerlo es estructurar un bucle de espera que compruebe periódicamente si el LSN de confirmación máximo actual registrado para cualquiera de las tablas de cambios de la base de datos supera el momento de finalización deseado del intervalo de solicitud.
Una vez que el bucle de espera comprueba que el proceso de captura ha procesado todas las entradas de registro correspondientes, use la función sys.fn_cdc_map_time_to_lsn para determinar el nuevo extremo superior expresado con un valor LSN. Para asegurarse de que se recuperen todas las entradas confirmadas hasta el momento especificado, llame a la función sys.fn_cdc_map_time_to_lsn y use la opción "el mayor valor menor o igual que".
Nota:
En períodos de inactividad, se agrega una entrada ficticia a la tabla cdc.lsn_time_mapping para marcar el hecho de que el proceso de captura haya procesado los cambios hasta un tiempo de confirmación determinado. De este modo, impide que parezca que el proceso de captura se ha retrasado cuando lo que ocurre es que simplemente no hay cambios que procesar.
La plantilla Enumerar todos los cambios hasta ahora muestra cómo usar la estrategia anterior para consultar los datos modificados.
Agregar una hora de confirmación a un conjunto de resultados de todos los cambios
La hora de confirmación de cada transacción con una entrada asociada en una tabla de cambios de base de datos está disponible en la tabla cdc.lsn_time_mapping. Al unir el valor __$start_lsn devuelto en una solicitud para todos los cambios con el valor start_lsn de una entrada de la tabla cdc.lsn_time_mapping, puede devolver el valor tran_end_time junto con los datos de cambios para marcar el cambio con la hora de confirmación de la transacción en el origen. La plantilla Append Commit Time to All Changes Result Set muestra cómo realizar esta unión.
Combinar datos modificados con otros datos de la misma transacción
En ocasiones, resulta útil combinar los datos de cambios con otra información recopilada sobre la transacción cuando esta se confirmó en el origen. La tran_begin_lsn columna de la tabla cdc.lsn_time_mapping proporciona la información necesaria para realizar dicha combinación. Cuando se produce la actualización del origen, el valor de database_transaction_begin_lsn procedente de la vista dinámica del sistema sys.dm_tran_database_transactions debe guardarse junto con cualquier otra información que se vaya a unir a los datos modificados. Use la función fn_convertnumericlsntobinary para comparar los database_transaction_begin_lsn valores y tran_begin_lsn . El código para crear esta función está disponible en la plantilla Crear función fn_convertnumericlsntobinary. La plantilla Devuelve todos los cambios con un determinado tran_begin_lsn muestra cómo afectar a la combinación.
Consulta mediante funciones de contenedor DateTime
Un escenario de aplicación común para consultar los datos modificados consiste en solicitar periódicamente los datos modificados a través de una ventana deslizante enlazada a valores de fecha y hora. Para este tipo de consumidores, la captura de datos modificados proporciona el procedimiento almacenado sys.sp_cdc_generate_wrapper_function, que genera scripts para crear funciones de envoltura personalizadas para las funciones de consulta de la captura de datos modificados. Estos contenedores personalizados permiten que el intervalo de la consulta se exprese como un par de fecha y hora.
Las opciones de llamada del procedimiento almacenado permiten generar encapsuladores para todas las instancias de captura a las que el llamador tiene acceso, o solo para una instancia de captura especificada. Entre las opciones admitidas también se incluye la capacidad de especificar si el extremo superior del intervalo de captura debe estar abierto o cerrado, cuáles de las columnas capturadas disponibles deben incluirse en el conjunto de resultados y cuáles de las columnas incluidas deben asociarse a marcas de actualización. El procedimiento devuelve un conjunto de resultados con dos columnas: el nombre de la función generada, que puede derivarse del nombre de la instancia de captura, y la instrucción CREATE del procedimiento almacenado contenedor. La función para encapsular la consulta de todos los cambios siempre se genera. Si el parámetro @supports_net_changes se estableció cuando se creó la instancia de captura, también se genera la función para encapsular la función de cambios netos.
Es responsabilidad del diseñador de la aplicación llamar al procedimiento almacenado para generar scripts a fin de generar las sentencias CREATE para los procedimientos almacenados de envoltura, y ejecutar los scripts CREATE resultantes para crear las funciones. Esto no se produce automáticamente cuando se crea una instancia de captura.
Los envoltorios de fecha y hora pertenecen al usuario y no se crean en el esquema predeterminado del llamador. La función generada resulta conveniente sin modificaciones para la mayoría de los usuarios. No obstante, siempre puede aplicarse alguna personalización más extensa al script generado antes de crear la función.
El nombre de la función que encapsula la consulta de todos los cambios es fn_all_changes_, seguido del nombre de la instancia de captura. El prefijo que se utiliza para el elemento contenedor de cambios netos es fn_net_changes_. Ambas funciones aceptan tres argumentos, al igual que sus TVF asociadas de captura de datos modificados. Sin embargo, el intervalo de la consulta de los contenedores está limitado por dos valores de fecha y hora y no por dos valores LSN. El parámetro @row_filter_option es el mismo para los dos conjuntos de funciones.
Las funciones contenedoras generadas admiten la siguiente convención para recorrer de forma sistemática la escala de tiempo de captura de datos modificados: se espera que el parámetro @end_time del intervalo anterior se use como parámetro @start_time del intervalo siguiente. La función de envoltorio se encarga de establecer la correspondencia entre los valores de fecha y hora y los valores LSN, y garantiza que no se pierda ni se repita ningún dato si se sigue esta convención.
Los contenedores se pueden generar para admitir un límite superior cerrado o un límite superior abierto en la ventana de consulta especificada. Es decir, quien realiza la llamada puede especificar si las entradas cuyo instante de confirmación sea igual al límite superior del intervalo de extracción deben incluirse en el intervalo. De forma predeterminada, se incluye el límite superior.
Aunque las TVF de consulta generadas producen un error si se les proporciona un valor NULL para el valor @from_lsn o el valor @to_lsn, las funciones envoltorio de fecha y hora usan NULL para permitir que estos envoltorios devuelvan todos los cambios actuales. Es decir, si se pasa null como extremo inferior de la ventana de consulta al envoltorio datetime, se utiliza el extremo inferior del intervalo de validez de la instancia de captura en la instrucción subyacente SELECT que se aplica a la TVF de consulta. Del mismo modo, si se pasa un valor NULL como extremo superior de la ventana de consulta, el extremo superior del intervalo de validez de la instancia de captura se utiliza al realizar la selección en la TVF de consulta.
El conjunto de resultados devuelto por una función contenedora incluye todas las columnas solicitadas seguidas por una columna de operación, que se codifica de nuevo como uno o dos caracteres para identificar la operación que está asociada a la fila. Si las marcas de actualización se han solicitado, aparecen como columnas de bits después del código de operación en el orden especificado en el parámetro @update_flag_list. Para obtener información sobre las opciones de llamada para personalizar las funciones contenedoras de fecha y hora generadas, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).
La plantilla Crear una instancia de una TVF contenedora con indicador de actualización muestra cómo personalizar una función contenedora generada para agregar un indicador de actualización para una columna especificada al conjunto de resultados que devuelve una consulta de cambios netos. La plantilla Crear instancias de envoltorios CDC para TVF para un esquema muestra cómo crear instancias de los envoltorios Datetime para las TVF de consulta para todas las instancias de captura creadas para las tablas de origen de un esquema de base de datos determinado.
Para obtener un ejemplo que usa un contenedor datetime para consultar datos modificados, en SQL Server Management Studio, vea la plantilla Obtener cambios netos mediante contenedor con marcas de actualización. Esta plantilla muestra cómo consultar los cambios netos con una función de envoltura cuando esta está configurada para devolver indicadores de actualización. La opción de filtro de fila "todos con máscara" es necesaria para que la función de consulta subyacente devuelva una máscara de actualización no nula al actualizar. Se proporcionan valores NULL para los límites inferior y superior del intervalo de fecha y hora, a fin de indicar a la función que utilice el extremo inferior y el extremo superior del intervalo de validez de la instancia de captura al realizar la consulta subyacente basada en LSN. La consulta devuelve una fila por cada modificación de una fila de origen que se produjo en el intervalo válido de la instancia de captura.
Usar las funciones de contenedor DateTime para realizar la transición entre instancias de captura
La captura de datos modificados admite un máximo de dos instancias de captura para una única tabla de origen sometida a seguimiento. El uso principal de esta capacidad consiste en alojar una transición entre varias instancias de captura cuando los cambios del lenguaje de definición de datos (DDL) efectuados en la tabla de origen amplían el conjunto de columnas disponibles para llevar a cabo el seguimiento. Cuando la transición se realiza a una nueva instancia de captura, un modo de proteger los niveles superiores de la aplicación para que no se produzcan cambios en los nombres de las funciones de consulta subyacentes consiste en utilizar una función contenedora que incluya la llamada subyacente. A continuación, asegúrese de que el nombre de la función contenedora sigue siendo el mismo. Cuando vaya a producirse el cambio, la función contenedora antigua se puede quitar, pues se crea una nueva con el mismo nombre que hace referencia a las nuevas funciones de consulta. Al modificar primero el script generado para crear una función contenedora con el mismo nombre, puede hacer el cambio a la nueva instancia de captura sin que esto afecte a los niveles superiores de la aplicación.