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

Cloud Migration ExperiencesPreviously, we explored Cloud Migration Benefits.  Next, let’s talk about where the rubber meets the road – experience.

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.

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.

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?


Cloud Migration BenefitsPreviously, we shared a high-level overview in Cloud Migration Introduction.  We introduced the three cloud hosting services, noted the subtle mindset change needed to unlock the cloud ‘s benefits and suggested an easy pilot project.

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;

  1. Cost-Effective – You pay only for what you use, with no long-term contracts and little to no initial investment
  2. Secure – Robust infrastructure keeps customer data secure and protects against threats
  3. Scalable – Optimizes IT assets based on demand, ensuring the ideal elasticity of your cloud
  4. Flexible – Supports a broad selection of operating systems, programming languages, frameworks, tools, databases and devices
  5. 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.

 

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.

Cloud Migration Introduction

The cloud.  Everybody’s talking about it, but what does it really mean and what does it take to get there?  This Cloud Migration Introduction and blog series will help you determine when moving to the cloud is right for your organization.

Cloud Migration Introduction

 

What is the Cloud?


Cloud Migration Introduction

Let’s start at the beginning. The cloud is hosting services over the Internet. Cloud migration is the process of moving an organization’s applications and databases from an onsite environment to the cloud or transitioning from one cloud platform to another. Cloud hosting services fall into three (3) categories: public, private and hybrid.

  • A cloud is called a “public cloud” when the services are rendered over a network that is open for public use. Technically there may be little or no difference between public and private cloud architecture, however, security consideration may be substantially different.
  • Private cloud is cloud infrastructure operated solely for a single organization, whether managed internally or by a third-party, and hosted either internally or externally. Undertaking a private cloud project requires a significant level and degree of engagement to virtualize the business environment, and requires the organization to reevaluate decisions about existing resources. When done right, it can improve business, but every step in the project raises security issues that must be addressed to prevent serious vulnerabilities.
  • Hybrid cloud is a composition of two or more clouds (private, community or public) that remain distinct entities but are bound together, offering the benefits of multiple deployment models. Hybrid cloud can also mean the ability to connect collocation, managed and/or dedicated services with cloud resources. Gartner defines a hybrid cloud service as a cloud computing service that is composed of some combination of private, public and community cloud services, from different service providers.

Begin with the End in Mind

Now that we’ve taken care of the cloud migration introduction, let’s shift to what matters most.  How do you ensure that your mission critical data will remain highly available, optimized, and most importantly, secure in the cloud?  The process begins with a subtle mindset change.

To leverage digital infrastructure, select the right cloud solution for your organization and reduce costs, architectural changes are often required. Simply stated, there is a different way of thinking between physical and digital infrastructure. You are close once you start asking, “How can we change our current architecture to reduce costs even further?”

Let’s be clear, the cloud is not right for every situation. But when it does make sense, total costs of ownership can be reduced with digital infrastructure. This is particularly true when capacity can be predicted. The question that often unlocks cost savings associated with the cloud is not how do we migrate to the cloud, but rather what do we migrate to the cloud. Based upon our experience, every organization has predictable systems.

UpSearch enables WebSystem3 to expand globally after successful cloud migration

Consider a Pilot Project

An easy pilot project is to identify a predictable system, maybe at end of hardware life, and work with a cloud specialist for one-to-one mapping and proof of concept.  The result will be actual capacity required to actual cost. You should know online pricing calculators are not concrete numbers sized to fit your needs.

You will typically need the expertise of a capacity planning expert with deep practical expertise migrating database platforms to the cloud. They will assess your current SQL Server environment along with your business needs to design a cloud architecture that works for you; whether that means a hybrid environment or a fully-hosted cloud solution.

We have verified Microsoft SQL Server’s total cost of ownership can be reduced with digital infrastructure, and so can you.

Conclusion

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 benefits and real world experiences. At the conclusion of this series, you will have a better understanding of how your organization will benefit from a Cloud Migration.

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 Health Check Webinar

Do you have SQL Server problems and don’t know where to turn for help? Register for UpSearch’s SQL Server Health Check webinar.  The webinar and related blog post series were designed to help you decide if a SQL Server audit is right for your organization.

You’re Invited to a SQL Server Health Check Webinar

 

SQL Server Health Check Webinar by UpSearch

In our last blog post, we described the final step of our Health Check process.  If you want more information about UpSearch’s SQL Server Health Check, you might consider watching a recorded webinar.

One of the most common questions technology leaders and experienced database professionals ask is how a SQL Server Health Check benefits their organization. The webinar was designed with this question in mind.  You will learn:

  1. If a SQL Server Audit is right for your organization
  2. How our proven process corrects specific problems and improves security and performance
  3. What you can expect after we analyze your environment
  4. Live Q&A with members of our team and Microsoft Certified Solutions Experts

View Webinar Recording

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

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 Health Check Step 3: Report Findings & Recommendations

Do you have SQL Server problems and don’t know where to turn for help? This blog series was designed to help you decide if a SQL Server audit is right for your organization. This article, SQL Server Health Check Step 3: Report Findings & Recommendations, is about the report we present after analyzing your environment.

SQL Server Health Check Step 3: Report Findings & Recommendations

SQL Server Health Check Step 3 Present Findings by UpSearch

In our last blog post, we talked about analyzing results during the second step of our Health Check. The third and final step describes what you can expect when we present our findings and recommendations.

After collecting key metrics and analyzing your environment, our team compiles and presents a comprehensive report that includes:

  • Executive Summary
  • Current Environment ConfigurationSQL Server Health Check Step 3
  • SQL Server Performance Overview
  • Query and Index Tuning Information

The report will provide a detailed description of your current environment, as well as actionable recommendations.  The “to do” list is designed to correct specific problems, improve overall security and performance in these areas:

  • Configuration
  • Performance
  • High Availability and Disaster Recovery
  • Security
  • Additional Concerns

In summary, we provide you with extensive research and practical recommendations customized for your organization.  As we present our finding and recommendations, we make certain to answer any questions and explain the “to do” list.  We can fix the problems or work with your team to make sure your SQL Server environment is stable, performs as expected and the problems fixed.

Up next will be an invitation to our SQL Server Health Check webinar.  We trust you enjoyed the blog series and have a better understanding of how your organization will benefit from UpSearch’s SQL Server Health Check.

 

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s SQL Server Health Check is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.
  1. SQL Server Health Check Introduction
  2. SQL Server Health Check Benefits
  3. SQL Server Health Check Process - Step 1
  4. SQL Server Health Check Process - Step 2
  5. SQL Server Health Check Process - Step 3
  6. SQL Server Health Check Webinar

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.