Obtenga una alerta cuando un cierto registro cambia en SQL Server

Auditar una base de datos es el primer paso hacia mantenerse actualizado acerca de cambios en la base de datos. De todas maneras, si datos específicos y altamente sensitivos necesitan revisión, una notificación inmediata de cualquier cambio es preferible.

Mandar correos electrónicos de alerta a uno o más recipientes es una de las maneras más comunes de una notificación inmediata. Mandar correos electrónicos para cada cambio en una base de datos no es recomendado. En la mayor parte de los casos llenará su bandeja de entrada, y añadirá muchas acciones innecesarias, lo cual puede también afectar el desempeño global de la aplicación. Por lo tanto, es importante elegir los cambios de los que desea estar alerta con cuidado.

Cómo configurar correos electrónicos de alerta

SI usted está usando SQL Server Auditing, los registros almacenados en un archivo, los registros de la aplicación o los registros de seguridad pueden ser usados.

Cree una tarea para correr cada minuto. La tarea debería:

• Correr el procedimiento almacenado que lee los registros y encuentra los cambios acerca de los que usted desea ser notificado. Si usted almacenando registros de auditoría en un archivo, use la función fn_get_audit_file para leerlo
• Enviar un correo electrónico si registros acerca de transacciones específicas son encontrados

Un inconveniente de este método es que la auditoría a nivel de base de datos no puede ser aplicada a columnas, así que los cambios no pueden ser rastreados en una columna específica, sino en toda la tabla. No puede se puede concluir lo que realmente pasó de la siguiente información:

UPDATE  TOP  ( 200 )  humanresources . employeepayhistory 
SET     modifieddate  =  @ModifiedDate 
WHERE   (  businessentityid  =  @Param1  ) 
       AND  (  ratechangedate  =  @Param2  ) 
       AND  (  rate  =  @Param3  ) 
       AND  (  payfrequency  =  @Param4  ) 
       AND  (  modifieddate  =  @Param5  )

Otra solución es usar las características de SQL Server Change Data Capture o Change Tracking.

Para obtener los registros cambiados para una tabla e información acerca de estos cambios, use la función CHANGETABLE(CHANGES…). Dicha función consulta los registros almacenados en las tablas de rastreo de cambios interna.

Luego, el mismo procedimiento anterior podría ser usado – un procedimiento que lee los registros, y una tarea que correrá cada minuto.

Un inconveniente de estas características es que no registran el tiempo del cambio ni tampoco valores antiguos o nuevos del registro cambiado. Dado que la operación es síncrona, prolonga el tiempo de la transacción. Otro inconveniente es que usted tiene que consultar las tablas periódicamente y entonces mandar el correo electrónico de notificación.

La siguiente solución es crear desencadenadores en una tabla que necesita auditoría. Los desencadenadores serán activados cuando un registro es insertado, eliminado o actualizado y msdb.dbo.sp_send_dbmail debería ser usado para mandar un correo electrónico de alerta a un recipiente de correo electrónico específico, inmediatamente:

CREATE TRIGGER t_Pers 
ON Person.Person 
AFTER INSERT, UPDATE, DELETE 
AS 
   EXEC msdb.dbo.sp_send_dbmail 
                        @profile_name = 'ApexSQLProfile', 
                        @recipients = 'marko.radakovic@apexsql.com' , 
                        @body = 'Data in AdventureWorks2012 is changed', 
                        @subject = 'Your records have been changed' 
GO

Como los desencadenadores son activados por lote, no por fila, se debería asegurar que todas las transacciones de lote son capturadas, y no se pierden datos.

Un inconveniente adicional es que mucho código necesita ser escrito manualmente, mientras que correr una tarea cada minuto no es una solución práctica. Una vez que el correo electrónico es enviado, no hay registro del tiempo cuando esos cambios han ocurrido. Administrar los desencadenadores uno por uno en una base de datos con un gran número de objetos puede consumir tiempo.

La solución de auditoría y alertas recomendada es tener una herramienta de auditoría de SQL Server especializada que haga todo por usted – crear desencadenadores para las columnas y acciones que necesitan auditoría, almacenar los registros auditados para una referencia posterior, y automáticamente mandar correos electrónicos. También provee una fácil administración de múltiples desencadenadores simultáneamente.

ApexSQL Trigger es una herramienta de auditoría de bases de datos para SQL Server, la cual captura los cambios en los datos que han ocurrido en una base de datos, incluyendo información acerca de quién hizo el cambio, qué objetos fueron afectados por él, cuándo fue hecho, así como información acerca del login SQL, la aplicación y el anfitrión usados para hacer el cambio. Guarda toda la información capturada en un repositorio central y lo exporta en formatos amigables para la impresión.

Para auditar cambios en los datos y enviar correos electrónicos de alerta usando ApexSQL Trigger:

1. Configure el correo electrónico de la base de datos
   1. Abra SQL Server Management Studio y conéctese a la instancia SQL Server
   2. En Object Explorer, expanda Management
   3. Haga clic derecho en Database Mail y seleccione Configure Database Mail
   4. Seleccione Set up Database Mail
   5. Establezca Profile name y Description
   6. Haga clic en Add para crear una cuenta SMPT
      

      

   7. Establezca los parámetros para la cuenta de correo electrónico
      

      

      

2. Copy the existing architecture from the following location:

   C:\Users\<user>\AppData\Local\ApexSQL\ApexSQLTrigger2015 and paste it on the same location or Desktop for example. Change its name to ApexSQL2053_Mail.audx

   • Open the newly created architecture and search for Dim TableFullName

   • Add the following:

   ‘Variables for Mail Alerts
   Dim Mail_Profile, Mail_Recipients, Mail_Body, Mail_Subject

   as shown below:

   

   • Below the StartTriggerNameDelete = “tr_d_AUDIT_” add the following:

     ‘Variables for Mail Alerts
     Mail_Profile = “John”
     Mail_Recipients = “John@mymail.com”
     Mail_Body = “Please verify database change.”
     Mail_Subject = “Unauthorized Database Change”
     

     Quick tip: You can customize the Mail Alerts variables values from above with your desired values in this step, or you can change them later using the ApexSQL Trigger Template editor. In this example we will leave Mail Alerts like this here, and change them later.

     

   • Search for INSERT INTO #tmp (Id) VALUES (CAST(@IDENTITY_SAVE AS DECIMAL)) (it comes three times as a search result) and add the following code (below each of the three search results):

     -Send email alert
        EXEC msdb.dbo.sp_send_dbmail
           @profile_name = ‘{% Audit.Print Mail_Profile %}’,
           @recipients = ‘{% Audit.Print Mail_Recipients %}’,
           @body = ‘{% Audit.Print Mail_Body %}’,
           @subject = ‘{% Audit.Print Mail_Subject %}’
     

     as shown below:

     

     In the Search dialog, click the Find next button, to navigate to another part when the modification needs to be applied. Repeat the previous step:

     

     In the Search dialog, click the Find next button, to navigate to another part when the modification needs to be applied. Repeat the previous step:

     

     Quick tip: The change needs to be applied on all places inside the architecture in order to customize it properly.

   • Save changes to the ApexSQL2053_Mail.audx file
3. Inicie ApexSQL Trigger
4. Conéctese a la base de datos que necesita auditoría:
   

   

5. Desde la pestaña Advanced, haga clic en el botón Open

6. Navegue al archivo grabado ApexSQL2049_Mail.audx. Su proyecto ApexSQL Trigger usará esta arquitectura en lugar de la por defecto.

   Para modificar la nueva arquitectura y establecer la cuenta de correo electrónico:

7. En la pestaña Home, en el grupo Actions, haga clic en Template editor
8. Encuentre el siguiente código:
   ‘Customize values below
   Mail_Profile = «John»
   Mail_Recipients = «John@mymail.com»
   Mail_Body = «Please verify database change.»
   Mail_Subject = «Unauthorized Database Change»
9. Modifíquelo usando las configuraciones para su cuenta de correo electrónico de la base de datos, establecidas en el paso #1
   

   

10. En el menú Template editor, haga clic en el botón Save.
11. En la Cuadrícula Principal, seleccione la tabla que contiene datos sensitivos
    

    

12. En el panel Fields for, seleccione las columnas para auditar
13. En la pestaña Home, en el grupo Triggers, haga clic en el botón Create
14. EL script del desencadenador es abierto en el editor interno. Verifique su código para msdb.dbo.sp_send_dbmail
    

    

15. Haga clic en el menú Execute
16. Después de que los desencadenadores son exitosamente creados, usted obtendrá el siguiente mensaje
    

    

Ahora todo lo que usted tiene que hacer es esperar por esos correos electrónicos

Obtener correos electrónicos de notificación cuando hay un insert, delete o update en una columna de la tabla especifica no tiene que ser complicado, y usted lo puede lograr con casi nada de escritura de código. ¡Deje que ApexSQL Trigger haga el trabajo por usted!

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

Traductor: Daniel Calbimonte