En más ocasiones de las que desearíamos nos encontramos con modelos de bases de datos “plagados” de triggers. En realidad, mi postura contra los triggers no es, en general, excesivamente radical en su contra aunque no hay duda que no son recomendables como “norma a seguir” para implementar lógica de negocio. Uno de los problemas de los triggers es que “ocultan” mucho detrás de un inocente INSERT/UPDATE/DELETE, operaciones que se suelen presuponer bastante “ligeras” y pueden convertirse en verdaderos monstruos para el rendimiento si no vigilamos con cuidado el impacto de los triggers.
Por otra parte, y es ese el tema principal de este artículo, existen situaciones en las cuales el uso de triggers puede resultar muy adecuado aunque el precio a pagar por la sincronía sea demasiado alto. Con sincronía hago referencia a que los triggers se disparan y forman parte de la actual transacción en la que están involucrados. No olvidemos que finalmente un trigger no es muy diferente a un procedimiento almacenado con la diferencia de poder tener acceso a las tablas inserted/deleted a las que habitualmente no tenemos acceso. Pensando en cómo implementar este escenario pero asíncrono se me ocurre que Service Broker puede hacer muy buen papel. Bastaría con crear un mensaje en el cual añadiéramos la información volátil del trigger (tablas inserted y deleted en XML por ejemplo), y procesarlo en una cola con autoactivación a un procedimiento almacenado con la misma lógica del trigger original pero que utilice dichos XML en vez de las tablas inserted/deleted.
Ahora lo lógico sería mostrar un ejemplo de cómo montar un trigger asíncrono con Service Broker pero… no vamos a hacerlo J ¿Los motivos? El primero es que utilizar Service Broker para dicho fin es digamos… un escenario “de libro”. El segundo es que creemos interesante mostrar el gran potencial que las técnicas de replicación de SQL Server tienen J No olvidemos que la replicación es una tecnología también disponible para los aún muchos usuarios de SQL Server 2000.
Vamos a montar con replicación un escenario típico del uso de triggers, la auditoria de cambios, pero de forma asíncrona. En versiones superiores como SQL Server 2008 disponemos de otras alternativas para realizar el seguimiento de cambios. Sin embargo, el uso de esta estrategia podrá sernos mucho más útil que simplemente registrar los cambios gracias a su potencial de “activación” de lógica mucho más compleja como la de un procedimiento almacenado. Incluso nada nos impediría utilizarlo, por ejemplo, para hacer llamadas asíncronas a un procedimiento almacenado CLR que conecte con un Webservice y nos actualice un valor de nuestra fila de forma totalmente asíncrona. Siempre debemos pensar que todo lo que se fuerce a ejecutarse de forma síncrona y secuencial va a representar un potencial cuello de botella para el tiempo de respuesta de nuestra base de datos y aplicaciones.
Sin más dilación, ¡vamos a por nuestro trigger asíncrono con replicación!
Comenzaremos creando una base de datos de prueba donde crearemos un par de tablas (tabla base y tabla para auditoría):
CREATE DATABASE test_Async
GO
USE test_Async
GO
CREATE TABLE base (id int identity (1,1) PRIMARY KEY, data1 varchar(100), data2 varchar(100))
GO
CREATE TABLE base_audit (idcambio int identity (1,1) PRIMARY KEY, tipo char, id int , data1 varchar(100), data2 varchar(100))
Crearemos a continuación dos procedimientos almacenados. El segundo de ellos actuará como “callback” del primero de ellos que actuaría como un “handle” del “evento” generado por el trigger. El interfaz que los comunicará será una replicación transaccional de procedimientos almacenados:
CREATE PROCEDURE proc_audit_async
(@inserted XML, @deleted XML)
AS
BEGIN
RETURN
END
GO
CREATE PROCEDURE proc_audit_async_callback
(@inserted XML, @deleted XML)
AS
BEGIN
— Creamos las tablas inserted y deleted. En este ejemplo no sería
— necesario materializarlas, pudiendo utilizar directamente la
— consulta en la inserción de auditoría posterior.
SELECT tabla.fila.value(‘@id’, ‘INT’) as id,
tabla.fila.value(‘@data1’, ‘NVARCHAR(100)’) as data1,
tabla.fila.value(‘@data2’, ‘NVARCHAR(100)’) as data2
INTO #INSERTED
FROM @inserted.nodes(‘/INSERTED’) tabla(fila);
SELECT tabla.fila.value(‘@id’, ‘INT’) as id,
tabla.fila.value(‘@data1’, ‘NVARCHAR(100)’) as data1,
tabla.fila.value(‘@data2’, ‘NVARCHAR(100)’) as data2
INTO #DELETED
FROM @deleted.nodes(‘/DELETED’) tabla(fila);
— Auditamos los cambios
IF EXISTS (SELECT * FROM #DELETED)
INSERT INTO base_audit (tipo,id,data1,data2)
SELECT ‘D’,* FROM #DELETED
IF EXISTS (SELECT * FROM #INSERTED)
INSERT INTO base_audit (tipo,id,data1,data2)
SELECT ‘I’,* FROM #INSERTED
END
El procedimiento proc_audit_async lo utilizaremos únicamente como un registrador del evento por lo cual no tendrá cuerpo alguno. El procedimiento proc_audit_aync_callback contendrá la lógica que habitualmente introduciríamos en el trigger. Crearemos a continuación el trigger que únicamente tendrá que generar un par de XML para almacenar las tablas inserted y deleted y ejecutar el procedimiento vacío. Podríamos utilizar otras alternativas a un XML como un conjunto de valores separados por comas, un TVP (Table Value Parameter) en SQL Server 2008, etc.
CREATE TRIGGER base_audit_trigger ON base FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF @@ROWCOUNT <>0
BEGIN
DECLARE @INSERTED XML = (SELECT * FROM INSERTED FOR XML AUTO, TYPE)
DECLARE @DELETED XML = (SELECT * FROM DELETED FOR XML AUTO, TYPE)
EXEC proc_audit_async @INSERTED,@DELETED
END
END
Una vez creados todos los elementos, únicamente necesitamos configurar una replicación transaccional que nos permita hacer llegar las ejecuciones de proc_audit_async a proc_audit_async_callback de forma asíncrona. Para ello, tras configurar un distribuidor para nuestro servidor, crearemos la publicación que será de tipo transaccional siguiendo los pasos:
- Crearemos la publicación
- Seleccionamos replicación transaccional
- Seleccionamos el procedimiento proc_audit_async
- Configuramos el artículo. En este punto es importante que marquemos que se mantenga el artículo sin cambios (para no machacar el cuerpo del procedimiento _callback con el que está vacío) y que cambiemos el nombre del objeto en destino a proc_audit_asynbc_callback. Además indicaremos que lo que queremos replicar son las ejecuciones del procedimiento almacenado.
- Indicaremos que se cree un snapshot inmediatamente, configuraremos el Agente con los credenciales apropiados en el siguiente paso y finalmente crearemos la publicación con un nombre descriptivo (triggers_asincronos por ejemplo):
Una vez en este punto, disponemos de una publicación pero no de una suscripción. El siguiente paso será suscribir nuestra propia base de datos a ella misma. Esto desgraciadamente no es posible hacerlo al 100% utilizando el asistente. La limitación consiste en que no nos permite suscribir una misma base de datos a sí misma. Mi recomendación es que utilizando el asistente configuremos la suscripción sobre otra base de datos pero en el último paso del wizard indiquemos que queremos crear un script en vez de crear directamente la suscripción. En dicho script sustituiremos los nombres de las instancias y bases de datos donde corresponda. Los pasos serian los siguientes:
- Añadir una suscripción
- Seleccionamos nuestra publicación
- Configuramos el tipo de publicación (por ejemplo push para nuestro ejemplo)
- Seleccionaremos una base de datos suscriptora “temporal” para engañar al wizard (en este caso Adventureworks) y configuraremos la seguridad del agente:
- Indicaremos que deseamos que la sincronización ocurra de forma continua
- Inicializamos la suscripción. Esto no tendrá ningún impacto ya que tenemos los objetos previamente creados y hemos indicado que se respeten sin sobreescribir.
- Generamos el script SIN generar la suscripción
Una vez creado el script lo editaremos para sustituir en cada uno de los procedimientos el nombre de la base de datos por la misma que la publicación (test_Async). En mi caso el script resultante es el siguiente:
use [test_Async]
go
exec sp_addsubscription @publication = N’triggers_asincronos’, @subscriber = N’SERVER64HYPERVSQL2008′, @destination_db = N’test_Async’, @subscription_type = N’Push’, @sync_type = N’automatic’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0
go
exec sp_addpushsubscription_agent @publication = N’triggers_asincronos’, @subscriber = N’SERVER64HYPERVSQL2008′, @subscriber_db = N’test_Async’, @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090316, @active_end_date = 99991231, @enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’
Una vez creada la suscripción ya únicamente nos queda probar que todo el “invento” funciona como es debido J Para ello realizaremos algunas operaciones DML sobre la tabla base y comprobaremos que, pasados unos segundos, los datos se encuentran en la tabla de auditoría:
— Insertamos dos registros
INSERT INTO BASE VALUES (‘nuevo’,‘valor’)
INSERT INTO BASE VALUES (‘nuevo2’,‘valor2’)
GO
— Los modificamos
UPDATE BASE
SET data1=data1 + ‘ pero actualizado’
GO
— Los borramos
DELETE FROM BASE
GO
— Esperamos unos segundos …
WAITFOR DELAY ’00:00:10′
— Comprobamos la auditoria
GO
SELECT * from base_audit
En conclusión, la replicación transaccional nos permite soluciones más allá de la mera replicación de datos. En este artículo hemos visto como poder crear triggers asíncronos mediante el uso de la réplica como mecanismo de “disparo asíncrono”. Os animo a aquellos que no conozcáis a nuestra querida replicación en SQL Server a irla conociendo poco a poco e ir buscando aquellos puntos en vuestros entornos donde podáis sacar buen partido de ella J
