Configuring Service Broker Architecture

  • WAL

    Mr or Mrs. 500

    Points: 594

    Comments posted to this topic are about the item Configuring Service Broker Architecture

  • venoym

    SSCarpal Tunnel

    Points: 4161

    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.

  • WAL

    Mr or Mrs. 500

    Points: 594

    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.

  • R.P.Rozema

    SSChampion

    Points: 12300

    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.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good article, thanks Val.

  • WAL

    Mr or Mrs. 500

    Points: 594

    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:

  • rgarrett

    SSC Rookie

    Points: 44

    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?

  • WAL

    Mr or Mrs. 500

    Points: 594

    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:

  • quackhandle1975

    SSChampion

    Points: 10963

    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.

    more info here[/url]:

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Jefferson Elias

    Ten Centuries

    Points: 1379

    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

  • WAL

    Mr or Mrs. 500

    Points: 594

    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.

  • Jefferson Elias

    Ten Centuries

    Points: 1379

    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_name from_service_name service_contract_name message_type_name transmission_status

    SampleServiceTarget SampleServiceSource SampleContract SampleMessage Dialog 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 ?

  • WAL

    Mr or Mrs. 500

    Points: 594

    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_name from_service_name service_contract_name message_type_name transmission_status

    SampleServiceTarget SampleServiceSource SampleContract SampleMessage Dialog 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.

  • Jefferson Elias

    Ten Centuries

    Points: 1379

    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 24 total)

You must be logged in to reply to this topic. Login to reply