Posts

SQLSaturday #527 – Columbus 2016

UpSearch at SQLSaturday #527 - Columbus 2016

UpSearch at SQLSaturday #527

UpSearch proudly supported PASS SQLSaturday #527 – Columbus 2016. The event was held July 16, 2016 at Quest Conference Center, 8405 Pulsar Place, Columbus, Ohio, 43082.

Presentations

The UpSearch team supported SQLSaturday #527 – Columbus 2016 by presenting:

Inside the Black Box – Making Sense of Service Broker by Colleen Morrow.

SQLSaturday #527 - Columbus 2016

Intro to Azure SQL Databases by Michael Fal.

SQLSaturday #527 - Columbus 2016

Honorable Mention

In her speaking debut at SQLSaturday Cleveland, Evelyn Maxwell presented Improving Your PowerPoint Skills. If you missed this popular session, you were in luck, because Evelyn presented at SQLSaturday Columbus.

Evelyn Maxwell at SQLSaturday #527 - Columbus 2016

Thank you to Columbus SQL Server Users Group for hosting SQLSaturday #527 – Columbus 2016. If you live in the Greater Columbus, OH area, consider getting involved with Columbus SQL Server Users Group (t).

 

UpSearch

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. To learn more about PASS SQL Saturday, visit http://www.sqlsaturday.com/about.aspx.


About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.


SQL Server Service Broker Error Handling

Service Broker is not a black box.  SQL Server Service Broker Error Handling is part of a blog series designed to support your messaging needs and make sense Service Broker.

SQL Server Service Broker Error Handling

SQL Server Service Broker Error HandlingOriginally published on ColleenMorrow.com.

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:

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. And how exactly you handle errors will depend on several factors:

  • Should the message processing be retried? If the error was a deadlock, then retrying is appropriate, because it should eventually succeed.
  • 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.
  • 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.
  • 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.

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Sending and Receiving

Service Broker is not a black box.  SQL Server Service Broker Sending and Receiving is part of a blog series designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Sending and Receiving

SQL Server Service Broker Sending and ReceivingOriginally published on ColleenMorrow.com.

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:

In this installment, we discuss sending and receiving messages.

Sending a Message

If you’ve been following along in this series, you’ll have created the necessary components of the service architecture, namely the message types, contract, queues, and services. You will also have started a conversation between your initiator and target services. You’re finally ready to start sending and receiving messages in your Service Broker app. Whoohoo! Let’s get to it!

To send a message in Service Broker, we use the SEND command. But first, we need to retrieve our conversation handle. Remember, all messages need to be sent as part of a conversation. If this message is unrelated to any previous messages, we might choose to start a new conversation. However, if this message *is* related to previous messages, we’ll want to reuse an existing conversation so that our messages are processed in the right order. More on that later.

For the sake of simplicity here, we’ll start a new conversation and send a message on that conversation handle.

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @TaxFormMessage NVARCHAR(1000);

BEGIN TRANSACTION;

--open a dialog between the initiator service and target service

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//SBDemo/Taxes/TaxpayerService]

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

ON CONTRACT [//SBDemo/Taxes/TaxContract]

WITH ENCRYPTION = OFF;

--build the message

SELECT @TaxFormMessage = 
N'<Form1040EZ>

<SSANumber>695256908</SSANumber>

<LastName>Erickson</LastName>

<FirstName>Gail</FirstName>

<MiddleName>A</MiddleName>

<BirthDate>1952-09-27</BirthDate>

<FilingStatus>M</FilingStatus>

<Wages>66662.00</Wages>

<FederalIncomeTax>12888.00</FederalIncomeTax>

<StateIncomeTax>2522.00</StateIncomeTax>

<SocialSecurityTax>5523.00</SocialSecurityTax>

</Form1040EZ>

';

--send the message using the id of the conversation started above

--specify the Request message, which can only be sent by the conversation initiator

SEND ON CONVERSATION @InitDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/TaxFormMessage]

(@TaxFormMessage);

COMMIT TRANSACTION;

So what happens when we run this? When we begin a dialog, SQL Server creates a conversation endpoint in the initiator database. We can view that endpoint by querying the sys.conversation_endpoints table. It doesn’t try to communicate with the target service yet, though, so at this point SQL Server doesn’t know whether that target is online or whether it exists at all. In fact, if we look at sys.conversation_endpoints immediately after beginning the dialog, we’ll see the state_desc of the endpoint as “STARTED_OUTBOUND”. This means that the conversation has been started, but no messages have been sent yet.

When we issue the SEND command, what happens depends on how our architecture is configured. So far, our initiator and target services are both in the same database, so when we send a message SQL Server attempts to insert it directly into the target service’s queue. If for some reason SQL Server can’t write to the target queue (maybe it’s been disabled), then the message gets written to the sys.transmission_queue system table. SQL Server will continue to try to deliver the message until it succeeds. Once the message is successfully delivered to the target queue, the message is deleted from the transmission queue.

Receiving Messages

Let’s assume our message was successfully delivered. In fact, let’s verify that it was. Remember that Service Broker queues are really just hidden tables, and we can select from them. So if we want to know whether our message reached the target queue, all we need to do is query the queue.

SELECT * FROM IRSQueue

However, note that SELECTing from the queue doesn’t remove messages from that queue. To pop a message out of the Service Broker queue we must RECEIVE it.

RECEIVE TOP(1) * FROM IRSQueue

Now, if we were to issue the statement above, the message and all of its related information will be returned to our results tab in SSMS. That’s not very useful, though, is it. That’s why, typically, we receive messages into variables, so that we can perform additional processing.

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg xml;

DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@RecvReqDlgHandle = conversation_handle,

@RecvReqMsg = cast(message_body as xml),

@RecvReqMsgName = message_type_name

FROM IRSQueue

IF @RecvReqMsgName =

N'//SBDemo/Taxes/TaxFormMessage'

BEGIN

DECLARE

@SSANumber                        varchar(9),

@LastName                        varchar(50),

@FirstName                        varchar(50),

@MiddleName                        varchar(50),

@BirthDate                        date,

@FilingStatus                char(2),

@Wages                                Decimal(12,2),

@FederalIncomeTax        Decimal(12,2),

@StateIncomeTax                Decimal(12,2),

@SocialSecurityTax        Decimal(12,2),

@Refund                                decimal(12,2)

select

@SSANumber                        = c.value(N'(./SSANumber)[1]', N'varchar(9)') ,

@LastName                        = c.value(N'(./LastName)[1]', N'varchar(50)') ,

@FirstName                        = c.value(N'(./FirstName)[1]', N'varchar(50)') ,

@MiddleName                        = c.value(N'(./MiddleName)[1]', N'varchar(50)') ,

@BirthDate                        = c.value(N'(./BirthDate)[1]', N'date') ,

@FilingStatus                = c.value(N'(./FilingStatus)[1]', N'char(2)') ,

@Wages                                = c.value(N'(./Wages)[1]', N'Decimal(12,2)') ,

@FederalIncomeTax        = c.value(N'(./FederalIncomeTax)[1]', N'Decimal(12,2)') ,

@StateIncomeTax                = c.value(N'(./StateIncomeTax)[1]', N'Decimal(12,2)') ,

@SocialSecurityTax        = c.value(N'(./SocialSecurityTax)[1]', N'Decimal(12,2)')

from @RecvReqMsg.nodes('Form1040EZ') T(c)

EXEC CalculateRefund @FilingStatus, @Wages, @FederalIncomeTax, @Refund OUTPUT

DECLARE @ReplyMsg NVARCHAR(100);

IF @Refund < 10000

BEGIN

SELECT @ReplyMsg =

N'<Refund>;'+cast(@Refund as varchar)+'</Refund>;';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/TreasuryCheckMessage]

(@ReplyMsg);

END

ELSE

BEGIN

SELECT @ReplyMsg =

N'<AuditNotice>Refund exceeds $10000: '+cast(@Refund as varchar)+'</AuditNotice>';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/AuditNotificationMessage]

(@ReplyMsg);

END

END CONVERSATION @RecvReqDlgHandle;

END

COMMIT TRANSACTION;

GO

There are a few points I’d like to highlight in the code above. First, when we receive the message, we don’t just receive the body of the message, we’re also receiving the conversation_handle and the message_type_name. This is typical of Service Broker apps. Grabbing the conversation_handle allows us to send a reply message on the same conversation, which we actually do later in the code. Getting the message type allows us to add logic to process different message types in different ways, even though they’re coming in on the same queue.

Secondly, note that we need to cast the message_body as an xml variable. The message_body is stored as a varbinary(max), so we need to convert it to xml before we can shred it.

Finally, the RECEIVE statement is inside BEGIN and COMMIT transaction statements, so we’re receiving the message as part of the transaction. Therefore, if we hit an error, maybe in the CalculateRefund procedure, the entire transaction will roll back and the message will go back into the queue. And the next time we run the code, this message will be received and processed again. Depending on what caused the error, we may run into the same problem. Can you see where this might be an issue? More on that soon.

Conclusion

I hope you’re enjoying this series of posts as much as I am. We’ve only scratched the surface, so much more to come! If there’s a specific area of Service Broker you want to see addressed, please feel free to leave a suggestion in the comments. Next I will continue with SQL Server Service Broker Error Handling.

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Conversation Architecture

Service Broker is not a black box.  SQL Server Service Broker Conversation Architecture is part of a blog series designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Conversation Architecture

SQL Server Service Broker Conversation ArchitectureOriginally published on ColleenMorrow.com.

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:

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 in SQL Server Service Broker Sending and Receiving.

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Service Architecture

Service Broker is not a black box. This SQL Server Service Broker Service Architecture is part of a blog series designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Service Architecture

SQL Server Service Broker Service ArchitectureOriginally published on ColleenMorrow.com.

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 posts:

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 in SQL Server Service Broker Conversation Architecture!

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Introduction

Service Broker is not a black box.  This SQL Server Service Broker Introduction is part of a blog series designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Introduction

SQL Server Service Broker IntroductionOriginally published on ColleenMorrow.com.

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.

UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQLSaturday #526 – Rochester 2016

UpSearch at SQLSaturday #526 - Rochester 2016

UpSearch at SQLSaturday #526

UpSearch proudly supported PASS SQLSaturday #526 – Rochester 2016. The event was held May 14, 2016 at Rochester Institute of Technology, 1 Lomb Memorial Drive, Rochester, New York, 14623.

Presentations

The UpSearch team supported SQLSaturday #526 – Rochester 2016 by presenting:

Inside the Black Box – Making Sense of Service Broker by Colleen Morrow.

SQLSaturday #526 - Rochester 2016

 

Thank you to Rochester PASS for hosting SQLSaturday #526 – Rochester 2016. If you live in the Greater Rochester, NY area, consider getting involved with Rochester PASS.

 

UpSearch

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. To learn more about PASS SQL Saturday, visit http://www.sqlsaturday.com/about.aspx.


About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.


Inside the Black Box – Making Sense of SQL Server Service Broker

 

About the Presentation


SQL Server Service Broker is often seen as a black box. Messages go in, and hopefully they come out on the other side. But when they don’t, DBAs typically don’t know where to look. Trying to figure out what’s wrong quickly turns into a guessing game.

Inside the Black Box - Making Sense of SQL Server Service BrokerIt’s not that complicated once you understand how it all works.

Service Broker is an asynchronous messaging technology built into SQL Server that allows you to scale out your application, replicate data or perform ETL, all while guaranteeing messages are delivered and processed in the right order. In this session, we’ll discuss the basics of the Service Broker architecture, its components, and deployment options. We will discuss troubleshooting tips and performance best practices to help you deploy a SQL Service Broker solution in your own environment.

Session Level: Beginner

Download the Presentation

Inside the Black Box - Making Sense of SQL Server Service Broker

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Service Broker?

If you'd like to learn more about how UpSearch can support your SQL Server service broker initiative, visit SQL Server Service Broker or contact us today.

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Basics Part Two

Service Broker is not a black box. SQL Server Service Broker Basics Part Two was designed to support your messaging needs and make sense of Service Broker.

Continued from SQL Server Service Broker Basics Part One

SQL Server Service Broker Basics Part Two

Originally published on SQLBlog.com.SQL Server Service Broker Basics Part Two

In the previous post, I introduced SQL Server Service Broker Basics Part One.  In this post, I’d like to cover some of the “plumbing” – the components that allow communication between different servers running Service Broker.

Endpoints. There needs to be a channel for the communications coming in and out of the server, and in the IP world that channel exists in the form of a port. You define the port to be used by defining an Endpoint in the master database.

CREATE ENDPOINT IntEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

Routes. To get from one place to another Service Broker routes need to be defined. You’ll need a route to the remote server defined in the database where your Service Broker application is running, and also one to the local server, and the latter needs to be defined in the msdb database. Defining a remote destination in your application database places the route information in sys.routes, but Service Broker always looks in msdb.sys.routes for any incoming messages to determine where they go.

USE AdventureWorks
GO

CREATE ROUTE DMZRoute 
AUTHORIZATION dbo 
WITH 
     SERVICE_NAME = N'//DMZSite/Sync/IntService',
     ADDRESS = N'TCP://SQLTBWS:4023'
GO

USE msdb;
GO

CREATE ROUTE IntRoute 
AUTHORIZATION dbo 
WITH 
     SERVICE_NAME = N'//IntSite/Sync/IntService',
     ADDRESS = N'LOCAL'
GO

One thing I hadn’t addressed in my last post was message security. Service Broker allows you to encrypt all messages, preventing network sniffers from discovering the data being sent. To enable this I created certificates at each site, and created a database user without a login to send and receive messages. Here’s the code I used to create the local user:

USE AdventureWorks
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'<enter REALLY secure password string here>';
GO

CREATE USER IntUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE IntCert 
     AUTHORIZATION IntUser
     WITH SUBJECT = 'Int Certificate',
          EXPIRY_DATE = N'12/31/2012';

BACKUP CERTIFICATE IntCert
  TO FILE = N'E:\Certs\IntCert.cer';
GO

I did the same thing at the destination site (called DMZSite), and to allow the DMZUser to send messages to my site I’ll create a local user from the certificate created at that site.

CREATE USER DMZUser WITHOUT LOGIN;

CREATE CERTIFICATE DMZCert
   AUTHORIZATION DMZUser
   FROM FILE = N'E:\Certs\DMZCert.cer';
GO

Remote Service Binding. Once the users are established and secure, the last component required is the Remote Service Binding. This binds a remote Service Broker service to our local one, defining the security credentials to be used in the conversations.

CREATE REMOTE SERVICE BINDING [DMZBinding] 
  AUTHORIZATION dbo 
  TO SERVICE N'//DMZSite/Sync/IntService'
  WITH USER = [DMZUser]
GO

Finally, we’ll grant the SEND permission to the DMZUser to allow the remote service to send messages to our site.

GRANT SEND
      ON SERVICE::[//IntSite/Sync/IntService]
      TO DMZUser;
GO

As I mentioned before, this set of objects make up the “plumbing” that allow separate instances or servers to communicate with each other. In my next post we’ll talk about the automated activation process and walk through the steps of message handling.

Reprinted with author’s permission from SQLBlog.com.

>> Back to SQL Server Service Broker Basics Part One <<

 

UpSearch

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is an UpSearch Alum and Microsoft SQL Server MVP.  For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data's usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Service Broker Basics Part One

Service Broker is not a black box. SQL Server Service Broker Basics Part One was designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Basics Part One

Originally published on SQLBlog.com.SQL Server Service Broker Basics Part One

I’m currently implementing a SQL Server Service Broker solution at a client site, and it’s been an interesting challenge, because there’s not a lot of information out there to help guide you through the process. Here I’d like to walk you through the basics.

Message Types. Service Broker sends messages asynchronously from one database to another. You can set it up to send messages between databases on a single server, or between SQL Server instances on a Windows server, or between different physical servers, whether or not they’re in the same domain. Essentially Service Broker works at the database level, the rest is handled through routing, which I’ll address in another post.

The important thing to remember is that Service Broker sends and receives messages, and then your applications (or stored procedures) handle those messages in some way. It handles them asynchronously, so the sending side doesn’t have to wait for the receiving side to acknowledge the message, and it handles them sequentially, so the messages will always arrive in the order in which they’ve been sent.

Many of the examples you’ll see use message types like “REQUESTMESSAGE” and “REPLYMESSAGE”. To me this is a disservice, because it doesn’t help you see the different ways you can use Service Broker to solve your business problems. At my client site the message types indicate the content of the message, so the receiving side can use the type to determine the action to take when the message is received. Service Broker has a built-in acknowledgement process, so you don’t need to specifically acknowledge a message, unless the application needs it. As long as the communication channels are open, the message will be delivered.

CREATE MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML
GO

Contracts. Once you’ve defined the types of messages that can be sent, you need to define how they’ll be delivered. Contracts define what message types are allowed to be sent, and in which direction. This means that Service Broker is secure in that it won’t process any messages types not defined in a contract, so rogue processes that attempt to try a type of SQL Injection attack against Service Broker will fail.

CREATE CONTRACT [//AWSync/Sync/IntContract]
	AUTHORIZATION dbo
	( [//AWSync/Sync/HumanResourcesEmployee] SENT BY ANY,
	  [//AWSync/Sync/PersonContact] SENT BY ANY,
      [//AWSync/Sync/PurchasingVendor] SENT BY ANY )
GO

Queues. Once the contract is defined, you can define the queue on which the messages are sent and received. The queue also defines (if you want) an automated process that will handle the messages it receives. In your Transact-SQL code you retrieve messages from the queue in the same way you read data from a table – in fact, the queue behaves just like a table in your database.

CREATE QUEUE IntQueue
   WITH
   STATUS = ON,
   RETENTION = OFF
GO

Services. The service is the glue which assigns the contract to the queue. It performs the work of actually sending the messages on the queue to their destination and receiving the messages coming from other senders.

CREATE SERVICE [//IntSite/Sync/IntService]
AUTHORIZATION IntUser
ON QUEUE IntQueue
([//AWSync/Sync/IntContract])
GO

Conversations. In its simplest form, the last thing we need is to send the message. We do that via a conversation, which is referred to in Service Broker as a DIALOG CONVERSATION or simply a DIALOG. You specify the source and destination service name, and a conversation handle (a GUID) is returned, then you SEND ON CONVERSATION using that conversation handle. The message body is usually in an XML form, and for security purposes should be encrypted.

BEGIN DIALOG @InitDlgHandle
   FROM SERVICE [//IntSite/Sync/IntService]
   TO SERVICE N'//ExtSite/Sync/IntService'
   ON CONTRACT [//AWSync/Sync/IntContract]
   WITH
	   ENCRYPTION = ON;

SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
   (@ChangeMsg);

Finally, you need to be able to receive the messages. Like I mentioned earlier, reading from a queue is like reading from a table, but there are some additional features in Transact-SQL to facilitate message handling. Specifically, there’s a special form of the WAITFOR command which will wait for either the arrival of a message, or timeout after a specified number of milliseconds.

WAITFOR (
	RECEIVE TOP(1)
		@ch = conversation_handle,
		@service_name = service_name,
		@service_contract_name = service_contract_name,
		@messagetypename = message_type_name,
		@messagebody = CAST(message_body AS XML)
	FROM ExtQueue
), TIMEOUT 60000

With these components you can set up messaging within a single instance of SQL Server. In my next post I’ll discuss the additional plumbing required to communicate between separate instances.

Reprinted with author’s permission from SQLBlog.com.

>> Continue Reading SQL Server Service Broker Basics Part Two <<

 

UpSearch

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is an UpSearch Alum and Microsoft SQL Server MVP.  For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data's usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.