Introdução ao Service Broker – Parte 2 – Diálogo e Permissões

Caminhando com o segundo artigo sobre Service Broker, entenderemos como criar um diálogo entre dois bancos de dados na mesma instância do SQL Server. Ao contrário do monólogo, que foi apresentado no artigo anterior, os diálogos que requerem a interação entre dois bancos precisam, além das configurações padrões do Service Broker, também da opção “TRUSTWORTHY” habilitada:

USE [master];

CREATE DATABASE DB01;

ALTER DATABASE DB01
	SET ENABLE_BROKER;

ALTER DATABASE DB01
	SET TRUSTWORTHY ON;

CREATE DATABASE DB02;

ALTER DATABASE DB02
	SET ENABLE_BROKER;

ALTER DATABASE DB02
	SET TRUSTWORTHY ON;

GO

USE DB01;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'su93rS3nh4?db01';

USE DB02;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'su93rS3nh4?db02';

GO

Para que os serviços que realizarão este diálogo possam entender as mensagens enviadas e recebidas, assim como os contratos estabelecidos, é necessário que haja nos dois bancos de dados a mesma definição de contratos e tipos de mensagens.

USE DB01;

CREATE MESSAGE TYPE Requisicao
	VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE Resposta
	VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT Contrato (
	Requisicao SENT BY INITIATOR,
	Resposta SENT BY TARGET
);

USE DB02;

CREATE MESSAGE TYPE Requisicao
	VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE Resposta
	VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT Contrato (
	Requisicao SENT BY INITIATOR,
	Resposta SENT BY TARGET
);

Por fim, estabelecemos uma fila e serviço para requisições no banco de dados que receberá a interação, e também uma fila e serviço para respostas no banco de dados que inicia a interação:

USE DB02;

CREATE QUEUE FilaRequisicao
	WITH STATUS = ON;

CREATE SERVICE ServicoRequisicao
	ON QUEUE FilaRequisicao (Contrato);

USE DB01;

CREATE QUEUE FilaResposta
	WITH STATUS = ON;

CREATE SERVICE ServicoResposta
	ON QUEUE FilaResposta (Contrato);

GO

Terminada a estrutura para o diálogo, iniciamos a interação a partir do serviço de resposta para com o serviço de requisição:

USE DB01;

DECLARE @Mensagem XML = 'Requisição'
, @MensagemId UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @MensagemId
	FROM SERVICE ServicoResposta
	TO SERVICE 'ServicoRequisicao'
	ON CONTRACT Contrato;

SEND ON CONVERSATION @MensagemId
	MESSAGE TYPE Requisicao(@Mensagem);

Recebemos a mensagem da fila de requisições e enviamos uma mensagem de respostas:

USE DB02;

DECLARE @Mensagem XML
, @MensagemId UNIQUEIDENTIFIER;

RECEIVE TOP(1)
	@Mensagem = [message_body],
	@MensagemId = [conversation_handle]
FROM FilaRequisicao;

SELECT @MensagemId, @Mensagem;

IF @MensagemId IS NOT NULL
BEGIN
	SET @Mensagem = 'Resposta';

	SEND ON CONVERSATION @MensagemId
		MESSAGE TYPE Resposta(@Mensagem);
END

Por fim, recuperamos da fila de respostas a mensagem, concluindo a comunicação:

USE DB01;

DECLARE @Mensagem XML
, @MensagemId UNIQUEIDENTIFIER;

RECEIVE TOP(1)
	@Mensagem = [message_body],
	@MensagemId = [conversation_handle]
FROM FilaResposta;

SELECT @MensagemId, @Mensagem;

IF @MensagemId IS NOT NULL
	END CONVERSATION @MensagemId;

Permissões

Em relação os usuários que possuem acessos limitados, mas que precisam realizar as interações, só será necessário que eles tenham a permissão de CONTROL das filas dos seus respectivos bancos de dados, por exemplo:

-- Usuário que poderá ser utilizado para interações dos serviços relacionados à fila de resposta.
USE DB01;

CREATE LOGIN Maria WITH PASSWORD = 'su93rS3nh4';

CREATE USER Maria FOR LOGIN Maria;

GRANT CONTROL ON dbo.FilaResposta TO Maria;

GO

-- Usuário que poderá ser utilizado para interações dos serviços relacionados à fila de requisição.
USE DB02;

CREATE LOGIN Heitor WITH PASSWORD = 'su93rS3nh4';

CREATE USER Heitor FOR LOGIN Heitor;

GRANT CONTROL ON dbo.FilaRequisicao TO Heitor;

GO

Por fim, uma interação completa utilizando somente da permissão de CONTROL destes usuários:

USE DB01;

EXEC AS LOGIN = 'Maria';

USE DB01;

DECLARE @Mensagem XML = 'Requisição'
, @MensagemId UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @MensagemId
	FROM SERVICE ServicoResposta
	TO SERVICE 'ServicoRequisicao'
	ON CONTRACT Contrato;

SEND ON CONVERSATION @MensagemId
	MESSAGE TYPE Requisicao(@Mensagem);

REVERT;

GO

USE DB02;

EXEC AS LOGIN = 'Heitor';

USE DB02;

DECLARE @Mensagem XML
, @MensagemId UNIQUEIDENTIFIER;

RECEIVE TOP(1)
	@Mensagem = [message_body],
	@MensagemId = [conversation_handle]
FROM FilaRequisicao;

SELECT @MensagemId, @Mensagem;

IF @MensagemId IS NOT NULL
BEGIN
	SET @Mensagem = 'Resposta';

	SEND ON CONVERSATION @MensagemId
		MESSAGE TYPE Resposta(@Mensagem);
END

REVERT;

GO

USE DB01;

EXEC AS LOGIN = 'Maria';

USE DB01;

DECLARE @Mensagem XML
, @MensagemId UNIQUEIDENTIFIER;

RECEIVE TOP(1)
	@Mensagem = [message_body],
	@MensagemId = [conversation_handle]
FROM FilaResposta;

SELECT @MensagemId, @Mensagem;

IF @MensagemId IS NOT NULL
	END CONVERSATION @MensagemId;

REVERT;

GO