sql - tabla - seguimiento base de datos

¿Buscar cambios en una tabla de SQL Server?

Question

¿Cómo puedo monitorear una base de datos de SQL Server para realizar cambios en una tabla sin usar activadores o modificar la estructura de la base de datos de ninguna manera? Mi entorno de programación preferido es .NET y C #.

Me gustaría ser compatible con cualquier SQL Server 2000 SP4 o posterior. Mi aplicación es una visualización de datos atornillada para el producto de otra compañía. Nuestra base de clientes es de miles, por lo que no quiero tener que establecer requisitos para modificar la tabla del proveedor de terceros en cada instalación.

Por "cambios en una tabla" me refiero a los cambios en los datos de la tabla, no a los cambios en la estructura de la tabla.

En última instancia, me gustaría que el cambio active un evento en mi aplicación, en lugar de tener que verificar los cambios en un intervalo.

El mejor curso de acción dado mis requisitos (sin desencadenantes o modificación de esquema, SQL Server 2000 y 2005) parece ser el uso de la función BINARY_CHECKSUM en T-SQL . La forma en que planeo implementar es esta:

Cada X segundos ejecuta la siguiente consulta:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);

Y compare eso con el valor almacenado. Si el valor ha cambiado, recorra la tabla fila por fila usando la consulta:

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);

Y compare las sumas de comprobación devueltas con los valores almacenados.

Answer 1

¿Con qué frecuencia necesita verificar los cambios y qué tan grande (en términos de tamaño de fila) son las tablas en la base de datos? Si usa el método CHECKSUM_AGG(BINARY_CHECKSUM(*)) sugerido por John, escaneará cada fila de la tabla especificada. La sugerencia de NOLOCK ayuda, pero en una base de datos grande, todavía está golpeando cada fila. También necesitará almacenar la suma de verificación para cada fila para que diga que una ha cambiado.

¿Has considerado ir a esto desde un ángulo diferente? Si no desea modificar el esquema para agregar activadores (lo cual tiene sentido, no es su base de datos), ¿ha considerado trabajar con el proveedor de la aplicación que hace la base de datos?

Podrían implementar una API que proporciona un mecanismo para notificar a las aplicaciones accesorias que los datos han cambiado. Podría ser tan simple como escribir en una tabla de notificación que enumera qué tabla y qué fila se modificaron. Eso podría implementarse mediante desencadenadores o código de aplicación. Por su parte, no importaría, su única preocupación sería escanear la tabla de notificaciones de forma periódica. El rendimiento alcanzado en la base de datos sería mucho menor que el escaneo de cada fila en busca de cambios.

La parte difícil sería convencer al vendedor de la aplicación para que implemente esta característica. Dado que esto puede manejarse completamente a través de SQL a través de desencadenantes, usted podría hacer la mayor parte del trabajo escribiendo y probando los desencadenantes y luego llevando el código al proveedor de la aplicación. Al hacer que el proveedor admita los desencadenantes, evita que la situación en la que agrega un activador inadvertidamente reemplace un desencadenador suministrado por el proveedor.

Answer 2

¿Por qué no quieres usar disparadores? Son algo bueno si los usa correctamente. Si los usa como una forma de reforzar la integridad referencial, es cuando pasan de buenos a malos. Pero si los usa para monitorear, en realidad no son considerados tabú.

Answer 3

Eche un vistazo al comando CHECKSUM:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

Eso devolverá el mismo número cada vez que se ejecute, siempre y cuando los contenidos de la tabla no hayan cambiado. Ver mi publicación en esto para más información:

CHECKSUM

Así es como lo usé para reconstruir las dependencias de caché cuando las tablas cambiaron:
Dependencia de caché de base de datos ASP.NET 1.1 (sin desencadenadores)

Answer 4

Lamentablemente, no creo que haya una forma clara de hacerlo en SQL2000. Si reduce sus requisitos a SQL Server 2005 (y posterior), entonces está en el negocio. Puede usar la clase SQLDependency en System.Data.SqlClient . Consulte Notificaciones de consultas en SQL Server (ADO.NET) .

Answer 5

Supongo aquí: si no quieres modificar las tablas de un tercero, ¿puedes crear una vista y luego poner un activador en esa vista?

Answer 6

Tener un trabajo DTS (o un trabajo que se inicia con un servicio de Windows) que se ejecuta en un intervalo determinado. Cada vez que se ejecuta, obtiene información sobre la tabla dada utilizando las tablas INFORMATION_SCHEMA del sistema y registra estos datos en el depósito de datos. Compare los datos devueltos con respecto a la estructura de la tabla con los datos devueltos la vez anterior. Si es diferente, entonces sabes que la estructura ha cambiado.

Ejemplo de consulta para devolver información con respecto a todas las columnas en la tabla ABC (idealmente enumerar solo las columnas de la tabla INFORMATION_SCHEMA que desee, en lugar de usar * select ** como hago aquí):

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''ABC''

Debería supervisar diferentes columnas y vistas de INFORMATION_SCHEMA según cómo defina exactamente "cambios en una tabla".

Answer 7

Verifica la última fecha de compromiso. Cada base de datos tiene un historial de cuándo se realiza cada confirmación. Creo que es un estándar de cumplimiento de ACID.

Answer 8

Lamentablemente, CHECKSUM no funciona siempre correctamente para detectar cambios . Es solo una suma de comprobación primitiva y ningún cálculo de CRC. Por lo tanto, no puede usarlo para detectar todos los cambios, por ejemplo, los cambios simétricos dan como resultado el mismo CHECKSUM.

P.ej. la solución con CHECKSUM_AGG(BINARY_CHECKSUM(*)) ofrece siempre 0 para las 3 tablas con contenido diferente.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 1 as numB
  UNION ALL
  SELECT 1 as numA, 1 as numB
)  q
-- delivers 0!

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 2 as numB
  UNION ALL
  SELECT 1 as numA, 2 as numB
)  q
-- delivers 0!

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( SELECT 0 as numA, 0 as numB UNION ALL SELECT 0 as numA, 0 as numB ) q -- delivers 0!