Original post by Colleen M. Morrow found here.

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

See all posts in the series here.


In this installment, we discuss networking and routing components for Service Broker. So far, we’ve deployed our Service Broker solution inside a single database. The real magic of using a messaging service is communicating and synchronizing data across distributed systems. In the next two posts we’ll learn about the different components that make that magic happen in Service Broker. We’ll begin with the networking infrastructure.

Endpoints

Service Broker uses TCP/IP to communicate with other Service Broker services on the network. Therefore, the first step in distributing a Service Broker solution is to create a TCP endpoint in the SQL Server instance. It will be used to listen for Service Broker communication over the network. We do that using the CREATE ENDPOINT command. When we create the endpoint, we need to specify a unique port number, in this case 4023. Make sure that port number isn’t being used by any other services on this server. We also specify the authentication type. Service Broker endpoints support Windows authentication (used in the example below) or certificate-based authentication.

For our taxes example code, we’ve decided to move the IRS-related components to separate database on another server. So we’ll need to create an endpoint on each instance:

--Create an endpoint on our Taxpayer instance
CREATE ENDPOINT TaxpayerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4023 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

--Create an endpoint on our IRS instance
CREATE ENDPOINT IRSEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

Note that you can only create one Service Broker endpoint per instance. Regardless of how many Service Broker solutions you’re deploying in that instance, all their network communications will go through that one endpoint. Also, if you’re using a firewall on the server, you’ll want to ensure you configure it to allow incoming connections on that port.

Routes

One of the first questions you might ask when distributing Service Broker solutions is “how does SQL Server know where the other service is?” That’s where routes come in. We use routes to tell SQL Server the server name and endpoint port of a remote service on the network.

For example, in our taxes solution, we would create a route in the Taxpayer database that points to the IRS service, and a route in the IRS database that points to the Taxpayer service:

--create in taxpayer database
CREATE ROUTE IRSRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/IRSService',
ADDRESS = N'TCP://LAB-SB2:4022'; --the IRS endpoint
GO

--create in IRS database
CREATE ROUTE TaxpayerRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/TaxpayerService',
ADDRESS = N'TCP://LAB-SB1:4023'; --the Taxpayer endpoint
GO

Each database contains a system table, sys.routes. When a conversation is started, SQL Server looks through the sys.routes table in the database where the conversation started, for a route with the service name (and Service Broker GUID, if used) specified in the conversation.

If a Service Broker identifier is specified in the conversation, SQL Server will look for a route with an exact service name and Service Broker identifier match. In the case of an exact match not being found, or if no Service Broker identifier is specified in the conversation, it will look for a match based on the service name alone. When SQL Server finds several matches based on the service name that have different Service Broker identifiers, it will arbitrarily pick one of the identifiers and then match the routes that use that identifier.

Note that incoming messages also go through a routing process, except their routing table is in msdb. When we configure Service Broker for network communications, we need to create a local route for messages received from the remote service:

--create on taxpayer instance
USE msdb
GO
CREATE ROUTE TaxpayerRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/TaxpayerService',
ADDRESS = N'LOCAL'
GO

--create on IRS instance
USE msdb
GO
CREATE ROUTE IRSRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/IRSService',
ADDRESS = N'LOCAL'
GO

Conclusion

We’ve got our networking and routing configured for Service Broker communications. We’re almost ready to send messages between our two servers! There’s just one more thing we need to do: configure security. More on that next time.

Original post by Colleen M. Morrow found here

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

See all posts in the series here.


In this installment, we discuss error handling in Service Broker applications as well as a noir-sounding concept called the poison message.

Poison Messages

At the end of last week’s post I raised a particular scenario. When receiving a message from the queue while inside a transaction, if we experience an error and the transaction rolls back, the message gets put back in the queue. And the next time we read from the queue, we’ll receive that same message again. So, what happens if we meet up with that same error again? And again? And again? Are you picking up what I’m layin’ down?

This type of situation, a message that can never be processed successfully, is known as a poison message. The name kind of makes it sound like there’s a problem with the message itself. And there might be. Perhaps the message format is wrong for what the receiving code was expecting. But maybe the problem is with the receiving code itself. Regardless of what causes the poison message, it has to be dealt with.

SQL Server has a built-in mechanism for handling poison messages. If a transaction that receives a message rolls back 5 times, SQL Server will disable the queue. So that means that all processing that depends on that queue will cease. Nice, huh? Because of this, it behooves you to make sure you include proper error handling in your message processing code.

How you handle errors will depend on several factors:

1.Should the message processing be retried?

If the error was a deadlock, then retrying is appropriate, because it should eventually succeed.

2. Should the whole transaction be rolled back or just part of it?

You can use savepoints to rollback all logic except the receive, for instance.

3. Should the conversation be ended?

In the case of a poison message, it’s common to commit the receipt of the message (to remove it from the queue) and end the conversation with an error. This notifies the sender service that something went wrong on the receiver side. However, if the sender doesn’t care about the success or failure of the receiver’s processing, you may choose to log the error and commit the transaction without ending the conversation.

4.What logic does the sender need to perform if the receiver gets an error?

This is where things can get sticky. It’s relatively easy to code error handling on the receiver side. But what if there’s logic on the sender side that needs to be undone to complete the “rollback”? Now we need to include error handling in the receiving code that notifies the sending service of the error and we need receiving code on the sender service that will handle the error and perform any necessary logic.

Best Practices

Asynchronous applications can get pretty complex. I know you were probably hoping for some example code on proper error handling. But the thing is, so much is dependent on how your specific implementation has to function. What I can share are some best practices. Here are some of my recommendations, in no particular order:

Map it out before writing one bit of code.

If you don’t have a clear picture of how your application logic flows, you simply won’t code an efficient and robust app. This should be a no-brainer, but even I’ve fallen victim to the urge to start coding before I’ve mapped out a clear picture of the logical flow. You’ve heard “measure twice, cut once”, well this is the developer’s version of that.


Do validation on the sending side.

Does your receiver assume the message will be in xml format? Make sure you’re casting your message as xml at the sender. Does your receiver require a specific format? Consider using a specific xml schema. Performing as much validation as possible on the sender side not only helps prevent a lot of receiver errors, it also lessens the workload of the receiving service, which means better message processing performance.


Keep it simple

The less complex your receiving code, the less opportunity there is for errors. If this is a one-way application, you might even consider something as simple as receiving messages from the queue and inserting them into another table to await subsequent processing. But even with something this simple…

Always include TRY/CATCH blocks in your receiving procedure.

Conclusion

I’d love to hear from others who are using Service Broker. How do you handle errors in your application? Any tips or tricks to share? Leave your thoughts in the comments.

Original post by Colleen M. Morrow found here.

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

See all posts in the series here.


In this installment, we introduce Service Broker’s conversation architecture.

Conversations

Conversations are part of everyday life. We have them with our partner, our coworkers, our friends, even total strangers. They can be short:

“Is anyone sitting here?”

“No.”

Or they can be very, very long. But they all have some things in common. One participant starts the dialog, and they involve at least 2 people. Conversations are bi-directional, and messages are received in the order in which they are sent.

Like real-life conversations, Service Broker conversations are a reliable-bidirectional stream of messages exchanged between two participating services. A Service Broker conversation can be short-lived, a simple exchange of 2 messages, or it can span days, weeks, even years. There are two key elements of a Service Broker conversation that are important to note, however. The first is that messages are guaranteed to be delivered in order, and only once. Service Broker uses sequencing and acknowledgement mechanisms to ensure this. The second key is that conversations are persistent. Persistent across network interruptions. Across server restarts. In fact, conversations persist until they are explicitly ended.

In the world of Service Broker, you’ll sometimes see the term “conversation” used. Sometimes it’s “dialog”. Sometimes it’s even “dialog conversation”. Although “conversation” and “dialog” are distinct concepts in the greater world of messaging services, in the context of Service Broker they are interchangeable.

Initiators and Targets

Last week I introduced the Service Broker contract. Here’s the syntax again:

CREATE CONTRACT [//SBDemo/Taxes/TaxContract]

(

[//SBDemo/Taxes/TaxFormMessage] SENT BY INITIATOR,

[//SBDemo/Taxes/TreasuryCheckMessage] SENT BY TARGET,

[//SBDemo/Taxes/AuditNotificationMessage] SENT BY TARGET

);

GO

You may have noticed something about the syntax, specifically the references to INITIATOR and TARGET. In a Service Broker conversation, the initiator is the service that begins a dialog. The target is the service that accepts the dialog started by the initiator. It can be easy to take the roles of target and initiator and apply them to specific servers or instances. However, it’s important to understand that these roles are specific to a conversation, and may change for other conversations. Think about it this way, I can start a conversation with you in the morning. In that conversation I’m the initiator and you’re the target. But later in the day, you might approach me and start a conversation. In that conversation, you’re the initiator.

The same is true for Service Broker. Which service acts as the initiator and which is the target will depend on how you design your solution. It may well be that a service on ServerA always initiates conversations with a service on ServerB. But you may deploy a solution that works in a more bi-directional manner. Therefore you need to keep this in mind when defining contracts and assigning message types to each role.

Starting a dialog

Before we can send a message between services, we need to create a dialog. Creating a dialog establishes the two participants in the dialog, what contract will be used (and therefore what message types are acceptable), and whether encryption will be used for extra security. We create a dialog using the BEGIN DIALOG command.

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//SBDemo/Taxes/TaxpayerService]

TO SERVICE N'//SBDemo/Taxes/IRSService'

ON CONTRACT [//SBDemo/Taxes/TaxContract]

WITH ENCRYPTION = OFF;

There are two keys to the CREATE DIALOG command that are important. The first is that it requires a UNIQUEIDENTIFIER variable. When a dialog is created, it’s assigned a GUID called a conversation handle. That conversation handle is then used to tell SQL Server which messages are related as part of a single conversation. So we use that variable to grab the conversation handle. The other part of the command worth noting is how each service is referenced. The FROM service (initiator) is always going to be in the current database, however the TO service (target) may be in the current database or it might reside in a separate instance altogether. For that reason, we specify the TO service as a string literal.

Conclusion

This has been a basic introduction to the conversation architecture. There are more complex aspects of conversations, like grouping and creating timers, which we’ll cover in future posts. Next time, however, we’ll walk through sending and receiving messages through Service Broker, as well as how to process incoming messages automatically.

Original post by Colleen M. Morrow found here.

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queuing technology built into the SQL Server Database Engine.

Previous post:

See all posts in the series here.


In this installment, we introduce the basic Service Broker service architecture components.

Taxes

Before I jump into the technical details of the Service Broker architecture, I think it helps to have a real-world analogy of what Service Broker is and does. In the last installment, I used the example of ordering something from Amazon.com. This time, I’d like to use an analogy that’s somewhat timely: taxes.

Each year, we fill out that 1040 or 1040EZ form and we send it to the Internal Revenue Service. Maybe we eFile, maybe we mail it in, it doesn’t matter. That form is received by the IRS and goes into a queue, awaiting review. At some point, days, maybe weeks later, our tax return is processed. If all goes well, our return is approved and the IRS cuts us a check. That is a Service Broker application.

Message Type

The first Service Broker components we define in a new application are the message types. The message type defines name and format of messages that will be exchanged between services. When we create a message type, we have the option of also applying validation, basically saying the message must adhere to a specific format. That validation format may be well-formed XML, XML of a specific schema, an empty message, or we can say no validation at all, in which case the message content could be anything. In our tax example we had 2 message types: the tax return form we submit and the check we get back. Each of these has a well-defined format with specific fields it must contain.

CREATE MESSAGE TYPE
[//SBDemo/Taxes/TaxFormMessage]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE
[//SBDemo/Taxes/TreasuryCheckMessage]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE
[//SBDemo/Taxes/AuditNotificationMessage]
VALIDATION = WELL_FORMED_XML;
GO

Contract

Once the message types have been defined, the next component we need to create is the contract. A Service Broker contract specifies which message types allowed in a conversation and which participant can send which message type. In our tax example, the taxpayer sends the 1040 message type and the IRS sends the treasury check. The IRS, however, would never send the taxpayer a completed 1040 form and a taxpayer would never send a treasury check. Why is this important? By defining what message types can be sent by each participant in a Service Broker app, we’re helping the receiving participant identify when an unauthorized message is received, thereby making our Service Broker app more secure.

Note that a contract can specify more than one message type for any participant. For example, the IRS can also send an audit notice. And the taxpayer can also send other forms or, unfortunately, a personal check. The same holds true for a Service Broker contract.

CREATE CONTRACT [//SBDemo/Taxes/TaxContract]
(
[//SBDemo/Taxes/TaxFormMessage] SENT BY INITIATOR,
[//SBDemo/Taxes/TreasuryCheckMessage] SENT BY TARGET,
[//SBDemo/Taxes/AuditNotificationMessage] SENT BY TARGET
);
GO

So, message types and contracts are pretty straightforward, right? The next 2 components sometimes cause a little confusion. Let’s start with queues.

Queue

A Service Broker queue is, at its essence, a hidden table in SQL Server that stores messages until they’re processed. Each message in the queue is a row in that hidden table. But the cool thing about this hidden table is that it’s not totally hidden. You can SELECT from it, but you can’t perform any DML operations on it. Like any other “real” table in the database, the queue is included in transactions, logging, database backups, database mirroring, etc., just like a “real” table. Each participant in a Service Broker application needs to have a queue to store received messages. In our tax analogy, I like to picture the queue as a cheap plastic inbox sitting on some sad desk in the IRS offices (complete with dreary florescent lighting, ala “Joe Versus the Volcano”). Our 1040 form will stay in that inbox until Tom Hanks processes it.

CREATE QUEUE TaxpayerQueue;
CREATE QUEUE IRSQueue;

Service

A Service Broker service is an addressable endpoint for conversations that bundles up a specific contract and queue. Huh? When we send our tax return form in, we don’t send it to a specific inbox on a desk, right? We send it to the Internal Revenue Service. And sending our return information to the Internal Revenue Service implies the adherence to the contract that was laid out earlier (we send in a specific form, they send us a check, etc.). And when we send that form to the IRS, the form automatically gets placed in that inbox (queue). Similarly, in a Service Broker application, we don’t send a message to a queue, we send it to a service. By sending a message to a specific service, we agree to use the message types defined in the contract on that service and SQL Server automatically places our message into the queue associated with that service.

CREATE SERVICE
[//SBDemo/Taxes/TaxpayerService]
ON QUEUE TaxpayerQueue
([//SBDemo/Taxes/TaxContract]);
GO
CREATE SERVICE
[//SBDemo/Taxes/IRSService]
ON QUEUE IRSQueue
([//SBDemo/Taxes/TaxContract]);
GO

Conclusion

That is a very basic introduction into the Service Broker architecture. Once we’ve created our message types, contract, queues, and services, we’re ready to start sending and receiving messages. More on that next time

Original post by Colleen M. Morrow found here

SQL Server Service Broker is a messaging technology that’s baked right into the SQL Server engine.  It was first introduced in SQL Server 2005, and if you ask me, is one of the most under-utilized, under-appreciated features in SQL Server.

What’s so great about Service Broker?

So why am I such a big fan of Service Broker and if it’s so great, why isn’t everybody using it? Let me start by telling you why I’m such a fan.

Asynchronous

The biggest benefit of Service Broker, or probably any messaging technology, is that is decouples long running processing from the client application. 

A great example of what I mean by this is Amazon.com.  When you place an order at Amazon, a whole series of backend processes are initiated.  Your payment is processed. Inventory is verified and updated. Fulfillment and shipping is initiated, etc.  All of which ultimately end with a box arriving on your doorstep.  But the website doesn’t wait for all of that stuff to finish before it thanks you for your order.  Your order information is added to a queue and the page returns.  All that other stuff is handled asynchronously.  And that’s what Service Broker lets you do in your database.

Guaranteed message delivery

Most of the time in a Service Broker application, you’ll be sending messages between servers.  What happens if the network link between those servers goes down?  Well, if you’re relying on synchronous connections or linked servers, you’d be in trouble.  But with Service Broker, messages are queued until the communication link is back up and they are automatically sent then.  Guaranteed.


Guaranteed message sequencing

Similar to guaranteeing all messages will be delivered, Service Broker uses internal locking and sequencing mechanisms to also guarantee that all messages will be processed in the order in which they were sent.

Durability

Service broker messages and conversations are persistent across server restarts.

Easier application development

All of the perks I mentioned above are built right into Service Broker, which means you don’t need to worry about any of that when you develop your code.

Any Edition

Service Broker is available in any edition of SQL Server.  Now how many features can you say that about?

Wow, Service Broker sounds awesome!  Why isn’t everybody using it?

While there are some pretty cool benefits of using Service Broker, it hasn’t really seen widespread adoption, and I think there are a couple reasons for that.  The first reason is that many people don’t know where they could use messaging technology like Service Broker.  So here are some ideas to get you started.

Event Notifications

If you’ve been to my blog much, you’ll know I’m kind of into auditing.  Event notifications are one of the ways I’ve accomplished SQL Server auditing in the past, and event notifications use Service Broker.

Asynchronous triggers

Any time you implement a trigger, you should make sure it is as lightweight as possible.  Many times I’ve seen developers and DBAs try to cram too much work into a trigger and the application suffers as a result.  If there is logic in your trigger that doesn’t need to be performed synchronously, as part of the current transaction, consider using Service Broker to offload some of that processing to an asynchronous process.

Data warehouse ETL

Whether it’s in conjunction with triggers or with technology like Change Data Capture (CDC), Service Broker is a great way to send data changes to your data warehouse.

Distributed server-side processing for client applications

I mentioned Amazon as an example of this earlier, but this isn’t confined to order entry systems.  Think about what back-end processing you have in your environment

The other problem with Service Broker is that it’s not always easy to understand and implement.  And it can be pretty easy to mess up.  I liken it to a black box of sorts:  messages go in and we hope they come out on the other side.  But when they don’t, we don’t always know where to look.

In this series of blog posts, I’ll walk through the Service Broker architecture, how Service Broker works, how to troubleshoot when things go wrong (and don’t worry, they will), best practices, and more.  Next time, we’ll begin with basic Service Broker components and the role that each one plays.

Lastest Stories

DevOps and the Spiritual Awakening

Growing up, my dad would often encourage me to focus on the small and simple things in life. It was his hope that, with this knowledge, I would accomplish great things. By playing, working, and socializing, the small things we learn create patterns of behavior. We are creatures of habit and the more we repeat […]

Read Story

The Personal DevOps Conversion

Read more about my own conversion to the DevOps model here. You hear the word “DevOps” thrown around quite a lot. It has become a buzzword which is unfortunate because the term holds significant meaning. The level of potential reduced risk coupled with developer and administrator empowerment is astounding. When a DevOps methodology has been […]

Read Story

The Business DevOps Conversion

Personal changes and mental shifts aren’t the only changes that need to happen in order to convert to an IT DevOps Model. There are serious business adjustments that need to be made as part of this process. Admittedly, this process starts out overwhelming (the change curve is real), but you will get there, I promise. […]

Read Story

Fortified Data makes Inc.’s 5000 Most Inspiring Companies 2018

We are excited to make Inc.’s 5000 most inspiring companies for 2018 with 405% growth over the last three years. To us this showcases how our model of results-driven managed services can result in real success. Fortified Data’s team approaches managed services as a partnership, pairing a strong methodology with a commitment to transparent reporting. […]

Read Story

SQL Server Service Broker—Security

There are 2 types of security in Service Broker: dialog and transport. Dialog security establishes a secure, authenticated connection between Service Broker Services or dialog endpoints.

Read Story

SQL Server Service Broker—Internal Activation

The other way to automate this stored procedure is by attaching it to the queue itself so that it Service Broker directly executes the procedure in a process called activation. With activation Service Broker starts an application whenever there is work to do (e.g. when messages are in the queue).

Read Story