Us\u0142uga Service Brokera, kt\u00f3ra pojawi\u0142a si\u0119 wraz z wersja 2005 zadziwia mnie do tej pory.
\nOd wielu lat producent niewiele w niej zmienia, co mo\u017ce \u015bwiadczy\u0107 o dobrze przemy\u015blanej architekturze.
\nZ drugiej strony monitoring tej us\u0142ugi dostarczony przez producenta pozostawia wiele do \u017cyczenia patrz\u0105c na to nawet z ergonomicznego puntku widzenia. Walka z wy\u0142\u0105czaj\u0105cymi si\u0119 kolejkami i komunikatami typu poison sp\u0119dzi\u0142a nie jeden sen z powiek. Jak sobie z tym najlepiej poradzi\u0107 ?
\nZak\u0142adam, \u017ce czytelnik zna podstawow\u0105 budow\u0119 Service Brokera i ma do\u015bwiadczenie w jego stosowaniu w warunkach produkcyjnych. To o czym nale\u017cy pami\u0119ta\u0107, to \u015bwiadomo\u015b\u0107, \u017ce wbudowano w serwer bazodanowy mechanizm, kt\u00f3ry potrafi obs\u0142u\u017cy\u0107 za pomoc\u0105 j\u0119zyka t-sql komunikacj\u0119 asynchroniczn\u0105 bez pomocy zewn\u0119trznych narz\u0119dzi. To niew\u0105tpliwie wielki plus funkcjonalny.
\nChcia\u0142bym skoncentrowa\u0107 si\u0119 nad propozycj\u0105 budowy szkicu uniwersalnej procedury aktywacyjnej, kt\u00f3ra obs\u0142uguje kolejk\u0119 targetow\u0105.
\nDokumentacja Microsoftu sprowadza si\u0119 do budowy dedykowanej per kolejka procedury aktywacyjnej, gdzie spore fragmenty kodu s\u0105 powtarzane i jest to nieco niezgodne z podej\u015bciem DRY.
\nPrzyj\u0119te za\u0142o\u017cenia:
\n1. Przekazywane s\u0105 jedynie komunikaty w formie XML
\n2. Ka\u017cdy typ komunikatu XML na podstawie jego root elementu b\u0119dzie obs\u0142ugiwany przez dedykowan\u0105 procedur\u0119 sk\u0142adowan\u0105
\n3. Ka\u017cda z takich procedur ma standardow\u0105 posta\u0107<\/p>\n
CREATE PROCEDURE [dbo].[spInterfaceSystemV2Operation]\r\n\t@InXML xml(dbo.scmInterfaceSystemV2Operation),\r\n\t@OutXML xml(dbo.scmInterfaceSystemV2Operation) output,\r\n\t@ErrorType varchar(200) output,\r\n\t@ErrorMsg varchar(4000) output\r\n\t\r\n\tAS\r\n\tBEGIN\r\n\t RETURN(0)\r\n\tEND\r\n \r\n<\/pre>\n4. Wszystkie obiekty bazodanowe s\u0105 obr\u0119bie jednej bazy.
\n5. Wykorzystany zostanie dynamiczny sql.
\n6. Procedura przyjmie minimaln\u0105 liczb\u0119 argument\u00f3w i stanie si\u0119 wewn\u0119trzn\u0105 procedur\u0105 aktywacyjn\u0105 ka\u017cdej z kolejek targetowych.<\/p>\n
Proponowany nag\u0142\u00f3wek:<\/p>\n
CREATE PROCEDURE [dbo].[spGenericActivationProcedure]\r\n@QueueName varchar(255) -- Nazwa kolejki targetowej,\r\n@OnErrorRollbackToQueue bit -- Spos\u00f3b obs\u0142ugi b\u0142\u0119dnych komunikat\u00f3w\r\n\r\nAS\r\n\r\nBEGIN\r\n RETURN(0)\r\nEND\r\n\r\n<\/pre>\nPowi\u0105zanie uniwersalnej procedury aktywacyjnej z przyk\u0142adow\u0105 kolejk\u0105<\/p>\n
Niech nasza kolejka nazywa si\u0119 SampleTargetQueue<\/strong>.<\/p>\nPrzygotowujemy procedur\u0119 nakladkow\u0105 definiowan\u0105 per kolejka<\/p>\n
\r\nCREATE PROCEDURE [dbo].[SpGenericActivation_SampleTargetQueue]\r\nAS\r\nBEGIN\r\n\r\n\tDECLARE @RC int\r\n\tDECLARE @QueueName varchar(255)\r\n\tDECLARE @OnErrorRollbackToQueue bit\r\n\t\r\n\tset @QueueName='SampleTargetQueue'\r\n\tset @OnErrorRollbackToQueue=0\r\n\r\n\tEXECUTE @RC = [dbo].[spGenericActivationProcedure] \r\n @QueueName=@QueueName\r\n ,@OnErrorRollbackToQueue=@OnErrorRollbackToQueue\r\n\r\nEND\r\n<\/pre>\nPowi\u0105zanie kolejki docelowej z tak przygotowan\u0105 procedur\u0105 aktywacyjn\u0105.<\/p>\n
\r\nALTER QUEUE [dbo].[SampleTargetQueue] \r\nWITH STATUS = ON , \r\nRETENTION = OFF , \r\nACTIVATION \r\n( STATUS = ON , PROCEDURE_NAME = [dbo].[SpGenericActivation_SampleTargetQueue] , \r\nMAX_QUEUE_READERS = 3 , EXECUTE AS OWNER ), \r\nPOISON_MESSAGE_HANDLING (STATUS = ON) \r\n\r\n<\/pre>\nSzkic funkcjonalny uniwersalnej procedury aktywacyjnej<\/p>\n
1. Pobranie z kolejki pierwszego komunikatu XML<\/p>\n
\r\nDECLARE @ReceiveSql NVARCHAR(MAX)\r\nDECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER\r\nDECLARE @RecvReqMsg varbinary(max)\r\nDECLARE @RecvReqMsgXml xml\r\nDECLARE @RecvReqMsgName sysname\r\nDECLARE @RecvReqValidation nchar(2)\r\nSELECT @ReceiveSql =\r\n' WAITFOR\r\n\t( RECEIVE TOP(1)\r\n\t\t@RecvReqDlgHandle = conversation_handle, \r\n\t\t@RecvReqMsg = message_body,\r\n\t\t@RecvReqMsgName = message_type_name,\r\n\t\t@RecvReqValidation = validation\r\n\t\tFROM ' + @QueueName + '\r\n\t), TIMEOUT 1000'\r\n\r\n<\/pre>\n2. Na podstawie root elementu XML ustalenie nazwy procedury sk\u0142adowanej odpowiedzialnej za logik\u0119 biznesow\u0105<\/p>\n
Proponowany kod transformacji :<\/p>\n
\r\n\r\n DECLARE @inXML XML\r\n DECLARE @ServiceName nvarchar(300)\r\n DECLARE @ServiceNamespace nvarchar(600) \r\n DECLARE @ProcedureName nvarchar(200)\r\n DECLARE @NamespaceSPPrefix nvarchar(200)\r\n DECLARE @ServiceQName nvarchar(300)\r\n declare @PrefixNameSpace nvarchar(100)\r\n\r\n set @PrefixNameSpace='http:\/\/mycompany.com\/interface\/'\r\n\r\n set @inXML='\r\n \r\n \r\n 11111<\/Id>\r\n\t<\/Account>\r\n \r\n 121212<\/Id>\r\n\t<\/Account>\r\n <\/Accounts>\r\n \r\n secret-user<\/User>\r\n SECRET-SYSTEM<\/System>\r\n 2018-04-18T13:25:08.896<\/Date>\r\n 7B3A9067-6B13-4B6B-9068-83D2BFF671A6<\/Uid>\r\n <\/Metrics>\r\n\r\n<\/ns3:GetAccountNumber>'\r\n\r\n SELECT @ServiceNamespace = cast(@inXML.query('namespace-uri(\/*[1])') as varchar(300)),\r\n @ServiceName = cast(@inXML.query('local-name(\/*[1])') as varchar(600))\r\n SELECT @ServiceQName = '{' + @ServiceNamespace + '}' + @ServiceName\r\n SELECT @NamespaceSPPrefix = SUBSTRING(@ServiceNamespace, LEN(@PrefixNameSpace), 8000) \r\n SELECT @NamespaceSPPrefix = replace(@NamespaceSPPrefix, '\/', '')\r\n SELECT @ProcedureName = 'spInterface' + @NamespaceSPPrefix + @ServiceName \r\n\t\t\r\n\t select @ServiceNamespace as [@ServiceNamespace],@ServiceName as [@ServiceName] ,\r\n\t\t@NamespaceSPPrefix as [@NamespaceSPPrefix] ,@ProcedureName as [@ProcedureName]\r\n \r\n<\/pre>\nW tym przypadku wyznaczamy warto\u015b\u0107 nazwy procedury sk\u0142adowanej jako
\nspInterfaceAccountV5getNumberGetAccountNumber<\/strong><\/p>\n3. Obs\u0142u\u017cenie sytuacji wyj\u0105tkowych, komunikat\u00f3w typu poison, XML-i niezgodnych ze schema parametru wej\u015bciowego procedury sk\u0142adowanej, wy\u0142\u0105czonej kolejki, naruszenia wi\u0119z\u00f3w integralno\u015b\u0107.
\n4. Wszystkie powy\u017csze punkty powinny by\u0107 realizowane w ramach jednej transakcji<\/p>\n
5. Zalogowane komunikaty b\u0142\u0119d\u00f3w trafiaj\u0105 do jednej wsp\u00f3lnej tabeli.
\nPropozycja takiej wsp\u00f3lnej tabeli<\/p>\n
\r\n\r\nCREATE TABLE [dbo].[GenericQueueFaultTable](\r\n\t[id] [bigint] IDENTITY(1,1) NOT NULL,\r\n\t[qName] [varchar](900) NULL,\r\n\t[eventTime] [datetime] NULL DEFAULT (getdate()),\r\n\t[inXML] [xml] NULL,\r\n\t[queue] [varchar](255) NULL,\r\n\t[exceptionMsg] [nvarchar](4000) NULL,\r\n\t[conversationId] [uniqueidentifier] NULL,\r\n\t[conversationHandle] [uniqueidentifier] NOT NULL,\r\n\t[callbackMessageSent] [bit] NULL,\r\n\t[status] [int] NOT NULL DEFAULT ((0)),\r\n\t[ChangeTime] [datetime] NOT NULL DEFAULT (getdate()),\r\n\t[ChangeOperator] [varchar](100) NOT NULL DEFAULT (suser_sname()),\r\n CONSTRAINT [pk_ServiceBrokerQueueFault] PRIMARY KEY CLUSTERED \r\n(\r\n\t[id] \r\n)\r\n\r\n<\/pre>\nPrzyk\u0142adowy fragment kodu uzupe\u0142niaj\u0105cego tabel\u0119 w przypadku wyst\u0105pienia b\u0142\u0119du:<\/p>\n
\r\n\r\nINSERT INTO [dbo].[GenericQueueFaultTable]\r\n\t\t\t ([qName]\r\n\t\t\t ,[inXML]\r\n\t\t\t ,[queue]\r\n\t\t\t ,[exceptionMsg]\r\n\t\t\t ,[conversationId]\r\n\t\t\t ,[conversationHandle]\r\n\t\t\t )\r\n\t\t\tVALUES\r\n\t\t\t (@QName\r\n\t\t\t ,@InXml\r\n\t\t\t ,@QueueName\r\n\t\t\t ,@ExceptionMsg\r\n\t\t\t ,@ConversationId\r\n\t\t\t ,@RecvReqDlgHandle\r\n\t\t\t )\r\n<\/pre>\nZnaczenie pola status:<\/p>\n
\n\n\nwarto\u015b\u0107<\/th>\n znaczenie<\/th>\n czy do ponownego przetworzenia<\/th>\n<\/tr>\n \n0<\/td>\n nowy wpis<\/td>\n TAK<\/td>\n<\/tr>\n \n1<\/td>\n wpis przetworzony z sukcesem<\/td>\n NIE<\/td>\n<\/tr>\n \n2<\/td>\n wpis przetworzony z b\u0142\u0119dem<\/td>\n TAK<\/td>\n<\/tr>\n \n3<\/td>\n nie wymaga przetworzenia<\/td>\n NIE<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
Proste zapytanie monitoruj\u0105ce b\u0142\u0119dne wpisy w tabeli<\/p>\n
\r\n\r\nselect DB_NAME() as [DB_NAME()], \r\ncount(*) as numer,\r\nqName as qName,\r\nmin(eventTime) as minEventTime, \r\nmax(eventTime) as MaxEventTime,\r\nstatus from [dbo].[GenericQueueFaultTable] with (nolock)\r\nwhere status in (0,2)\r\ngroup by qName,status\r\norder by count(*) desc, qName,status\r\n\r\n<\/pre>\nNale\u017cy dodatkowo wprowadzi\u0107 polityk\u0119 retencji, gdzie po ustalonej liczbie dni, dane powinny by\u0107 usuwane trwale, w szczegolno\u015bci tam, gdzie status wskazuje na brak potrzeby ponownego przetworzenia.<\/p>\n
<\/p>\n
<\/p>\n
Literatura<\/p>\n
https:\/\/sqlperformance.com\/2014\/03\/sql-performance\/configuring-service-broker<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"