Cloud Migration Experiences
In the cloud, experience definitely matters. So, we wrote Cloud Migration Experiences to highlight some of what we learned after migrating numerous clients to the cloud, including our own ISV. If you have questions about migrating to the cloud, we can help.
Cloud Migration Experiences
Something we often hear is “We looked at cloud platforms, but they are too complicated and cost too much.” That’s a fairly common situation. Yet, sticking with physical infrastructure can end up costing you significantly in the long run.
The predictability of any of your systems has a huge impact on when the the cloud makes sense. But, you really wouldn’t know how to move beyond the complexity or cost barriers without experience.
Evyware’s story is one of many about an organization enabled to leverage the cloud after working with UpSearch. Here’s what Evyware’s David Poeschl had to say:
“When it was time for our ISV to move beyond open source, we turned to UpSearch to migrate to the cloud and Microsoft SQL Server from MySQL. They complemented our team extremely well, provided structured guidance and expert SQL Server and Azure know-how. The migration went so well we engaged UpSearch to provide 24/7 monitoring and dedicated support for our SQL Server / Azure environment.”
Real World Experiences
Our team has developed deep practical know how and firsthand experiences, which include:
- Convert business requirements & existing SQL Server infrastructure into an Azure topology
- VMWare and Hyper-V planning with a focus on SQL Server
- Deploy SQL Server to Azure Virtual Machines or Amazon Web Service’s EC2
- Plan and implement High Availability & Disaster Recovery strategies for complete cloud environments and hybrid scenarios
- Migrate existing SQL Server databases to SQL Server on Azure VM with minimal downtime
- Monitor and manage SQL Server in the cloud and virtualized environments
- Automate SQL Server deployments to public and private clouds with Powershell
- Deploy database solutions to Azure SQL Database
- Amazon Web Service’s Relational Database Service for SQL Server
- Deploy and performance tune SQL Server in VMWare environments
Conclusion
The cloud is a viable option to reduce risk, costs and time to deploy. Regardless of private, public or hybrid cloud, a subtle mindset change is required to unlock the value of digital infrastructure. Consider engaging a capacity planning expert to get concrete numbers sized to fit your unique needs.
This blog series continues with case study from one of our clients. We enabled them to expand globally after a successful cloud migration. If you have questions about migrating to the cloud, we can help.
SQL Server Health Check Series
This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>
</ol>
About UpSearch






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.
Cloud Migration Benefits
The cloud is the hot topic these days. But are the benefits really worth it? We wrote Cloud Migration Benefits to share what we learned after migrating numerous clients to the cloud, including our own ISV. When the time is right for your organization to move to the cloud, we know first hand you can lower total cost of ownership.
Cloud Migration Benefits
What are the benefits of Cloud Migration?


Let’s get right to the point. You want to know why you should move to the cloud. Its really quite simple – to reduce costs. Let’s not make this any harder than it needs to be. You have a a fiduciary responsibility to get it done – for less.
The cloud is cost-effective because you will only pay for what you actually use, with no long-term contracts and little to no initial investment. That is a profound shift from physical infrastructure.
There is Always a But
We’ve grown to understand that if you are not saving money by using the cloud, you’re doing it wrong. You probably either picked a system that’s not right for the cloud or didn’t build to capacity enough. The right cloud service fit to your needs will ensure mission critical data remains highly available, optimized, and most importantly secure.
The cloud can be a little tricky. A subtle mindset change is required to unlock the cloud’s value. As stated previously, the secret lies in understanding your actual capacity needs. Think what to move rather than how to move. The more predictable the system the more likely reduced costs are possible. A capacity planning expert can help you get concrete numbers sized to fit your unique needs.
UpSearch enables WebSystem3 to expand globally after successful cloud migration
Benefits Beyond Reduced Costs
The cloud offers many benefits in addition to cost savings, including;
- Cost-Effective – You pay only for what you use, with no long-term contracts and little to no initial investment
- Secure – Robust infrastructure keeps customer data secure and protects against threats
- Scalable – Optimizes IT assets based on demand, ensuring the ideal elasticity of your cloud
- Flexible – Supports a broad selection of operating systems, programming languages, frameworks, tools, databases and devices
- Faster deployment– Integrated tools and pre-built templates reduce deployment times from days to hours and increase the productivity of your IT professionals
In summary, the cloud has become a viable option to reduce risk, costs and time to deploy. Regardless of private, public or hybrid cloud, a subtle mindset change is required to unlock the value of digital infrastructure. Consider engaging a capacity planning expert to get concrete numbers sized to fit your unique needs.
This blog series continues with a highlight of real world experiences. At the conclusion of this series, you will have a better understanding of how your organization will benefit from Cloud Migration.
SQL Server Health Check Series
This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>
</ol>
About UpSearch






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
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:
- SQL Server Service Broker Introduction
- SQL Server Service Broker Service Architecture
- SQL Server Service Broker Conversation Architecture
- SQL Server Service Broker Sending and Receiving
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.
SQL Server Service Broker Series
About the Author




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/.
SQL Server Health Check Series
This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>
</ol>
About UpSearch






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
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:
- SQL Server Service Broker Introduction
- SQL Server Service Broker Service Architecture
- SQL Server Service Broker Conversation Architecture
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 &lt; 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.
SQL Server Service Broker Series
About the Author




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/.
SQL Server Health Check Series
This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>
</ol>
About UpSearch






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
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.
SQL Server Service Broker Series
About the Author




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/.
SQL Server Health Check Series
This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>
</ol>
About UpSearch






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.
About UpSearch
UpSearch is where IT leaders, engineers, and administrators turn to engage with peers, keep up with best practices, and get the insights and guidance they need to protect sensitive data.
We are Data Estate Advisors for 1000s of SQL Servers at court systems, public utilities, healthcare enterprises, and other essential service providers nationwide.