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:
wartość | znaczenie | czy do ponownego przetworzenia |
---|---|---|
0 | nowy wpis | TAK |
1 | wpis przetworzony z sukcesem | NIE |
2 | wpis przetworzony z błędem | TAK |
3 | nie wymaga przetworzenia | NIE |
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
Dodaj komentarz