SQL Server 2008 - Broker Service

Question

Hello All

I have some questions about SQL Server 2008 Broker Service. Here is the context:

SQL Server 2008 (replicated tables) --> Broker Service --> "A DLL 3rd party" --> Video Application

When some values are updated / inserted in the tables after replication, the service broker informs a 3rd DLL party in order to recover the different messages in the appropriated queues and then this DLL writes information in the application. But sometime, some messages are disappeared. So:

- if I activate the retention setting on broker service, can I store the messages ? how many time ? and can I export them (if possible) ? Is it possible to do a replay of these messages ?

- where can I look for the service broker ? (system tables, system views or in the application DB ?)

- All the SQL Server broker service activities are cetainly logged. But, where  ? 

The SQL Server Developer team creates contracts and services for the broker, but they are not visible in SSMS. Is this a display bug or not ?

---

Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

Answer 1

Hi RHUM2,

I would like to involve someone familiar with this issue to have a look, and give an update as soon as possible. Thanks for your understanding.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi,

The more information you can provide about your architecture the better, not sure I understand your setup. I will try to answer some of these.

We only recommend using retention to troubleshoot and not to run permenantly in production. The messages will stay in the queue until the conversation is ended. It will then be removed. Instead of retention some will insert the message and other details in a audit table for tracking. You can not "replay" the message but when you receive the message from the queue you do have the option to resend the same message to a queue. You would have to code this in your activation stored procedure or your external application that is receiving the messages from the queue.

RETENTION (create queue command in books online)

Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations that use this queue are retained in the queue until the conversations have ended. This lets you retain messages for auditing purposes, or to perform compensating transactions if an error occurs. If this clause is not specified, the retention setting defaults to OFF.

Service broker has several system tables and views that you can use:

Two of the most important are sys.conversation_endpoint and sys.transmission_queue. Conversation_endpoint lists metadata about each conversation. Transmission_queue holds metadata on messges sent to the target. When you send a message it adds a record to the sys.transmission_queue. When the target recevices the message in the queue and ACK is sent back to the initiator. When the ACK is received the message will be removed from sys.transmission_queue. If not service broker will try to resend the message.

Some other helpful service broker tables and view. See books online for complete list.

select * from sys.dm_broker_activated_tasks
select * from sys.dm_broker_connections
select * from sys.dm_broker_queue_monitors
select * from sys.service_broker_endpoints
select * from sys.service_queues
select * from sys.routes

Service broker activity is not logged but you can certainly add logging to your activation stored procedure or application to log activity. Profiler is a good tool and has several broker events to help troubleshoot. Also two places to check for errors are your sql server error logs and the transmission_status column in sys.transmission_queue.

SQL Server Management Studio does expose service broker information. Under a database you will see a service broker folder. Expand this and you will see more for folders for message types, contracts, queues, services, routes. If you do not see them then you will need to check you permissions.

 

Resources:

Books: http://www.apress.com/9781590599990

Blog:  http://blogs.msdn.com/b/sql_service_broker/   and   http://rusanu.com/articles/

CodePlex:  http://msftsbprodsamples.codeplex.com/

Books Online

Some of the questions I would ask is how do you know that messages disappear? How are the messages sent to the queue? What is receving the messages out of the queue, an activation stored procedure or a external application? If the messages are being sent by a trigger on a replicated table, you might be able to add code to audit or track the send side. You might also run a profiler trace to see activity. I would also check your sys.transmission_queue (transmission_status) and conversation_endpoints tables.

Hope this helps some,

Bill -- Microsoft CSS

Answer 3

Here is a script that you can use to gather information on service broker objects, etc. Best to save the output as text (.rpt).

print '***sys.databases***'
select * from master.sys.databases where is_broker_enabled = 1 and name not in('tempdb', 'model', 'AdventureWorks', 'AdventureWorksDW')
print '***sys.dm_broker_activated_tasks***'
select * from sys.dm_broker_activated_tasks
print '***sys.dm_broker_connections***'
select * from sys.dm_broker_connections
print '***sys.dm_broker_forwarded_messages***'
select * from sys.dm_broker_forwarded_messages
print '***sys.dm_broker_queue_monitors***'
select * from sys.dm_broker_queue_monitors
print '***sys.service_broker_endpoints***'
select * from sys.service_broker_endpoints
print '***sys.tcp_endpoints***'
select * from sys.tcp_endpoints
print '***sys.certificates***'
select * from sys.certificates
print '***sys.database_mirroring***'
select * from sys.database_mirroring where mirroring_guid is not null
print '***sys.dm_db_mirroring_connections***'
select * from sys.dm_db_mirroring_connections
print '***sys.dm_os_memory_clerks (broker)***'
select * from sys.dm_os_memory_clerks where type like '%BROKER%' order by type desc

DECLARE tnames_cursor CURSOR
FOR select name from master.sys.databases where is_broker_enabled = 1 and name not in('tempdb', 'model', 'AdventureWorks', 'AdventureWorksDW')
OPEN tnames_cursor;
DECLARE @dbname sysname;
FETCH NEXT FROM tnames_cursor INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @dbname = RTRIM(@dbname);
 select @dbname;
 EXEC ('USE ' + @dbname);
 print ''
 print '***' + @dbname + '***'
 print '***sys.service_message_types***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_message_types');
 print '***sys.service_contract_message_usages***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contract_message_usages');
 print '***sys.service_contracts***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contracts');
 print '***sys.service_contract_usages***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_contract_usages');
 print '***sys.service_queues***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_queues');
 print '***sys.service_queue_usages***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.service_queue_usages');
 print '***sys.services***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.services');
 print '***sys.routes***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.routes');
 print '***sys.remote_service_bindings***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.remote_service_bindings');
 print '***sys.certificates***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.certificates');
 print '***sys.dm_qn_subscriptions***'
 EXEC ('SELECT * FROM ' + @dbname + '.sys.dm_qn_subscriptions');
 print '***sys.transmission_queue (count and top 1500)***'
 EXEC ('SELECT count(*) as count FROM ' + @dbname + '.sys.transmission_queue');
 EXEC ('SELECT top 1500 * FROM ' + @dbname + '.sys.transmission_queue');
 print '***sys.conversation_endpoints (count and top 1500)***'
 EXEC ('SELECT count(*) as count FROM ' + @dbname + '.sys.conversation_endpoints');
 EXEC ('SELECT top 1500 * FROM ' + @dbname + '.sys.conversation_endpoints');
 
   
   FETCH NEXT FROM tnames_cursor INTO @dbname;
END;
CLOSE tnames_cursor;
DEALLOCATE tnames_cursor;

Bill -- Microsoft CSS

Answer 4

Hi Bill !

Thanks for your replies and for the script. 

I have looked at the code and I think it is possible to add much checks or controls to provide a better log activity. I will examine and study your different links. I did not know this SQL Server Brokers's blog team.

I have all Sysadmin rights and in SSMS, I don't always see contracts and services (note that those will be created directly in SQL code). 

---

Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2