Procedimientos almacenados críticos en replicaciones

Cuando montamos una replicación  transaccional se ven involucrados varios procedimientos almacenados. Estos procedimientos son autogenerados durante la creación de la suscripción a una publicación transaccional. Para cada tabla publicada se generan tres procedimientos para aplicar los comandos en la base de datos del suscriptor. 

Dada una tabla dbo.A,  se generan los procedimientos sp_MSins_dboA, sp_MSupd_dboA y sp_MSdel_dboA responsables de la inserción, actualización y borrado. Cuando incluimos lógica adicional en dichos procedimientos (llamar a otro procedimiento, filtrar operaciones, ignorar comandos, auditar, etc.) es crítico que dichos procedimientos no resulten sobreescritos o borrados para mantener la replicación funcionando correctamente.

Esta situación indeseada puede ocurrir bien si reinicializamos la suscripción sin ser precavidos (sin haber indicado en la replicación que no se sobreescriban los procedimientos si existen) o simplemente si alguien decide “meterles mano” o borrar esos procedimientos.

Gracias a los triggers DDL (Data Definition Language), nuevos en SQL Server 2005, podemos crear  un trigger que vele por la no modificación ni borrado de los procedimientos pertenecientes a las replicaciones pero permitiendo la creación de nuevas suscripciones.

Este trigger deberá ser desactivado previamente cuando debamos realizar modificaciones o borrados de dichos procedimientos bien directamente o indirectamente. Con directamente me refiero desactivando el trigger en sí, indirectamente hace referencia a desactivarlo con algún flag o condición que evite su aplicación. De esta forma tenemos cierta salvaguarda que nos protegerá de cambios o borrados involuntarios.

Como ejemplo mostramos el siguiente trigger que vigilará los procedimientos de las replicaciones  y podrá ser desactivado temporalmente de forma indirecta creando un objeto “habilita_cambios” en la base de datos del suscriptor.

create trigger [vigila_procedimientos]
on database
for DDL_PROCEDURE_EVENTS
as
begin

if not exists (select 1 from sys.sysobjects where name = 'habilita_cambios')
 begin
 declare @procedimiento as nvarchar(255)
 declare @comando as nvarchar(max)

  SELECT @comando=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
  SELECT @procedimiento=EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)')

if (not @comando is null and not @procedimiento is null and substring(@procedimiento,0,9) in ('sp_MSins','sp_MSdel','sp_MSupd') and (@comando like '%ALTER%' or @comando like '%DROP%') )
  begin
  raiserror (N'vigila_procedimientos: No se pueden modificar los procedimientos de la replicación. Cree el objeto habilitar_cambios para habilitar las modificaciones.',10,1)
  end
 end
end