Asynchronous Scale-Out (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
Microsoft SQL Server Service Broker provides native database engine support for messaging and queuing applications, enabling asynchronous processing. This makes it easier for developers to create sophisticated applications that use the built-in engine components to communicate between disparate databases without the need for additional messaging products or expensive two-phase commit protocols. Developers can use Service Broker to build reliable distributed applications.
Application developers can use Service Broker to distribute data workloads across several databases without invoking complex communication and messaging protocols. This can help improve both scale-up and scale-out performance. Using Service Broker can reduce development and test work because it handles communication paths in the context of a conversation. Using Service Broker can also help improve the response time performance of online transactions. For example, front-end databases that support websites can record information and send process-intensive tasks to queues in back-end databases, providing quick response for online users. The complex, time consuming processing takes place asynchronously in the background. Service Broker ensures that all tasks are managed in the context of transactions to assure reliability and technical consistency.
Several of the largest projects in the world have incorporated asynchronous messaging into their solution where applicable. Although there are many asynchronous messaging products available on the market, Service Broker is built into the SQL Server engine and uses the database engine-provided standard backup, restore, and high availability technologies.
In brief, the advantages of using Service Broker include simple and flexible message coordination, asynchronous, guaranteed, transactional, message ordering, scalability, multithreading, and scalable receiving and processing. Using Service Broker requires learning a new technology and the need to make sure that the designers and developers are skilled in using the technology appropriately. Database administrators are often unfamiliar with message-based architectures.
The following articles include best practices and points of concern for using Service Broker:
The article An Introduction to SQL Server Service Broker1 provides a good overview of Service Broker and the scenarios where it can be used.
The article Service Broker: Performance and Scalability Techniques2 includes examples of Service Broker use from a practical deployment scenario viewpoint.
The main SQL Server 2008 R2 Books Online launch page for Service Broker, SQL Server Service Broker,3 includes links to various useful topic areas.
While most of the articles written about Service Broker discuss its capabilities as a messaging infrastructure and as a platform for building reliable database applications, the article Async Lifestyle: Manage Your Tasks With Service Broker4 describes how to use Service Broker to implement a simple batch-scheduling system that can be used to run tasks such as data warehouse maintenance and population, or routine database maintenance.
Some customers have found Bob Beauchemin's blog5 to be a useful site for Service Broker information. One of the more useful posts describes changes in SQL Server 2008 R2 for handling poison messages.
Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
6 is a good white paper covering Service Broker and several other topics. This paper is also useful for SQL Server 2008, even though it was written for SQL Server 2005. The first section following the introduction focuses on Service Broker.
Case Studies and References
Different scale-out techniques have been used by different companies:
Myspace developed and deployed five major architecture changes as they experienced rapid growth. The latest architecture includes several scale-out techniques, including Service Broker. For more information, see Inside MySpace: The Story.7
Please use caution when viewing these documents, and honor the confidentiality set by management for the TechReady 12 content when using these customer examples.
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
Service Broker is an extremely useful technology, and as the case studies show, customers are using it in innovating ways for scaling-out their workloads. However, with these benefits come some considerations. With scale-out, you must manage more objects, and executing backup, restore, and disaster recovery becomes more complex. Designers, developers, and operations staff will also have to be educated about Service Broker, and this requires an understanding of new concepts of conversations, dialogs, messages, and other components.
Determine which part of the database transaction workload is asynchronous, or which part could be modified to benefit from being asynchronous. The benefit comes from design patterns that reduce online transaction path lengths while moving more processing to background servers, which perhaps can be done in parallel and in batches for efficiency.
The article Planning, Implementing, and Administering Scale-out Solutions with SQL Server 20058 points out that setting up Service Broker security can be complex, although it is scriptable. The article includes pointers to some scripts that can be useful.
Following are the full URLs for the hyperlinked text.
1 An Introduction to SQL Server Service Brokerhttp://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx#sqlsvcbr_topic1
2 Service Broker: Performance and Scalability Techniqueshttp://msdn.microsoft.com/en-us/library/dd576261(v=SQL.100).aspx
3 SQL Server Service Brokerhttp://technet.microsoft.com/en-us/library/bb522893.aspx
4 Async Lifestyle: Manage Your Tasks With Service Brokerhttp://technet.microsoft.com/en-us/magazine/2005.05.servicebroker.aspx
5 Bob Beauchemin's Bloghttp://www.sqlskills.com/BLOGS/BOBB/category/Service-Broker.aspx
6 Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc
7 Inside MySpace: The Storyhttp://www.baselinemag.com/c/a/Projects-Networks-and-Storage/Inside-MySpacecom/
8 Planning, Implementing, and Administering Scale-out Solutions with SQL Server 2005http://technet.microsoft.com/en-us/library/cc917714.aspx