September 2, 2015 at 10:23 pm
Comments posted to this topic are about the item Configuring Service Broker Architecture
September 3, 2015 at 6:43 am
Very nice article!
Fairly good description of how to get started. As always, there will be variability in your configuration and usages.
Some things to remember:
1. You can enable Service Broker without a Master Key by enabling Trustworthy if the routes are within the same SQL Instance
2. Service Broker defaults to Disabled if the DB is detached or restored. This will cause your DB size to explode due to enqueuing all messages into the Transmission Queue. If you enable Service Broker you have to do it with a ROLLBACK IMMEDIATE and will lose all messages in the Transmission Queue.
3. Trustworthy is also disabled (if using this option) in the same instances that Service Broker is... leading to the same issue restarting it.
4. When Sending it's common place to use a timeout to automatically close conversations (it's an efficiency thing). This can lead to sending a message on a closed conversation if the message is set to be sent within a couple ms of the automatic closing of the conversation. the Service Broker Transmission Conversations dmv (I don't have SQL Server handy to get the actual DMV) has a timeout column that you can bounce against to prevent that.
5. Any error in relation to Service Broker is severity 16 which places any transaction into an uncommitable state and forces a rollback of that transaction. Can be problematic at times.
6. The Receive queue will close automatically on 5 exceptions during processing. You can't control this, any exception (even caught by a TRY...CATCH) adds to that number.
September 5, 2015 at 3:05 am
Thanks for the rating and comment.
Just to make clear:
1.Trustworthy db option is disabled by default. Also, it will be set to OFF after detach/attach db operations. Enabling this property can introduce certain threats. More info on https://msdn.microsoft.com/en-us/library/ms187861.aspx
Service Broker is enabled by default on a newly created database. However, it is disabled on an attached or restored database by default.
2. Service Broker uses a transmission queue as a holding area for messages. Each service-broker enabled database contains a separate transmission queue. The transmission queue is stored in the primary filegroup for the database. The message will remain in the transmission queue until the target has acknowledged receipt of the message. Thus, transmission queue can blow up if messages get stuck in and can not be delivered. More info on https://technet.microsoft.com/en-us/library/ms166030(v=sql.105).aspx
Alter database with rollback immediate option will cause to close all existing db sessions and roll back any pending transactions in the database. It is usually specified because ENABLE_BROKER option requires an exclusive database lock.
3.SQL Server is Secure By Design and By Default.
4.More info on https://msdn.microsoft.com/en-us/library/ms187377.aspx
5.Sql Server XML casting and validation errors are raised with severity 16, and this severity will always put the transaction into an uncommittable state. You can specify xml validation schema on the service-broker message type to ensure that XML payload is valid before sending it on a conversation. More info on https://msdn.microsoft.com/en-us/library/ms187744.aspx
6.If you set POISON_MESSAGE_HANDLING option of a queue to OFF then the queue will not be disabled after five consecutive transaction rollbacks. This allows for a custom poison message handing system to be defined by the application.
September 8, 2015 at 2:35 am
We found Service Broker to be very useful in situations where the number of services is low. However in a larger environment setting up and maintaining the routes is a real hassle. Dynamic Routing (https://technet.microsoft.com/en-us/library/ms166054(v=sql.105).aspx) is supposed to be the solution, but either I am overlooking some important piece of information, or the design for Dynamic Routing is flawed: once signaled a route is found missing, it is impossible to retrieve for which instance a route was found missing. If anyone can provide more information that helps overcoming this issue, Service Broker will get a lot more useful in an enterprise environment.
September 8, 2015 at 2:54 pm
venoym (9/3/2015)
Very nice article!Fairly good description of how to get started. As always, there will be variability in your configuration and usages.
Some things to remember:
1. You can enable Service Broker without a Master Key by enabling Trustworthy if the routes are within the same SQL Instance
2. Service Broker defaults to Disabled if the DB is detached or restored. This will cause your DB size to explode due to enqueuing all messages into the Transmission Queue. If you enable Service Broker you have to do it with a ROLLBACK IMMEDIATE and will lose all messages in the Transmission Queue.
3. Trustworthy is also disabled (if using this option) in the same instances that Service Broker is... leading to the same issue restarting it.
4. When Sending it's common place to use a timeout to automatically close conversations (it's an efficiency thing). This can lead to sending a message on a closed conversation if the message is set to be sent within a couple ms of the automatic closing of the conversation. the Service Broker Transmission Conversations dmv (I don't have SQL Server handy to get the actual DMV) has a timeout column that you can bounce against to prevent that.
5. Any error in relation to Service Broker is severity 16 which places any transaction into an uncommitable state and forces a rollback of that transaction. Can be problematic at times.
6. The Receive queue will close automatically on 5 exceptions during processing. You can't control this, any exception (even caught by a TRY...CATCH) adds to that number.
Thanks for the additional info.
September 8, 2015 at 2:54 pm
Good article, thanks Val.
September 12, 2015 at 8:12 am
Why did you like it? Has it helped you to solve issues?
What was bad in it?
I like critics... don't be shy.:hehe:
September 15, 2015 at 2:57 pm
I am in an organisation that is looking at using the Service Broker to queue up messages for getting them sent out to RabbitMQ using a custom SQL CLR function. I have gone through this tutorial but cannot get it to work.
Everything is created but when I add a row to the Order table, it does not make it to the Queue. What controls getting messages in the Queue to be processed?
October 24, 2015 at 1:19 pm
rgarrett (9/15/2015)
I am in an organisation that is looking at using the Service Broker to queue up messages for getting them sent out to RabbitMQ using a custom SQL CLR function. I have gone through this tutorial but cannot get it to work.Everything is created but when I add a row to the Order table, it does not make it to the Queue. What controls getting messages in the Queue to be processed?
You need to create a conversation and send a message like described in the article. Regarding CLR assembly usage, do you plan to use CLR function to pull data from SQL Server?
I have referenced a book about Service Broker in the article. you can find examples of CLR code there if you read books.:hehe:
November 2, 2015 at 6:54 am
Great article as I've found SB to be one of the most underused and misunderstood SQL Server features (along with Resource Governor) and there's a distinct lack of info on troubleshooting SB issues.
Just to echo Ten Centuries post and point 4 on the conversation timeout, with my last client we found an issue where the application was using SB and the conversations weren't being timed out (it wasn't set, so went to default, which is a billion seconds! :w00t:) The result of this was Service Broker bloat, we could see our main production app db growing by 6GB a day and yet when we were performing a weekly reindex on a 600GB+ db it was completing in under 20 mins. Initially I did think damn those SSDs are good! 😀 Be then we found it was SB causing the issue.
Couldn't change the app code so we had to perform a staggered CONVERSATION CLOSE as if you do it too harshly it can cause issues.
qh
February 10, 2016 at 4:06 am
Hi, thanks for this article.
While running the code you provided, there is a little syntax error at
CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION $(LoginName)
STATE= STARTED AS TCP
( LISTENER_PORT=4022, LISTENER_IP= ALL ) FOR SERVICE_BROKER
( AUTHENTICATION = WINDOWS, ENCRYPTION= REQUIRED, ALGORITHM= RC4 )
This should be
CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION $(LoginName)
STATE= STARTED AS TCP
( LISTENER_PORT=4022, LISTENER_IP= ALL ) FOR SERVICE_BROKER
( AUTHENTICATION = WINDOWS, ENCRYPTION= REQUIRED ALGORITHM RC4 )
Cheers
February 10, 2016 at 5:29 am
Jefferson Elias (2/10/2016)
Hi, thanks for this article.While running the code you provided, there is a little syntax error at
CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION $(LoginName)
STATE= STARTED AS TCP
( LISTENER_PORT=4022, LISTENER_IP= ALL ) FOR SERVICE_BROKER
( AUTHENTICATION = WINDOWS, ENCRYPTION= REQUIRED, ALGORITHM= RC4 )
This should be
CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION $(LoginName)
STATE= STARTED AS TCP
( LISTENER_PORT=4022, LISTENER_IP= ALL ) FOR SERVICE_BROKER
( AUTHENTICATION = WINDOWS, ENCRYPTION= REQUIRED ALGORITHM RC4 )
Cheers
Nice spot! I think '=' sign appeared during this article publishing process. I suggest to use attached scripts for deployment.
Cheers.
February 10, 2016 at 6:21 am
After reading everything and I tried to run from attached scripts, I can't manage to get it work.
I'm trying to run Service Broker on two separate servers. The code to send a message to the destination works enqueues the message, but the message get stuck in transmission queue
select to_service_name,from_service_name,service_contract_name,message_type_name,transmission_status from sys.transmission_queue;
to_service_namefrom_service_nameservice_contract_namemessage_type_nametransmission_status
SampleServiceTargetSampleServiceSourceSampleContractSampleMessageDialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement.
Also tried with "WITH ENCRYPTION = OFF" but still didn't work.
Any clue ?
February 11, 2016 at 10:49 am
Jefferson Elias (2/10/2016)
After reading everything and I tried to run from attached scripts, I can't manage to get it work.I'm trying to run Service Broker on two separate servers. The code to send a message to the destination works enqueues the message, but the message get stuck in transmission queue
select to_service_name,from_service_name,service_contract_name,message_type_name,transmission_status from sys.transmission_queue;
to_service_namefrom_service_nameservice_contract_namemessage_type_nametransmission_status
SampleServiceTargetSampleServiceSourceSampleContractSampleMessageDialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement.
Also tried with "WITH ENCRYPTION = OFF" but still didn't work.
Any clue ?
Hi,
Have you tried to troubleshoot using ssbdiagnose tool? example is in the scripts too. it should work. Are db servers in the same domain?
WAL.
February 12, 2016 at 12:47 am
Hi.
Here is the command I executed for ssbdiagnose
# ------------- Configuration --------------------
$TargetServer = "MyServ"
$TargetDbName = "TestServiceBroker"
$SSBSourceServiceName = "SampleServiceSource"
$SSBTargetServiceName = "SampleServiceTarget"
$SSBContract = "SampleContract"
$SSBDiagnoseLogDest = ".\ssbdiagnose.log"
# ------------- End of Configuration -------------
$TargetHostAddressObj = [System.Net.Dns]::GetHostAddresses("$TargetServer")
if($TargetHostAddressObj -eq $null -or $TargetHostAddressObj.Count -lt 1) {
throw "Target Server with name [$TargetServer] not found on network"
}
$TargetHostIP = $TargetHostAddressObj[0].IPAddressToString
#ssbdiagnose.exe -u username -p password -s "$TargetHostIP" -d "$TargetDbName" CONFIGURATION FROM SERVICE "$SSBSourceServiceName" TO SERVICE "$SSBTargetServiceName" ON CONTRACT "$SSBContract"
# Report on configuration issues
ssbdiagnose.exe -s "$TargetHostIP" -d "$TargetDbName" CONFIGURATION FROM SERVICE "$SSBSourceServiceName" TO SERVICE "$SSBTargetServiceName" ON CONTRACT "$SSBContract" > "$SSBDiagnoseLogDest"
and here is its output
Microsoft SQL Server 11.0.2100.60
Service Broker Diagnostic Utility
D 29912 192.168.96.90 TestServiceBroker Service SampleServiceTarget was not found
D 29973 192.168.96.90 TestServiceBroker No valid certificate with a private key was found for user dbo
D 29977 192.168.96.90 TestServiceBroker The user dbo from database TestServiceBroker on 192.168.96.90 cannot be mapped into this database using certificates
D 29971 192.168.96.90 TestServiceBroker Dialog security requires a remote service binding for service SampleServiceTarget
D 29933 192.168.96.90 TestServiceBroker The routing address TCP://192.168.98.93:4022 for service SampleServiceTarget does not match any of the IP addresses for 192.168.96.90
5 Errors, 0 Warnings
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply