O Service Broker é a melhor escolha para auditar alterações de dados no SQL Server Express?

Suponho que a postagem a seguir seja a base do que você está usando atualmente: Auditoria assíncrona centralizada com o Service Broker .

Embora eu realmente goste do Service Broker, não acho que seja o mais adequado para lidar com essa situação específica. As principais preocupações que tenho com o Service Broker, pelo menos neste cenário específico, são:

• Provavelmente é muito complicado se usado principalmente para separar o evento DML do evento de auditoria. Se não mover os dados para outro sistema, isso não parece oferecer muitos benefícios, pois você ainda precisará persistir as tabelas INSERTEDe no gatilho DML.DELETED
• É baseado em eventos, e os eventos variam muito em tamanho e frequência. Você pode ter um milhão de operações de 1 registro ou uma ou duas operações de 1 milhão de registros. E como cada evento é cada operação DML individual, você não tem nenhuma oportunidade de remover entradas duplicadas e/ou negativas em várias operações DML.

Minha preferência é despejar as alterações em uma tabela de filas e, em seguida, em um processo separado programado para ser executado a cada X minutos, ler o número Y de linhas e processá-las.

1. Crie uma tabela de filas para armazenar os dados de auditoria de uma tabela específica (não para cada operação DML individual). A tabela deve ter:

   • a LOCK_ESCALATIONopção definida como DISABLE(via ALTER TABLE {name} SET (LOCK_ESCALATION = DISABLE)) para evitar conflito entre o registro de novos dados pelo gatilho e a remoção de dados do processamento de auditoria. Esta opção foi introduzida no SQL Server 2008, portanto não pode ser usada em instâncias de 2005, mas não há razão para não usá-la nas instâncias de 2008 e mais recentes, pois não altera a funcionalidade em nenhum dos casos.
   • uma AuditIDPK que é uma das seguintes:
     • a INT IDENTITYpartir de -2147483648
     • umaBIGINT IDENTITY
     • an INTcom seu valor vindo de a SEQUENCEque é definido comoCYCLE
   • apenas os campos para os quais você está rastreando alterações, se não todos eles
   • campos para "antigo" e "novo" se precisar acompanhar os valores antes e depois de cada processamento. Dependendo de como você está auditando as alterações, se você tiver um arquivo de valores anteriores, precisará apenas dos "novos" valores da INSERTEDtabela, pois os valores na DELETEDtabela já devem estar em seus dados de auditoria anteriores.

2. Crie um gatilho que simplesmente insere na tabela de filas. Se você precisar passar valores "antigos" e "novos", JUNTE as tabelas INSERTEDe DELETEDaqui em vez de tentar manter duas tabelas de filas separadas, uma para cada uma dessas pseudotabelas. Juntá-los neste ponto e inserir os valores "antigo" e "novo" em uma única linha é um pequeno impacto no desempenho, mas garantirá que cada operação permaneça unida e em ordem cronológica (via PK de incremento).

   Se você não estiver rastreando as alterações em todos os campos, use UPDATE() ou COLUMNS_UPDATED() para determinar se as colunas que estão sendo auditadas foram realmente atualizadas (para UPDATEoperações; essas funções retornam true para todas as colunas em INSERToperações). Lembre-se de que as funções UPDATE()e não determinam se uma alteração foi feita no valor da(s) coluna(s)!! Eles informam apenas se as colunas estavam presentes na cláusula da declaração. A única maneira de determinar se o(s) valor(es) realmente mudou(s) é JOIN the eCOLUMNS_UPDATED()SETUPDATEINSERTEDDELETEDtabelas. Mas, se não estiver rastreando todas as colunas, essas funções são ótimas para sair do Gatilho sem fazer nenhum trabalho se nenhuma coluna rastreada tiver sido alterada. Ou seja, no início do Trigger, você faria:

   IF (NOT UPDATE(TrackedColumn1) AND NOT UPDATE(TrackedColumn2))
   BEGIN
     RETURN; -- no changes so just exit
   END;
   

   Se você não estiver capturando valores "antigos" e "novos" na tabela de filas, essa é a única oportunidade de eliminar registros "atualizados" em que nenhuma coluna foi realmente alterada. Ao inserir na tabela de filas, basta filtrar WHERE IntColOld <> IntColNew OR StringFieldOld <> StringFieldNew COLLATE Latin1_General_BIN2 OR ISNULL(DateFieldOld, '1900-01-01') <> ISNULL(DateFieldNew, '1900-01-01') OR .... É importante usar um _BIN2agrupamento em campos de string para garantir que os dois campos sejam de fato idênticos. E você precisa usar INSULLapenas os campos anuláveis ​​para que eles possam ser iguais se ambos forem NULL.

3. Crie um procedimento armazenado que será executado por X segundos e, nesse período, processará conjuntos de TOP(@BatchSize)linhas, usando ORDER BY AuditID ASC. Você faz isso por meio de um WHILEloop que verifica qual foi definido GETDATE()no @StartTimeinício do procedimento armazenado. Dependendo do processamento que você precisa fazer, às vezes é mais fácil criar uma tabela temporária local para INSERT INTO #TempTable SELECT...o conjunto de trabalho (então você terá DELETEessas linhas no final de cada loop) ou DELETE FROMusar OUTPUT INTO #TempTable.

   Aqui você pode remover modificações duplicadas. Se você estiver rastreando valores "antigos" e "novos" para cada linha, também deverá eliminar as linhas em que nenhuma das colunas realmente foi alterada. Você pode fazer isso testando WHERE IntColOld = IntColNew AND StringFieldOld = StringFieldNew COLLATE Latin1_General_BIN2 AND .... É importante usar um _BIN2agrupamento em campos de string para garantir que os dois campos sejam de fato idênticos. Os agrupamentos não binários, mesmo se diferenciam maiúsculas de minúsculas e acentos, etc., ainda permitem normalizações linguísticas que devem comparar o mesmo para comparações regulares, mas não durante a auditoria. Não use _BINagrupamentos, pois eles foram obsoletos desde o SQL Server 2005, que foi quando os _BIN2agrupamentos foram lançados.

   Tudo feito dentro do loop precisa estar dentro de um // BEGIN TRANexplícito . Use um bloco TRY / CATCH para gerenciar isso. Isso evitará a perda de alguns registros ou o processamento duplo de alguns.COMMITROLLBACK

4. Agende o procedimento armazenado para ser executado a cada X minutos. Normalmente, isso é feito por meio de um trabalho do SQL Server Agent , mas o SQL Server Agent não está disponível para as edições Express. Nesse caso, você pode usar o Agendador de Tarefas do Windows ou obter algo como Quartz.NET .

A configuração de um processo dessa maneira permite que você tenha um processo mais consistente e ajustável que deve processar constantemente X registros a cada Y minutos. Portanto, não importa se você tem 1 milhão de operações DML de linha única ou uma única operação DML de 1 milhão de linhas; esse processo continuará avançando, fazendo o que faz.