SQL Server: Building Simple Asynchronous Applications

Course Overview
Hi everyone. My name is Jonathan Kehayias and welcome to my course SQL Server: Building Simple Asynchronous Applications. I'm a principal consultant and trainer at SQLskills. com and have been working with SQL Sever for the last 11 years including using Service Broker with many clients. Most developers interact with SQL Server in a synchronous fashion, sending request to the server and waiting for the results to be returned. However, this is not always ideal and Service Broker provides a flexible and easy to implement way of interacting asynchronously with SQL Server. In this course we'll introduce the basic components required to develop single database asynchronous applications in SQL Server and how to automate processing using queues, services, messages, and contracts in Service Broker. Some of the major topics that we'll cover include: building a Hello World application in Service Broker, the basic architecture and components of Service Broker, building a full application with Service Broker, and some basic trouble shooting scenarios. By the end of this course you'll know how to implement simple asynchronous processing of request in SQL Server using Service Broker. Before beginning the course you should have a basic understanding of Transact-SQL as a language and SQL Server. But no prior knowledge of Service Broker is necessary. From here you should feel comfortable diving into more advanced Service Broker implementations like communicating between instances, addressing scalability problems, and adding high availability to your Service Broker solution. All our which I'll be covering in future courses. In the meantime, I hope you'll enjoy this course and join me on a journey to learn about Service Broker with the SQL Server Building Simple Asynchronous Application course here at Pluralsight.

Introduction
Hi, this is Jonathan Kehayias of SQLskills. com, and I'm recording this SQL Server: Building Simple Asynchronous Applications course for Pluralsight. This is Module 1, the Introduction to the course. So in this module, what we're going to talk about are some of the database application challenges that exist for designing applications with SQL Server and why you might want to use Service Broker to solve certain challenges that exist as a part of the database application design process. We'll also talk about some example usage scenarios for where you might use Service Broker as an alternative to existing methods that you already have implemented in your application design, and then we'll go over, finally, the entire core structure for this course as it will be laid out and taught. Some of the database application challenges that developers have to cope with include having lost data from failed operations or transactions occurring within the database tier, having long running operations that affect the end user experience, having serial execution of Transact-SQL statements inside of a single batch within SQL Server, being able to scale to meet workload requirements, and the asynchronous execution requirements that often accompany end user applications, especially when you have something like a long running operation that would end up affecting the end user experience. And each of these are something that Service Broker might be able to assist a developer with being able to address one or many of the different challenges that are associated with database application design.

"Hello World" with Service Broker
Hi, this is Jonathan Kehayias with SQLskills. com, and I'm recording this SQL Server: Building Simple Asynchronous Applications course for Pluralsight. This is Module 2, "Hello World" with Service Broker. So in this module we're going to start off by taking a look at the different Service Broker components that are required for building an asynchronous application inside of SQL Server, and then we're going to actually go through each of the components and build out a "Hello World' Service Broker example. And with that example, we're going to look at how do we send messages between services? How do we process messages? And then we'll wrap up the module by looking at some warnings about conversations, and dialogs, and making sure that you understand the appropriate way to implement asynchronous applications in SQL Server successfully the first time out. There are four basic components for Service Broker inside of SQL Server that are required for all applications that are going to be doing asynchronous processing. The first component that you need is a message type, and what the message types do is define the types of messages that are going to be used within the application architecture. Then we define a contract, which is going to specify which messages are going to be sent and/or received by the different services within the application. And then we will define a queue, which is a storage area for the data that gets received by each of the services. And queues are implemented as internal tables inside of SQL Server and have a specific way that you would access them to be able to process the messages for each of the services. And then you'll define the services themselves, which are used to send and receive messages that are defined by the contract that the application will utilize.

Basic Architecture and Components
Hi, this is Jonathan Kehayias of www. SQLskills. com, and I'm recording this SQL Server: Building Simple Asynchronous Applications course for Pluralsight. This is Module 3, Basic Architecture and Components. In this module we're going to take a look at more in depth each of the components that we've already used to build our HelloWorld Service Broker example in the previous module. And we'll start off looking at the application message types, and how they can be defined inside of SQL Server, and the different options for defining our message types. Then we'll take a look at our system message types and understand what they are and why we need to handle them to build a robust application inside of SQL Server, our contracts and how we define those, and then we'll wrap up with our queues and our services, and the way that we define each of those to build an asynchronous application in SQL Server on top of Service Broker.

Building a Full Application
Hi, this is Jonathan Kehayias of www. SQLskills. com, and I'm recording this SQL Server: Building Simple Asynchronous Applications course for Pluralsight. This is Module 4, Building a Full Application. In this module, we're going to start off by taking a look at how to build a stored procedure to automate queue processing with internal activation, and then we're going to look further at the system messages that we need to handle as a part of our stored procedure and application design, specifically the error message and the end dialog message. Then we'll talk about testing our queue processing end-to-end before we automate our activation, and then finally we'll look at how we automate processing with internal activation by binding the stored procedure we've created to our queue, and setting it up to execute automatically any time a message is added to the queue.

Basic Troubleshooting
Hi, this is Jonathan Kehayias with www. SQLskills. com, and I'm recording this SQL Server: Building Simple Asynchronous Applications course for Pluralsight. This is Module 5, Basic Troubleshooting. So this module is going to cover how you go about diagnosing and troubleshooting problems with Service Broker implementations inside of the database, and we're going to start off by looking at the transmission queue and its role inside of Service Broker for guaranteeing reliable delivery of messages, and for troubleshooting problems when messages don't arrive the way you expect them to. Then we'll talk about some basic diagnostic questions that you'll want to ask any time that you're having an issue with a Service Broker implementation. And then we'll go through some basic troubleshooting steps to make sure that you understand the steps involved in getting to the root cause of any problems or issues with a Service Broker implementation as soon as possible, and being able to restore the functionality provided by the services as soon as possible. And the last thing we'll talk about are poison messages in Service Broker, and how they affect queue status, and how to deal with poison messages when and if they occur inside of your implementation.