Service Broker in action Tomasz Libera tomasz liberaplssug

Service Broker in action Tomasz Libera tomasz. libera@plssug. org. pl @tomasz_libera

SQLSat Kyiv Team Olena Smoliak Oksana Borysenko Vitaliy Popovych Yevhen Nedashkivskyi Mykola Pobyivovk

3 Sponsor Sessions at 12: 40 § Don’t miss them, they might be providing some interesting and valuable information! Room A Room B Room C 13: 00 - 13: 15 Intapp DB Best NULL 13: 20 - 13: 50 Microsoft Dev. Art Jet. Brains

Our Awesome Sponsors

Session will begin very soon : ) § Please complete the evaluation form from your pocket after the session. Your feedback will help us to improve future conferences and speakers will appreciate your feedback! § Enjoy the conference!

Tomasz Libera § DB Developer - WSZi. B Krakow § Polish SQL Server User Group Association § plssug. org. pl § Member of The Board § Krakow Local Group Leader § Certificates § MCT, § MCSE § MCITP-DBA, MCITP-DD… § Interests § mountain biking § MTB marathons

Agenda § Introduction, History § Anatomy of Service Broker Message, Contract, Service § § First Conversation Activation Troubleshooting SB as SQL Server Agent

Introduction § § Introduced in SQL Server 2005 Asynchronous messaging system Very little support in SSMS GUI Specific T-SQL clauses § SEND § RECEIVE § Receiving and processing messages is done on demand or based on activation

Send and Receive § SEND puts a message on a queue (equivalent of INSERT) SEND ON CONVERSATION @Ch MESSAGE TYPE [//PLSSUG/SQLSB/My. Msg] (@Msg. Body); § RECEIVE removes a message from a queue (equivalent of SELECT) RECEIVE TOP(1) @Ch = conversation_handle, @Msg. Type. Name = message_type_name, @Msg. Body = CAST(message_body AS XML) FROM Receiver. Queue

History • Multicast - Messages can be sent to multiple target services • message_enqueue_time • Poison message handling can be disabled • Always. On support • Service Broker introduction SQL 2005 SQL 2008 SQL 2012 • Conversation priorities • SSBdiagnose utility • Alternate poison message handling • Performance objects/counters SQL 2014 • REBUILD QUEUE SQL 2016 • No changes

Agenda § Intruduction, History § Anatomy of Service Broker Message, Contract, Service § § First Conversation Activation Troubleshooting SB as SQL Server Agent

Dialog § Must be started before exchanging messages § Messages are being sent § in the same database § in different databases in the same instance § in different databases in different instances BEGIN DIALOG CONVERSATION @Ch FROM SERVICE [//PLSSUG/SQLSB/Sender. Service] TO SERVICE '//PLSSUG/SQLSB/Receiver. Service', 'CURRENT DATABASE' ON CONTRACT [//PLSSUG/SQLSB/Contract] WITH ENCRYPTION = OFF;

Message § VARBINARY(MAX) structure § Defines the name and the validation § § NONE (default) – no validation is performed. EMPTY - message body must be NULL. WELL_FORMED_XML (most popular) - must contain well-formed XML. VALID_XML WITH SCHEMA COLLECTION - message body must contain XML that complies with a schema in the specified schema collection § XML is implemented internally in SQL Server as a binary structure

Service Broker Anatomy § Queue - sys. service_queue An object which hold messages § Service - sys. services Messages are being sent to services which placed them on the queue § Message – sys. service_message_types The name and the validation § Contract - sys. service_contracts What types of messages can be used by sender and receiver § Route – sys. routes § Remote service binding - sys. remote_service_binding

Agenda § Introduction, History § Anatomy of Service Broker Message, Contract, Service § § First Conversation Activation Troubleshooting SB as SQL Server Agent

Message exchange in Service Broker SENDER SERVICE RECEIVER SERVICE SENDER QUEUE SYS. TRANSMISSION QUEUE STORED PROCEDURE RECEIVER QUEUE request massage SYS. TRANSMISSION QUEUE STORED PROCEDURE

Message exchange in Service Broker SENDER SERVICE RECEIVER SERVICE SENDER QUEUE SYS. TRANSMISSION QUEUE STORED PROCEDURE RECEIVER QUEUE response messages SYS. TRANSMISSION QUEUE STORED PROCEDURE

DEMO 1: Setup DEMO 2: First conversation § Create § § message types contracts services queues § Begin conversation § Send and receive messages § End conversation

/SQL/Service. Broker/End. Dialog § Message. Type that marks the endpoint as closed http: //schemas. microsoft. com/SQL/Service. Broker/End. Dialog § When endpoint receives the End. Dialog message, it can’t send messages on the dialog § Must be sent by both services END CONVERSATION @Ch § Could be sent because of an error END CONVERSATION @Ch WITH ERROR = 31472 DESCRIPTION = 'Something goes wrong' § To cleanup orphaned dialogs END CONVERSATION @Ch WITH CLEANUP

DEMO 2 B: End. Dialog § END CONVERSATION WITH ERROR

Agenda § Introduction, History § Anatomy of Service Broker Message, Contract, Service § § First Conversation Activation Troubleshooting SB as SQL Server Agent

Activation § Activation lunches the process in response to the enqueue receiving messages § Internal activation - runs the stored procedure within the database. § External activation - external process (. NET application) , processes messages which are waiting in the queue. Both types of activation has ability to launch multiple processes to handle large volume of messages in parallel

DEMO 3: Activation § Create activation stored procedures § For Sender § For Receiver § Alter queue to enable activation § Test activation

Agenda § Introduction, History § Anatomy of Service Broker Message, Contract, Service § § First Conversation Activation Troubleshooting SB as SQL Server Agent

Troubleshooting § Transmission queue § Poison message § Error Handling

Transmission queue § sys. transmission_queue § One in every database. § Stores messages temporarily before they reach destination § Important for troubleshooting - transmission_status § Is used: § The destination is on a different SQL Server instance § The destination is disabled STATUS=OFF (poison message) § Service Broker is disabled (after restore/attach) § The destination is unknown

Poison messages § When error occurs during message processing – transaction is rolled back and message is going back to the queue § After 5 rollbacks in a row – a queue will be disabled to stop the poison message § SOLUTION: prevent

DEMO 4: transmission queue § Sending message to disabled queue § Disabled Service. Broker § Poison message

/SQL/Service. Broker/Error § Message. Type when a dialog is ended with error http: //schemas. microsoft. com/SQL/Service. Broker/End. Dialog § When endpoint receives the Error message, neither side of conversation can send messages § Can contain error number and description § Usually used in CATCH block END CONVERSATION @Ch WITH ERROR = 5555 DESCRIPTION = 'Incorrect e-mail address'

DEMO 5: Error Handling § TRY…CATCH code in activated procedures

Do you want more? § Books § Pro SQL Server 2008 Service Broker - Klaus Aschenbrenner http: //www. apress. com/9781590599990 § Inside Microsoft SQL Server 2008 T-SQL Programming (Roger Wolter chapter) § Links § RUSANU – the best source about troubleshooting Service Broker http: //rusanu. com/articles § SQLDay 2013 | DBA/DEV Track | Denny Cherry - Getting SQL Service Broker Up and Running https: //www. youtube. com/watch? v=5 MGlq. Cf. G 1 p 0 § SQL Server Service Broker http: //msdn. microsoft. com/en-us/library/bb 522893(v=sql. 105). aspx § Scheduling Jobs in SQL Server Express http: //www. sqlteam. com/article/scheduling-jobs-in-sql-server-express § SQL Server 2012 – What’s new in Service Broker § http: //sqlwithsanil. com/2012/05/18/sql-server-2012 -whats-new-in-service-broker/ § How to use Service Broker to scale out SQL Server database applications https: //www. youtube. com/watch? v=CXPJL 1 jhip. A

FOR HELP, CONTACT ME: tomasz. libera@plssug. org. pl

THANK YOU