service broker lesson 11. skills matrix service broker service broker, provides a solution to common...
TRANSCRIPT
![Page 1: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/1.jpg)
Service BrokerService BrokerLesson 11
![Page 2: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/2.jpg)
Skills MatrixSkills Matrix
![Page 3: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/3.jpg)
Service BrokerService Broker
• Service Broker, provides a solution to common problems with message delivery and consistency that occur when transferring transactional messages or data from one server to another.
• This technology allows SQL Server to implement its own architecture to create a queue-based message delivery system that guarantees delivery.
![Page 4: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/4.jpg)
Service BrokerService Broker• Service Broker includes the infrastructure
for asynchronous programming, which can be used for applications within a single database or a single instance, as well as for distributed applications.
• Service Broker also makes it easy to scale your application up or down to accommodate the amount of traffic it receives. – When a number of requests are queued
up, you actually can implement more queue readers to process requests.
![Page 5: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/5.jpg)
Queue MechanismQueue Mechanism
• The notable feature of this queue mechanism means that SQL Server guarantees that messages will be submitted to and processed from the queue in the correct order.
![Page 6: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/6.jpg)
Broker ArchitectureBroker Architecture
![Page 7: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/7.jpg)
Service BrokerService Broker• Most of the messages in a Service Broker
conversation are the application-defined messages used to communicate between services. – Each message must comply with a
message type format that was defined by a CREATE MESSAGE TYPE statement.
– The set of message types allowed for a conversation is defined by the contract specified in the BEGIN DIALOG CONVERSATION statement.
![Page 8: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/8.jpg)
![Page 9: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/9.jpg)
Service BrokerService Broker
• When designing a broker architecture, you must first define which messages you need in the application.
• You can specify which message type can be sent by the initiator and what can be returned by the target or destination.
![Page 10: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/10.jpg)
Service BrokerService Broker
• The messages sent will be submitted to a queue and processed by a service program. – To communicate from one service to
the other, you also have to specify the route to get to that destination.
– When defining which service can send and receive information from each other, you have to set up an agreement, called a contract.
![Page 11: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/11.jpg)
Creating a Message TypeCreating a Message Type
• You can create a message type using the CREATE MESSAGE TYPE statement.
• In creating these message types, you can define different types, depending on how you structure the message body.
![Page 12: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/12.jpg)
Creating a Message TypeCreating a Message Type• The SQL Server Service Broker architecture
supports the following message bodies:– None: No validation on the content takes
place; you can send whatever you like in the body.
– Header only: empty: This includes the header only; you send a message with an empty body.
– Well-formed XML: The data in the message body needs to be well-formed XML.
– Schema-bound XML: The data in the message body needs to be schema-validated XML. This means the message-body XML schema must exist on the server.
![Page 13: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/13.jpg)
Creating a QueueCreating a Queue
• You must create a queue as the next step in configuring a broker architecture.
• You can specify what should happen to the queue when you submit a message, which you can do by configuring its status and activation properties.CREATE QUEUE <object>
![Page 14: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/14.jpg)
Creating a ContractCreating a Contract
• In a contract, you set up the agreement between the sender of a message and the recipient. – This contract defines which message
type can be sent by the initiator and what can be returned by the target.
CREATE CONTRACT contract_name
![Page 15: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/15.jpg)
Creating a ServiceCreating a Service• Now that you have configured queues,
message types, and a contract, you can set up the service.
• A service references a contract. • Service Broker uses the name of the service
to route messages, deliver messages to the correct queue within a database, and enforce the contract for a conversation.
• If you are targeting multiple services to participate in the broker service, you need to create the routes between them.
![Page 16: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/16.jpg)
Creating a ServiceCreating a Service
• You can assign multiple contracts to a service, as well.
• Service programs initiate conversations to this service using the contracts specified.
• If no contracts are specified, the service may only initiate conversations.
![Page 17: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/17.jpg)
Create a ServiceCreate a Service
• Once the services are configured, you can specify the route between them if you communicate over multiple services.
• When not configured, you use the local database service and default service route.
• The CREATE SERVICE statement follows this syntax:CREATE SERVICE service_name
![Page 18: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/18.jpg)
Creating a RouteCreating a Route
• For outgoing messages, Service Broker determines the routing by checking the routing table in the local database.
• In a route, you specify the communication settings between services.
• Before creating a route, you should configure the HTTP endpoints by using the CREATE ENDPOINT statement.
![Page 19: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/19.jpg)
Creating a PriorityCreating a Priority
• SQL Server 2008 offers a new conversation priority feature.
• The message priority may be set from 1 (lowest) to 10 (highest) with a default of 5.
• First you must enable the priority feature, then set the priority:
![Page 20: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/20.jpg)
Using Service BrokerUsing Service Broker
• After successfully configuring the broker service, you can start sending messages over the broker instance.
• Key components in this process are the queue and the service program.
![Page 21: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/21.jpg)
Sending MessagesSending Messages
• You manage messages using a queue. • To group messages, you initiate a
dialog, in which you forward related messages.
• To identify a conversation, you use a unique identifier.
• You then have to end the conversation so the messages can be processed by the queue.
![Page 22: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/22.jpg)
Receiving MessagesReceiving Messages
• Once you have the message submitted to the recipient, you have to retrieve it from the receiving queue.
• Retrieving a message from a queue, however, uses the RECEIVE statement, which processes every message.
• This message retrieval process may be complex, because you may want to retrieve messages from the queue one by one or all at once.
![Page 23: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/23.jpg)
Receiving MessagesReceiving Messages• The RECEIVE statement processes the
message from the queue and stores it in the variable. – If the message type received is
TicketRequest, you proceed by executing a stored procedure to which you pass the message.
– The WAITFOR statement waits until receiving a message in the queue, while the RECEIVE statement with the TOP clause specifies how many messages you process at once.
![Page 24: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/24.jpg)
Automating the Queue ProcessingAutomating the Queue Processing
• To automate the queue processing, you can change the status of a queue (or specify this when you create the queue).
• When making changes to the queue, you execute the ALTER QUEUE statement,
![Page 25: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/25.jpg)
SQL Server Service Broker ArchitectureSQL Server Service Broker Architecture
• As you probably noticed, the SQL Server Service Broker architecture requires a lot of syntax and SQL Server does not provide a graphical interface to configure queues, and so on. – However, you can review created
queues and other related broker service objects in Object Explorer.
![Page 26: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/26.jpg)
SummarySummary
• In this lesson, you learned how to configure a Service Broker architecture. – A Service Broker architecture proves
useful in an environment where you need a message-based system or a queue-based system.
• Since SQL Server disables features by default, you need to enable Service Broker on the database level before you can start using it.
![Page 27: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/27.jpg)
SummarySummary
• Because of security reasons, Service Broker, by default, communicates only on local servers. – If you want to allow external access, you
need to configure an endpoint for Service Broker.
– You learned how to do this in this lesson. – HTTP can use endpoints for SOAP
requests, and endpoints can provide access to the database from any client that can explore and use a Web service.
![Page 28: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/28.jpg)
SummarySummary
• Service Broker works with message types, contracts, queues, and service programs. – On a queue, you can specify how an
endpoint should be activated. – The entire configuration of the Web
service, including the permissions and IP restrictions, are managed within SQL Server.
– Users need to be granted permissions before they can connect to an endpoint.
![Page 29: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/29.jpg)
Summary for Certification ExaminationSummary for Certification Examination• Know how to enable, configure and work with the
Service Broker. – Understand the Service Broker architecture and the
components it consists of in terms of services, service programs, contracts, message types, and routing.
• Understand how to create a service. Know how to create a service by identifying which contract is used, and be able to configure and identify the CREATE SERVICE syntax.
• Understand how to create a queue. On a queue, you have the possibility to initiate the activation of the queue and specify the service program to use (stored procedure).
![Page 30: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/30.jpg)
Summary for Certification ExaminationSummary for Certification Examination• Understand how to create a contract. In a
contract, you identify what message types the initiator can send and what can be returned by the sender.
• Understand how to create a message type. Message type bodies can be empty (i.e., header-only messages), or they may need to be well-formed XML or match an XML schema collection.
• Know how to initiate a conversation. When submitting messages to a queue, you need to create a conversation. Within a conversation, you can submit multiple messages that relate to each other.
![Page 31: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/31.jpg)
Summary for Certification ExaminationSummary for Certification Examination
• Understand how to send and receive messages. – When sending and receiving
messages, you use the SEND and RECEIVE keywords.
– You need to fully understand the syntax, how to retrieve messages from a queue, and how to close the conversation when it has concluded.
![Page 32: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/32.jpg)
Summary for Certification ExaminationSummary for Certification Examination
• Understand how to create and secure HTTP endpoints.
• When creating endpoints, you need to identify the virtual directory or path on the server, as well as configure the methods that can be used by the endpoint.
• Know that you manage within the endpoint configuration and that a user must have the appropriate permissions to connect to an endpoint.
![Page 33: Service Broker Lesson 11. Skills Matrix Service Broker Service Broker, provides a solution to common problems with message delivery and consistency that](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649da75503460f94a93a21/html5/thumbnails/33.jpg)
SummarySummary
• You can partition views in the same way you can partition tables and for the same reasons: store parts of your views on different spindles or even different servers.