SQL Server 2008 - Broker Service

Toutes les réponses

• mercredi 7 mars 2012 07:54

  Modérateur

   

   
  

  0

  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

  

  • Réponse
  • Citation

   
• mercredi 7 mars 2012 18:52

   

   
  

  0

  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

  • Marqué comme réponse RHUM2 lundi 11 mars 2013 15:04
  •  
  • Réponse
  • Citation

   
• mercredi 7 mars 2012 18:55

   

   
  

  0

  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

  • Marqué comme réponse RHUM2 lundi 11 mars 2013 15:04
  •  
  • Réponse
  • Citation

   
• mercredi 25 avril 2012 12:29

   

   
  

  0

  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

  • Réponse
  • Citation