We have a lot of blogs and documents that can explain how service broker works.
https://msdn.microsoft.com/en-us/library/bb839489(v=sql.105).aspx
This step by step how to configure SQL Server Service Broker between two servers.
You have two Windows Servers with SQL Server each one with a database. We will use this list of SQL variables: $(SQLServer1) Name of Windows Server 1 $(SQLServer2) Name of Windows Server 2 $(SQLInstance1) Name of instance of SQL Server 1 $(SQLInstance2) Name of instance of SQL Server 2 $(Database1) Name of Database 1 $(Database2) Name of Database 2 $(Password) Password to create master key.
All Script must be run on SQLCMD Mode.
Creating the Target Database
1
2:CONNECT SQLONE
3USE master;
4GO
5IF EXISTS (SELECT * FROM master.sys.endpoints WHERE name = N'InstTargetEndpoint')
6 DROP ENDPOINT InstTargetEndpoint;
7GO
8CREATE ENDPOINT InstTargetEndpoint
9STATE = STARTED
10AS TCP ( LISTENER_PORT = 4022 )
11FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
12GO
13
14PRINT '-- Create the target database, master key, and user'
15USE master;
16GO
17IF EXISTS (SELECT * FROM sys.databases WHERE name = N'InstTargetDB')
18 DROP DATABASE InstTargetDB;
19GO
20CREATE DATABASE InstTargetDB;
21GO
22USE InstTargetDB;
23GO
24CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<EnterStrongPassword1Here>';
25GO
26CREATE USER TargetUser WITHOUT LOGIN;
27GO
28
29PRINT '-- Create the target certificate'
30USE InstTargetDB;
31GO
32CREATE CERTIFICATE InstTargetCertificate
33AUTHORIZATION TargetUser WITH SUBJECT = 'Target Certificate',
34EXPIRY_DATE = N'12/31/2099';
35
36BACKUP CERTIFICATE InstTargetCertificate
37 TO FILE = N'\\SQLONE\C$\temp\InstTargetCertificate.cer';
38GO
39
40PRINT '-- Create the message types'
41USE InstTargetDB;
42GO
43CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
44 VALIDATION = WELL_FORMED_XML;
45CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
46 VALIDATION = WELL_FORMED_XML;
47GO
48
49PRINT '-- Create the contract'
50USE InstTargetDB;
51GO
52CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
53 ([//BothDB/2InstSample/RequestMessage]
54 SENT BY INITIATOR,
55 [//BothDB/2InstSample/ReplyMessage]
56 SENT BY TARGET
57 );
58GO
59
60PRINT '-- Create the target queue and service'
61
62USE InstTargetDB;
63GO
64CREATE QUEUE InstTargetQueue;
65
66CREATE SERVICE [//TgtDB/2InstSample/TargetService]
67 AUTHORIZATION TargetUser
68 ON QUEUE InstTargetQueue
69 ([//BothDB/2InstSample/SimpleContract]);
70GO
Creating the Initiator Database
1
2:CONNECT SQLTWO
3
4PRINT '-- Create a Service Broker endpoint'
5USE master;
6GO
7IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'InstInitiatorEndpoint')
8 DROP ENDPOINT InstInitiatorEndpoint;
9GO
10CREATE ENDPOINT InstInitiatorEndpoint
11STATE = STARTED
12AS TCP ( LISTENER_PORT = 4022 )
13FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
14GO
15
16PRINT '-- Create the initiator database, master key, and user'
17USE master;
18GO
19IF EXISTS (SELECT * FROM sys.databases WHERE name = N'InstInitiatorDB')
20 DROP DATABASE InstInitiatorDB;
21GO
22CREATE DATABASE InstInitiatorDB;
23GO
24USE InstInitiatorDB;
25GO
26
27CREATE MASTER KEY
28 ENCRYPTION BY PASSWORD = N'<EnterStrongPassword2Here>';
29GO
30CREATE USER InitiatorUser WITHOUT LOGIN;
31GO
32
33PRINT '-- Create the initiator certificate'
34USE InstInitiatorDB;
35GO
36CREATE CERTIFICATE InstInitiatorCertificate
37 AUTHORIZATION InitiatorUser
38 WITH SUBJECT = N'Initiator Certificate',
39 EXPIRY_DATE = N'12/31/2099';
40
41BACKUP CERTIFICATE InstInitiatorCertificate
42 TO FILE = N'\\SQLONE\C$\temp\InstInitiatorCertificate.cer';
43GO
44
45PRINT '-- Create the message types'
46USE InstInitiatorDB;
47GO
48CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
49 VALIDATION = WELL_FORMED_XML;
50CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
51 VALIDATION = WELL_FORMED_XML;
52GO
53
54PRINT '-- Create the contract'
55USE InstInitiatorDB;
56GO
57CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
58 ([//BothDB/2InstSample/RequestMessage]
59 SENT BY INITIATOR,
60 [//BothDB/2InstSample/ReplyMessage]
61 SENT BY TARGET
62 );
63GO
64
65PRINT '-- Create the initiator queue and service'
66USE InstInitiatorDB;
67GO
68CREATE QUEUE InstInitiatorQueue;
69
70CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
71 AUTHORIZATION InitiatorUser
72 ON QUEUE InstInitiatorQueue;
73GO
74
75PRINT '-- Create references to target objects'
76USE InstInitiatorDB;
77GO
78CREATE USER TargetUser WITHOUT LOGIN;
79
80CREATE CERTIFICATE InstTargetCertificate
81 AUTHORIZATION TargetUser
82 FROM FILE =
83N'\\SQLONE\c$\temp\InstTargetCertificate.cer'
84GO
85
86PRINT '-- Create routes'
87USE InstInitiatorDB;
88GO
89DECLARE @Cmd NVARCHAR(4000);
90
91SET @Cmd = N'USE InstInitiatorDB;
92CREATE ROUTE InstTargetRoute
93WITH SERVICE_NAME = N''//TgtDB/2InstSample/TargetService'',
94 ADDRESS = N''TCP://SQLONE:4022'';';
95
96EXEC (@Cmd);
97
98SET @Cmd = N'USE msdb
99CREATE ROUTE InstInitiatorRoute
100WITH SERVICE_NAME = N''//InstDB/2InstSample/InitiatorService'',
101 ADDRESS = N''LOCAL''';
102
103EXEC (@Cmd);
104GO
105CREATE REMOTE SERVICE BINDING TargetBinding
106 TO SERVICE N'//TgtDB/2InstSample/TargetService'
107 WITH USER = TargetUser;
108
109GO
Completing the Target Conversation Objects
1
2:CONNECT SQLONE
3
4PRINT '-- Create references to initiator objects'
5USE InstTargetDB
6GO
7CREATE USER InitiatorUser WITHOUT LOGIN;
8
9CREATE CERTIFICATE InstInitiatorCertificate
10 AUTHORIZATION InitiatorUser
11 FROM FILE =
12N'\\SQLONE\C$\temp\InstInitiatorCertificate.cer';
13GO
14
15PRINT '-- Create routes'
16USE InstTargetDB
17GO
18DECLARE @Cmd NVARCHAR(4000);
19
20SET @Cmd = N'USE InstTargetDB;
21CREATE ROUTE InstInitiatorRoute
22WITH SERVICE_NAME =
23 N''//InstDB/2InstSample/InitiatorService'',
24 ADDRESS = N''TCP://SQLTWO:4022'';';
25
26EXEC (@Cmd);
27
28SET @Cmd = N'USE msdb
29CREATE ROUTE InstTargetRoute
30WITH SERVICE_NAME =
31 N''//TgtDB/2InstSample/TargetService'',
32 ADDRESS = N''LOCAL''';
33
34EXEC (@Cmd);
35GO
36GRANT SEND
37 ON SERVICE::[//TgtDB/2InstSample/TargetService]
38 TO InitiatorUser;
39GO
40CREATE REMOTE SERVICE BINDING InitiatorBinding
41 TO SERVICE N'//InstDB/2InstSample/InitiatorService'
42 WITH USER = InitiatorUser;
43GO
Beginning the Conversation
1
2:CONNECT SQLTWO
3
4USE InstInitiatorDB;
5GO
6
7DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
8DECLARE @RequestMsg NVARCHAR(100);
9
10BEGIN TRANSACTION;
11
12BEGIN DIALOG @InitDlgHandle
13 FROM SERVICE [//InstDB/2InstSample/InitiatorService]
14 TO SERVICE N'//TgtDB/2InstSample/TargetService'
15 ON CONTRACT [//BothDB/2InstSample/SimpleContract]
16 WITH
17 ENCRYPTION = ON;
18
19SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';
20
21SEND ON CONVERSATION @InitDlgHandle
22 MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
23 (@RequestMsg);
24
25SELECT @RequestMsg AS SentRequestMsg;
26
27COMMIT TRANSACTION;
28GO
Receiving a Request and Sending a Reply
1
2:CONNECT SQLONE
3USE InstTargetDB;
4GO
5DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
6DECLARE @RecvReqMsg NVARCHAR(100);
7DECLARE @RecvReqMsgName sysname;
8
9BEGIN TRANSACTION;
10
11WAITFOR
12( RECEIVE TOP(1)
13 @RecvReqDlgHandle = conversation_handle,
14 @RecvReqMsg = message_body,
15 @RecvReqMsgName = message_type_name
16 FROM InstTargetQueue
17), TIMEOUT 1000;
18
19SELECT @RecvReqMsg AS ReceivedRequestMsg;
20
21IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
22BEGIN
23 DECLARE @ReplyMsg NVARCHAR(100);
24 SELECT @ReplyMsg =
25 N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
26
27 SEND ON CONVERSATION @RecvReqDlgHandle
28 MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
29 (@ReplyMsg);
30
31 END CONVERSATION @RecvReqDlgHandle;
32END
33
34SELECT @ReplyMsg AS SentReplyMsg;
35
36COMMIT TRANSACTION;
37GO
Comments