SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Configuring Service Broker Architecture


Configuring Service Broker Architecture

Author
Message
WAL
WAL
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 258
Comments posted to this topic are about the item Configuring Service Broker Architecture
venoym
venoym
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3763 Visits: 2082
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
WAL
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 258
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
R.P.Rozema
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10827 Visits: 1774
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64366 Visits: 886
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.
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64366 Visits: 886
Good article, thanks Val.
WAL
WAL
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 258
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
rgarrett
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 117
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
WAL
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 258
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
quackhandle1975
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9685 Visits: 1489
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! :w00tSmile 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! :-D 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:

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search