Uniwersalna procedura aktywacyjna Service Brokera

Usługa Service Brokera, która pojawiła się wraz z wersja 2005 zadziwia mnie do tej pory.
Od wielu lat producent niewiele w niej zmienia, co może świadczyć o dobrze przemyślanej architekturze.
Z drugiej strony monitoring tej usługi dostarczony przez producenta pozostawia wiele do życzenia patrząc na to nawet z ergonomicznego puntku widzenia. Walka z wyłączającymi się kolejkami i komunikatami typu poison spędziła nie jeden sen z powiek. Jak sobie z tym najlepiej poradzić ?
Zakładam, że czytelnik zna podstawową budowę Service Brokera i ma doświadczenie w jego stosowaniu w warunkach produkcyjnych. To o czym należy pamiętać, to świadomość, że wbudowano w serwer bazodanowy mechanizm, który potrafi obsłużyć za pomocą języka t-sql komunikację asynchroniczną bez pomocy zewnętrznych narzędzi. To niewątpliwie wielki plus funkcjonalny.
Chciałbym skoncentrować się nad propozycją budowy szkicu uniwersalnej procedury aktywacyjnej, która obsługuje kolejkę targetową.
Dokumentacja Microsoftu sprowadza się do budowy dedykowanej per kolejka procedury aktywacyjnej, gdzie spore fragmenty kodu są powtarzane i jest to nieco niezgodne z podejściem DRY.
Przyjęte założenia:
1. Przekazywane są jedynie komunikaty w formie XML
2. Każdy typ komunikatu XML na podstawie jego root elementu będzie obsługiwany przez dedykowaną procedurę składowaną
3. Każda z takich procedur ma standardową postać

CREATE PROCEDURE [dbo].[spInterfaceSystemV2Operation]
	@InXML xml(dbo.scmInterfaceSystemV2Operation),
	@OutXML xml(dbo.scmInterfaceSystemV2Operation) output,
	@ErrorType varchar(200) output,
	@ErrorMsg varchar(4000) output
	
	AS
	BEGIN
	  RETURN(0)
	END
    

4. Wszystkie obiekty bazodanowe są obrębie jednej bazy.
5. Wykorzystany zostanie dynamiczny sql.
6. Procedura przyjmie minimalną liczbę argumentów i stanie się wewnętrzną procedurą aktywacyjną każdej z kolejek targetowych.

Proponowany nagłówek:

CREATE PROCEDURE [dbo].[spGenericActivationProcedure]
@QueueName varchar(255) -- Nazwa kolejki targetowej,
@OnErrorRollbackToQueue bit -- Sposób obsługi błędnych komunikatów

AS

BEGIN
  RETURN(0)
END

Powiązanie uniwersalnej procedury aktywacyjnej z przykładową kolejką

Niech nasza kolejka nazywa się SampleTargetQueue.

Przygotowujemy procedurę nakladkową definiowaną per kolejka

CREATE PROCEDURE [dbo].[SpGenericActivation_SampleTargetQueue]
AS
BEGIN

	DECLARE @RC int
	DECLARE @QueueName varchar(255)
	DECLARE @OnErrorRollbackToQueue bit
	
	set @QueueName='SampleTargetQueue'
	set @OnErrorRollbackToQueue=0

	EXECUTE @RC = [dbo].[spGenericActivationProcedure] 
    @QueueName=@QueueName
   ,@OnErrorRollbackToQueue=@OnErrorRollbackToQueue

END

Powiązanie kolejki docelowej z tak przygotowaną procedurą aktywacyjną.

ALTER QUEUE [dbo].[SampleTargetQueue] 
WITH STATUS = ON , 
RETENTION = OFF , 
ACTIVATION 
(  STATUS = ON , PROCEDURE_NAME = [dbo].[SpGenericActivation_SampleTargetQueue] , 
MAX_QUEUE_READERS = 3 , EXECUTE AS OWNER  ), 
POISON_MESSAGE_HANDLING (STATUS = ON) 

Szkic funkcjonalny uniwersalnej procedury aktywacyjnej

1. Pobranie z kolejki pierwszego komunikatu XML

DECLARE @ReceiveSql NVARCHAR(MAX)
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER
DECLARE @RecvReqMsg varbinary(max)
DECLARE @RecvReqMsgXml xml
DECLARE @RecvReqMsgName sysname
DECLARE @RecvReqValidation nchar(2)
SELECT @ReceiveSql =
' WAITFOR
	( RECEIVE TOP(1)
		@RecvReqDlgHandle = conversation_handle,        
		@RecvReqMsg = message_body,
		@RecvReqMsgName = message_type_name,
		@RecvReqValidation = validation
		FROM ' + @QueueName + '
	), TIMEOUT 1000'

2. Na podstawie root elementu XML ustalenie nazwy procedury składowanej odpowiedzialnej za logikę biznesową

Proponowany kod transformacji :

 DECLARE @inXML XML
 DECLARE @ServiceName nvarchar(300)
 DECLARE @ServiceNamespace nvarchar(600)    
 DECLARE @ProcedureName nvarchar(200)
 DECLARE @NamespaceSPPrefix nvarchar(200)
 DECLARE @ServiceQName nvarchar(300)
 declare @PrefixNameSpace nvarchar(100)

 set  @PrefixNameSpace='http://mycompany.com/interface/'

 set @inXML='
  
      
     11111
	
   
     121212
	
   
  
    secret-user
    SECRET-SYSTEM
    2018-04-18T13:25:08.896
    7B3A9067-6B13-4B6B-9068-83D2BFF671A6
  

'

 SELECT @ServiceNamespace = cast(@inXML.query('namespace-uri(/*[1])') as varchar(300)),
        @ServiceName = cast(@inXML.query('local-name(/*[1])') as varchar(600))
        SELECT @ServiceQName = '{' + @ServiceNamespace + '}' + @ServiceName
        SELECT @NamespaceSPPrefix = SUBSTRING(@ServiceNamespace, LEN(@PrefixNameSpace), 8000)         
        SELECT @NamespaceSPPrefix = replace(@NamespaceSPPrefix, '/', '')
        SELECT @ProcedureName = 'spInterface' + @NamespaceSPPrefix + @ServiceName 
		
	 select @ServiceNamespace as [@ServiceNamespace],@ServiceName as [@ServiceName] ,
		@NamespaceSPPrefix as [@NamespaceSPPrefix] ,@ProcedureName as [@ProcedureName]
        

W tym przypadku wyznaczamy wartość nazwy procedury składowanej jako
spInterfaceAccountV5getNumberGetAccountNumber

3. Obsłużenie sytuacji wyjątkowych, komunikatów typu poison, XML-i niezgodnych ze schema parametru wejściowego procedury składowanej, wyłączonej kolejki, naruszenia więzów integralność.
4. Wszystkie powyższe punkty powinny być realizowane w ramach jednej transakcji

5. Zalogowane komunikaty błędów trafiają do jednej wspólnej tabeli.
Propozycja takiej wspólnej tabeli

CREATE TABLE [dbo].[GenericQueueFaultTable](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[qName] [varchar](900) NULL,
	[eventTime] [datetime] NULL DEFAULT (getdate()),
	[inXML] [xml] NULL,
	[queue] [varchar](255) NULL,
	[exceptionMsg] [nvarchar](4000) NULL,
	[conversationId] [uniqueidentifier] NULL,
	[conversationHandle] [uniqueidentifier] NOT NULL,
	[callbackMessageSent] [bit] NULL,
	[status] [int] NOT NULL DEFAULT ((0)),
	[ChangeTime] [datetime] NOT NULL DEFAULT (getdate()),
	[ChangeOperator] [varchar](100) NOT NULL DEFAULT (suser_sname()),
 CONSTRAINT [pk_ServiceBrokerQueueFault] PRIMARY KEY CLUSTERED 
(
	[id] 
)

Przykładowy fragment kodu uzupełniającego tabelę w przypadku wystąpienia błędu:

INSERT INTO [dbo].[GenericQueueFaultTable]
			   ([qName]
			   ,[inXML]
			   ,[queue]
			   ,[exceptionMsg]
			   ,[conversationId]
			   ,[conversationHandle]
			   )
			VALUES
			   (@QName
			   ,@InXml
			   ,@QueueName
			   ,@ExceptionMsg
			   ,@ConversationId
			   ,@RecvReqDlgHandle
			   )

Znaczenie pola status:

Proste zapytanie monitorujące błędne wpisy w tabeli

select  DB_NAME() as [DB_NAME()], 
count(*) as numer,
qName as qName,
min(eventTime) as minEventTime, 
max(eventTime) as MaxEventTime,
status  from  [dbo].[GenericQueueFaultTable] with (nolock)
where status in (0,2)
group by qName,status
order by count(*) desc, qName,status

Należy dodatkowo wprowadzić politykę retencji, gdzie po ustalonej liczbie dni, dane powinny być usuwane trwale, w szczegolności tam, gdzie status wskazuje na brak potrzeby ponownego przetworzenia.

Literatura

https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker