Transcript [Powerpoint] - SQLSat_Kiev_2016_ServiceBrokerx

Service Broker in action
Tomasz Libera
[email protected]
@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
DevArt
JetBrains
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 - WSZiB 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
www.wszib.edu.pl
 Університет управління та
банківської справи пропонує
навчання на таких академічних
ступнях: бакалавр, спеціаліст,
магістр і післядипломна освіта.
Вуз оснащений є у власну
дидактичну базу.
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/MyMsg]
(@MsgBody);
 RECEIVE removes a message from a queue
(equivalent of SELECT)
RECEIVE TOP(1)
@Ch = conversation_handle,
@MsgTypeName = message_type_name,
@MsgBody = CAST(message_body AS XML)
FROM ReceiverQueue
History
• Multicast - Messages can be
sent to multiple target services
• message_enqueue_time
• Poison message handling can
be disabled
• AlwaysOn 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/SenderService]
TO SERVICE '//PLSSUG/SQLSB/ReceiverService', '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
DEMO1: Setup
DEMO2: First conversation
 Create




message types
contracts
services
queues
 Begin conversation
 Send and receive messages
 End conversation
/SQL/ServiceBroker/EndDialog
 MessageType that marks the endpoint as closed
http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
 When endpoint receives the EndDialog 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
DEMO2B: EndDialog
 END CONVERSATION
 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
DEMO3: 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
DEMO4: transmission queue
 Sending message to disabled queue
 Disabled ServiceBroker
 Poison message
/SQL/ServiceBroker/Error
 MessageType when a dialog is ended with error
http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
 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'
DEMO5: 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=5MGlqCfG1p0
 SQL Server Service Broker
http://msdn.microsoft.com/en-us/library/bb522893(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=CXPJL1jhipA
FOR HELP, CONTACT ME:
[email protected]
THANK YOU