Service Broker Essentials

Service Broker Essentials

Introduction to--service Broker
Service broker is to make SQL Server a platform for building reliable distributed asynchronous applications.
Service Broker is not a common message processing system.
Service Broker supports transactional message processing, which is the only way to ensure that messages are processed only once and sequentially.

--Application Scenario
1 reliable service-oriented architecture services Oriented Architecture (SOA)
2 Asynchronous Processing: Data extraction in the Data warehouse every night
If each are a SQL statement or stored procedure to process, a service could being written that receives messages from The queue and executes the contents of the message by passing it to a SQL EXEC statement.
A typical example of the night processing for a data warehouse.

--Fit the scene
Service Broker and MSMQ (Microsoft message Queue)
The maximum number of MSMQ messages can be 4MB, while Service Broker messages can be up to 2GB.
MSMQ provides a fast, reliable, transactional message style, while Service Broker provides only transactional.
MSMQ can communicate with any two Windows applications. Service Broker can only communicate between applications that are connected to SQL Server.

Service Broker and BizTalk
BizTalk can manipulate the content of messages, map message formats, manage message processing, manage workflows, manage status,
Send messages through multiple different senders.

Service Broker and WCF (Windows communication Foundation)
WCF supports multiple forms of message processing across multiple standards-based protocols between Windows and any operating system that implements the standard protocols supported by WCF.

--session
A dialog session (dialog conversation) is a reliable, sequential, bidirectional message delivery between two endpoints.
A monologue session (Monolog conversation) is a reliable, sequential stream of messages from the sender to multiple receivers, a set of conversations from one sender to the receiving end.
This feature is not implemented in SQL Server 2005.

Service Broker Three features: reliable, sequential, and asynchronous.

--Message (Messages)
Service Broker consists of a message header and a message body. 、
The message body's data type is varbinary (max), which can contain a maximum of 2GB of any SQL Server data that can be converted to varbinary (max).
Note: Both the message type name and the other Service Broker metadata in the message header use a binary collation. Therefore, names must match exactly, and are case-sensitive and accented.
It is recommended that replication be used to prevent errors.

CREATE message TYPE message_type_name
   [VALIDATION = {  NONE
                   | EMPTY
                   | Well_formed_xml
                   | Valid_xml with SCHEMA COLLECTION
                      Schema_collection_name
                  }]

VALIDATION
Specifies how Service Broker validates the message body of this type of message. If this clause is not specified, the validation defaults to NONE.
NONE
Validation is not performed. The message body can contain any data, or it can be NULL.
EMPTY
The message body must be NULL. For example, a "sucess" type of message that does not need to carry any data.
Well_formed_xml
The message body must contain well-formed XML.
Valid_xml with SCHEMA COLLECTION schema_collection_name
The message body must contain XML that is consistent with the schema in the specified schema collection. Schema_collection_name must be the name of an existing XML schema collection.

--Agreement (contracts)
The ALTER CONTRACT statement used to add or remove message types is not provided in SQL Server 2005, and you must ensure that the creation contract is correct.
The only way to change the list of message types is to delete the contract after the rebuild.
Note: If you do not specify a message type and convention, you will use the default message type and one containing the default message type default convention.

--Queue (queues)
Queues are the only services broker objects that actually store data, so you can specify the filegroup in which the messages are stored.
A queue can be the target of a SELECT statement. However, you can only modify the contents of a queue by using statements that run in a Service Broker session, such as SEND, RECEIVE, and end conversation.
The queue cannot be the target of an INSERT, UPDATE, DELETE, or TRUNCATE statement.
The queue cannot be a temporary object. Therefore, the name of the queue that begins with # is an invalid name.
Set the status to off to create a queue, you can first prepare the infrastructure for the service, and then allow messages to be received in the queue.
STATUS (activation) Specifies whether Service Broker activates the stored procedure. When STATUS = ON, if the number of processes currently running is less than max_queue_readers and the message arrives at a queue faster than the stored procedure receives the message, the queue initiates the stored procedure specified with procedure_name. When STATUS = off, the queue does not activate the stored procedure. If this clause is not specified, the default is on.
Max_queue_readers = Max_readers The maximum number of instances of the activation stored procedure that the specified queue starts at the same time. The value of the max_readers must be a number between 0 and 32767.

Note: The status of the set retention to on queue is set to 0, indicating that it has been received and processed by the application and that the transaction has been committed. And the message is not deleted,
Instead, the copy is kept in the queue. The queues will soon become very large and performance will drop dramatically.
However, it is useful to run a long-running business transaction back by running a compensating transaction.

Each database has only one sys.transmission_queue view, temporarily storing messages.
Scenario: The target is in a different instance of SQL Server, the destination queue is invalid, STATUS = off;
The enable_broker of the database in which the destination queue resides is not set;

--Service (services)
The service is the name of the session endpoint.

Note: When you create a session, the session endpoint of the initiating session does not check the initiator's list of services. Therefore, the initiator's list of services is usually empty.
If there is no agreement from the initiator, you will never know if there is a problem with the list of services of the initiator.

--initiating and ending a session
BEGIN DIALOG conversation @Dialog
From SERVICE [//microsoft.com/manufacturingservice]
To SERVICE '//microsoft.com/inventoryservice '
On CONTRACT [//microsoft.com/inventory/additemcontract]
With encryption = off, LIFETIME = 3600;

The O service parameter is a string of length 256. The target_service_name type is nvarchar (256). Service Broker compares each byte to ensure that it matches the target_service_name string.
In other words, comparisons are case-sensitive and do not take into account the current collation.
LIFETIME = Dialog_lifetime
Specifies the maximum time that the dialog will remain open. For the dialog to complete successfully, the two endpoints must explicitly end the conversation during the lifetime. The value of the dialog_lifetime must be in seconds. The type of the lifetime is int.
If the LIFETIME clause is not specified, the lifetime of the dialog is the maximum value of the int data type.
Encryption
If messages sent and received by this dialog are sent to Microsoft SQL Server Real exception, the message must be encrypted.
The conversation that must be encrypted is a security conversation. If encryption = ON, but the certificate required to support encryption is not configured,
The Service Broker returns an error message for the session. Encryption = Off,
Use encryption if a remote service binding is configured for Target_service_name, otherwise, messages are not encrypted when they are sent.
If this clause is not used, the default value is on.

The database master key refers to the symmetric key used to protect the private key of the certificate and the asymmetric key that exists in the database.
When the master key is created, it is encrypted using the Triple DES algorithm and the user-supplied password.
Create Master key:
CREATE MASTER KEY Encryption by PASSWORD = ' pass.word1 ';

--Send and receive
WAITFOR (
RECEIVE Top (1)
@message_type = Message_type_name,
@message_body = Message_body,
@dialog = Conversation_handle
FROM dbo. Inventoryqueue
), TIMEOUT 2000;

In order to ensure that the parser knows that send and receive are at the beginning of a new command, the command before send or receive must be
The semicolon (;) ends, but the WAITFOR statement is an exception.

If the timeout parameter is omitted, the receive statement in the WAITFOR statement is an exception.

--session security

-To allow access across a database using a simulated security context, the mock database can be represented as "trusted (trustworthy)"
ALTER DATABASE manufacturing SET trustworthy on;

Asymmetric cryptography is used to securely transfer symmetric keys, while symmetric key technology is used for data exchange.

--routing
Service Broker routing is a mapping of services to network addresses, which is where messages are sent to the bottom of the web.

The route of the service can specify the database on the instance
The address of the route is local, which means that Service Broker will look for services that match the name in the SQL Server instance.
When the address of the route is transport, Service Broker attempts to use the name of the services as a route.
For example, if each initiator uses their network address and port as a parameter to the from service, the target needs to include only one address
The transport wildcard route can route the impact to all of the initiators.
CREATE ROUTE Transportroute
With address = ' transport ';

--Balanced load
If there are multiple routes with the same service name in the database, Service Broker will then select a route when the first message is sent in the session.

The application must determine which service starts the session and select a service using the broker instance in the begin DIALOG command.
Service_broker_guid
Specifies the database that hosts the target service. If more than one database hosts a target service instance, you can communicate with a specific database by providing service_broker_guid.
The service_broker_guid type is nvarchar (128). To find the service_broker_guid of your database, run the following query in the database:
SELECT service_broker_guid
From sys.databases
WHERE database_id = db_id ();

Start a conversation with a specific broker instance
The example starts a conversation session and stores the dialog's identifier in the @dialog_handle. Adventure-works.com/expenseclient Service is the initiator of dialogue, and//adventure-works.com/expenses service is the goal of dialogue. The dialog follows the agreed//adventure-works.com/expenses/expensesubmission.
Broker routes messages for this conversation to broker that is identified by the GUID a326e034-d4cf-4e8b-8d98-4d7e1926c904.

DECLARE @dialog_handle uniqueidentifier;
BEGIN DIALOG conversation @dialog_handle
From SERVICE [//adventure-works.com/expenseclient]
To SERVICE '//adventure-works.com/expenses ',
' a326e034-d4cf-4e8b-8d98-4d7e1926c904 '
On CONTRACT [//adventure-works.com/expenses/expensesubmission];

--Forwarding
message_forwarding = {ENABLED | disabled*}
Determines whether messages that are received by this endpoint for services located elsewhere are forwarded.
ENABLED
If a forwarding address is provided, the message is forwarded.
DISABLED
Discard messages for services located in other locations.
Message_forwarding_size = Forward_size
Specifies the maximum amount of storage (in megabytes) to be allocated for the endpoint to be used when storing messages to be forwarded.

ALTER ENDPOINT inventoryendpoint for Service_broker
  (message_forwarding = ENABLED,
  Message_ Forward_size = 50);