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.

Best Practices

The following articles include best practices and points of concern for using 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.

Appendix

Following are the full URLs for the hyperlinked text.

1 An Introduction to SQL Server Service Brokerhttps://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx#sqlsvcbr_topic1

2 Service Broker: Performance and Scalability Techniqueshttps://msdn.microsoft.com/en-us/library/dd576261(v=SQL.100).aspx

3 SQL Server Service Brokerhttps://technet.microsoft.com/en-us/library/bb522893.aspx

4 Async Lifestyle: Manage Your Tasks With Service Brokerhttps://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 2005https://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 2005https://technet.microsoft.com/en-us/library/cc917714.aspx