I am in an environment where we have many untrusted domains running SQL Server and want to minimize/stop using SQL Server accounts. I want to use Service Broker to use certificate based authentication so I can send monitoring information from all the different servers to a central monitoring database server, such as backup history, DMV information, and a few others. Something that's a very 'simple use case' scenario for service broker and have watched the PASS2011 video by @SQLRunr, Bob Beauchmann's video on SQLSkills, and read numerous blogs but am still confused by getting a simple SB app up and running.
I created a table tbl_1 with the tables and stored procs needed to populate them with the relevant information and now need to send it over via SB to our central server. There are currently just 2 tables. 1 for a list of all the DBs on that server, and 1 that holds some MSDB backup information. The goal is to transfer these to the central server nightly or at some other set interval.
Right now I can:
Send a request from one DB to another if there's only 1 row
See the results of the request on the other
End the conversation at the target
-Populate the information into a different table
-End conversation on the initiators end
-Select a * from a row
This is my query to initiate the conversation. T he problem is in the SELECT TOP 1 CustomerName FROM.. I want it to be SELECT * FROM
/* Actual Messaging */ DECLARE @InitDlgHandle UNIQUEIDENTIFIER ; DECLARE @ChangeMsg NVARCHAR(MAX); BEGIN DIALOG CONVERSATION @InitDlgHandle FROM SERVICE [//CentMon/LC0/InitMsgTypeService] TO SERVICE '//CentMon/LC0/ReplyMsgTypeService' ON CONTRACT [//CentMon/LC0/InitAndReplyMsgTypeContract] ; SET @ChangeMsg = (SELECT TOP 1 CustomerName FROM [dbo].[CBU] WHERE [BUFinishDate] > GETDATE() -1 ) ; BEGIN TRANSACTION; SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//CentMon/LC0/InitMsgType] (@ChangeMsg); COMMIT TRANSACTION END CONVERSATION @InitDlgHandle
This is how I am currently receiving the message, I'd like to receive it and place it into a table on a different DB on a diff server (currently though everything is on the same server)
DECLARE @ch UNIQUEIDENTIFIER DECLARE @messagetypename NVARCHAR(256), @service_name NVARCHAR(512), @service_contract_name NVARCHAR(256) DECLARE @messagebody NVARCHAR(MAX)
RECEIVE TOP(1) @ch = conversation_handle, --@service_name, @service_contract_name = service_contract_name, @messagetypename = message_type_name, @messagebody = message_body FROM ReplyMsgTypeQueue
This is how I end the conversation at the target, I don't know how to automatically tell it to just end the conversation and also not sure how to end it on the initiators end:
--I get the conversation ID from sys.conversation_endpoints
DECLARE @h UNIQUEIDENTIFIER SET @h = '846430FB-FC78-E111-B180-0800272EE368' END CONVERSATION @h GO
Any help is greatly appreciated! Next I have to get this working between different servers and I don't have to worry about the plumbing for a monitoring solution across all these untrusted domains! Thanks.
Support my SQL MCM & Internal Training blog on AliRazeghi.Com. Have a tough I.T. job and you need to talk to someone with no obligation? Contact my certified associates and I at Rosonco.com!
If you find a question answered please click 'mark as answer' or 'vote as helpful'. This will help other users find answers quickly. Thanks for visiting!
- Edited by Ali Razeghi Wednesday, March 28, 2012 6:27 PM
Hi Ali Razeghi,
To receive the message and put them into a table, you can directly query the information directly from the receive queue, and end the conversation according to the conversation handle from the receive queue:
--Change the target table and filter columns you needed. You can use Linked Server for a remote SQL Server instance. INSERT INTO TABLE_NAME SELECT * FROM ReplyMsgTypeQueue --End the conversation DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER; RECEIVE TOP(1) @RecvReqDlgHandle = conversation_handle FROM ReplyMsgTypeQueue END CONVERSATION @RecvReqDlgHandle;For more information, please pay attention to: Completing a Conversation Between Instances.
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
TechNet Community Support
- Marked as answer by Ali Razeghi Thursday, March 29, 2012 4:52 PM