Асинхронные темы в SQL с Service Broker.

Когда нет времени ждать выполнения задачи в дело вступает асинхронное программирование.

Представим что нужно выполнить 5 задач , если мы запустим их в SQL они будут выполняться последовательно, после запуска задачи процесс будет ждать завершения

и только после этого начнется следующая задача.

С помощью компонента Service Broker можно выносить выполнение задач во внешние приложения используя обмен сообщениями и управлением очередями сообщений.

Приложение может быть на другой машине таким образом настраивается масштабируемость  без изменения работы логики основного приложения.

Если внешняя служба не доступна Service Broker будет помещать сообщения в очередь на отправку и возобновит работу когда  служба будет  доступна, без ущерба работы основного приложения, таким образом Service Broker гарантирует доставку сообщений.

Когда лучше использовать Service Broker

• Асинхронные триггеры
• Надежная обработка запросов
• Надежный сбор данных
• Распределенная серверная обработка для клиентских приложений
• Консолидация данных для клиентских приложений
• Широкомасштабная пакетная обработка

1.Включение Service Broker

1.Проверить состояние компонента Service Broker

SELECT name, is_broker_enabled

FROM sys.databases

2.Включить компонент Service Broker

ALTER DATABASE SERVICE_BROKER SET ENABLE_BROKER with rollback immediate

--отключить компонент

alter database SERVICE_BROKER set disable_broker with rollback immediate

--задать  новый идентификатор Service Broker, удаляет все диалоги, необходимо заново пересоздать маршруты с новым идентификатором

alter database SERVICE_BROKER set new_broker

3. Включить безопасное взаимодействие

--Главный ключ базы данных — это симметричный ключ, который применяется для защиты закрытых ключей сертификатов

--и асимметричный ключей, которые есть в базе данных.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='dswfdsdgfdg3241df'

--посмотреть список ключей

select * from sys.symmetric_keys

2.Создаем необходимые системные объекты, сообщение, контракт, очередь и сервис

• Типы сообщений определяют данные, которые передаются в рамках диалога.
• Контракты определяют задачи. В каждом контракте задаются типы сообщений, который могут использовать в заданном диалоге, а также сторона диалога, которая может отправлять такие сообщения.
• Очередь хранит входящие сообщения для службы.
• Служба представляет связанный набор бизнес-задач. Имя службы также может использоваться, чтобы определить очередь для службы.

Обратите внимание, что контракт зависит от одного или нескольких типов сообщений. Служба зависит от очереди и может зависеть от одного или нескольких контрактов. Поэтому контракты создаются после типов сообщений и удаляются до типов сообщений. Службы создаются после очередей и контрактов, а удаляются до очередей и контрактов.
Если приложение занимается отправкой сообщений между экземплярами SQL Server, рекомендуется создать один сценарий, в котором определяются типы сообщений и контракты для службы, и второй сценарий, где определяются очередь и служба. Первый сценарий определяет интерфейс для службы — объекты, которые являются общими для вызывающей службы и целевой службы. Второй сценарий определяет имя службы и очередь — объекты, относящиеся к одной стороне диалога.

Передавать можно

– структурированный XML

– просто XML

– Binary data

CREATE MESSAGE TYPE BLOB

VALIDATION = NONE

GO

CREATE CONTRACT BLOB_Contract

(BLOB SENT BY ANY)

GO

CREATE QUEUE BLOB_Queue_Init

CREATE QUEUE BLOB_Queue_Target

GO

CREATE SERVICE BLOB_Service_Init

ON QUEUE BLOB_Queue_Init

(BLOB_Contract)

CREATE SERVICE BLOB_Service_Target

ON QUEUE BLOB_Queue_Target

(BLOB_Contract)

GO

Общая схема взаимодействия выглядит следующим образом

Более полная картинка с использованием маршрутов

3.Отправляем сообщения

1.Объявить переменную дескриптор двухстороннего общения

2.Начать диалоговое общение инструкцией ;SEND

3.Отправить сообщение

DECLARE @h UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @h

FROM SERVICE BLOB_Service_Init

TO SERVICE 'BLOB_Service_Target'

ON CONTRACT BLOB_Contract

WITH ENCRYPTION=OFF

;SEND ON CONVERSATION @h MESSAGE TYPE BLOB

(CONVERT(varbinary, 'Test Message'))

4.Получаем сообщения

1.Объявить переменные для хранения сведений о сообщении

2.Вызвать инструкцию ;RECEIVE

3.Проверить тип сообщения и обработать соответствующим образом

4.По завершении общения вызвать инструкцию END CONVERSATION

DECLARE @h UNIQUEIDENTIFIER

DECLARE @ReplyReceivedMessage VARCHAR(1000) , @MsgType NVARCHAR(255)

;RECEIVE TOP(1)

@h=Conversation_Handle

,@MsgType = message_type_name

,@ReplyReceivedMessage=Message_Body

FROM BLOB_Queue_Target;

IF @MsgType = N'BLOB'

--Обработка сообщения

END CONVERSATION @h;

4.Автоматическая обработка сообщений из очереди (Активация)

1.Создаем таблицу куда будем сохранять все сообщения из очереди

CREATE TABLE TestActivation

(

    theMessage VARBINARY(MAX)

)

2.Создаем процедуру которая будет читать сообщения из очереди

CREATE PROCEDURE InsertTestActivation

AS

BEGIN

    SET NOCOUNT ON

    DECLARE

        @h UNIQUEIDENTIFIER,

        @t sysname,

        @b varbinary(max)

    --Get all of the messages on the queue

    WHILE 1=1

    BEGIN

        SET @h = NULL

        --Note the semicolon..!

        ;RECEIVE TOP(1)

            @h = conversation_handle,

            @t = message_type_name,

            @b = message_body

        FROM BLOB_Queue_Target

        --No message RECEIVEd

        IF @h IS NULL

        BEGIN

            BREAK

        END

        --BLOB message

        ELSE IF @t = 'BLOB'

        BEGIN

            INSERT TestActivation

            VALUES (@b)

        END

        --EndDialog message

        ELSE IF @t = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

        BEGIN

            INSERT TestActivation

            VALUES (CONVERT(varbinary(MAX), 'EndDialog'))

            END CONVERSATION @h

        END

        --Any other message type

        ELSE

        BEGIN

            INSERT TestActivation

            VALUES (CONVERT(varbinary(MAX), 'Unknown'))

        END

    END       

END

3. Выполняем активацию очереди процедурой

ALTER QUEUE BLOB_Queue_Target

WITH ACTIVATION

(

    STATUS = ON,

    PROCEDURE_NAME = InsertTestActivation,

    MAX_QUEUE_READERS = 1, --максимальное количество обработчиков

    EXECUTE AS OWNER

)

--проверить включена ли активация

SELECT

    name,

    activation_procedure,

    is_activation_enabled

FROM sys.service_queues

WHERE name = 'BLOB_Queue_Target'

5.Маршруты и сообщения между экземплярами и базами

До сих пор мы отправляли и получали сообщения в пределах одной базы.

Для обмена сообщений между экземплярами и базами необходимо создать конечную точку и маршрут.

По умолчанию экземпляр SQL Server не содержит конечных точек компонента Service Broker. Таким образом, по умолчанию компонент Service Broker не отправляет и не получает сообщения по сети. Чтобы иметь возможность отправлять и получать сообщения за пределами экземпляра SQL Server, необходимо создать конечную точку компонента Service Broker.

1.Создаем объекты для обмена сообщениями тип сообщения, контракт, очередь сервис на удаленной машине.

CREATE MESSAGE TYPE BLOB

VALIDATION = NONE

GO

CREATE CONTRACT BLOB_Contract

(BLOB SENT BY ANY)

GO

CREATE QUEUE BLOB_Queue_Remote

GO

CREATE SERVICE BLOB_Service_Remote

ON QUEUE BLOB_Queue_Remote

(BLOB_Contract)

GO

2. Создаем конечную точку

CREATE ENDPOINT SSB_Endpoint

STATE = STARTED

AS TCP

(

    LISTENER_PORT = 9998

)

FOR SERVICE_BROKER

(

    AUTHENTICATION = WINDOWS,

    ENCRYPTION = DISABLED

)

GO

SELECT
    name,
    state_desc
FROM sys.service_broker_endpoints

3. Чтобы отправить сообщение необходимо иметь маршрут  по которому будет передаваться сообщение для этого  узнаем GUID  SERVICE BROKER

SELECT service_broker_guid

FROM sys.databases

WHERE name = 'RemoteServiceBroker'

--создаем маршрут

CREATE ROUTE TestRemoteRoute

WITH

    SERVICE_NAME = 'BLOB_Service_Remote',

    ADDRESS = 'tcp://remotehost:9998',

    --Use the GUID from above

    BROKER_INSTANCE = 'CE38831B-0193-4D3F-AC11-44489BC69B63'

/*
Проверить созданный Маршрут можно запросом
*/SELECT
    remote_service_name,
    broker_instance,
    address
FROM sys.routes
WHERE name = 'TestRemoteRoute'
GO

Дать права на отправку

GRANT SEND ON SERVICE::BLOB_Service_Remote TO [Public]; 

Отправляем сообщение

DECLARE @h UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @h

FROM SERVICE BLOB_Service_Init

TO SERVICE 'BLOB_Service_Remote'

ON CONTRACT BLOB_Contract

WITH ENCRYPTION=OFF

;SEND ON CONVERSATION @h

MESSAGE TYPE BLOB

(CONVERT(VARBINARY, 'Test_Remote'))

Подробно можно почитать здесь