Pro SQL Server 2008 Service Broker Klaus Aschenbrenner Pro SQL Server 2008 Service Broker Copyright © 2008 by Klaus Aschenbrenner All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-999-0 ISBN-10 (pbk): 1-59059-999-3 ISBN-13 (electronic): 978-1-4302-0865-5 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jonathan Gennick Technical Reviewer: Fabio Ferracchiati Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick, Kevin Goff, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Project Manager: Tracy Brown Collins CopyEditor:Liz Welch Associate Production Director: Kari Brooks-Copony Production Editor: Katie Stence Compositor:Patrick Cunningham Proofreader: Lisa Hamilton Indexer: Carol Burbo Artist: April Milne Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide bySpringer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected],or visit http:// www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales—eBook Licensing web page at http://www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall haveany liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com. For Karin.Every day that starts with you turns out to be a great day. Iwill always love you. Contents at a Glance About the Author .................................................................xv About the Technical Reviewer .....................................................xvii Acknowledgments ...............................................................xix Introduction .....................................................................xxi PART 1 ■ ■ ■ The Service Broker Programming Model ■CHAPTER 1 Fundamentals of Message-Based Processing....................3 ■CHAPTER 2 Introducing Service Broker.....................................17 ■CHAPTER 3 Service Broker in Action .......................................33 ■CHAPTER 4 Service Broker Activation......................................73 ■CHAPTER 5 Service Broker with Managed Code ...........................127 ■CHAPTER 6 Locking and Transaction Management ........................163 ■CHAPTER 7 Distributed ServiceBroker Applications .......................239 PART 2 ■ ■ ■ Advanced Service Broker Programming ■CHAPTER 8 Advanced Distributed Service Broker Progamming ............271 ■CHAPTER 9 Service-Oriented Database Architecture ......................305 ■CHAPTER 10 Real-World Application Scenarios .............................351 ■CHAPTER 11 High Availability and Scalability ...............................461 ■CHAPTER 12 Administration ...............................................513 ■INDEX .......................................................................551 v Contents About the Author .................................................................xv About the Technical Reviewer .....................................................xvii Acknowledgments ...............................................................xix Introduction .....................................................................xxi PART 1 ■ ■ ■ The Service Broker Programming Model ■CHAPTER 1 Fundamentals of Message-Based Processing.............3 Message Concepts ...............................................4 Message Anatomy...........................................4 Messaging in Daily Life.......................................5 Why Messaging?.................................................6 Asynchronous Message Processing............................6 Deferred Message Processing.................................7 Fault Tolerance..............................................7 Distributed Systems .........................................8 Messaging Problems..............................................9 Performance................................................9 Queue Reader Management .................................10 Transaction Management....................................10 Message Sequencing and Correlation.........................11 Maintenance...............................................12 Messaging Architectures.........................................12 SOA ......................................................12 SODA.....................................................13 Available Messaging Technologies.................................14 MSMQ ....................................................14 Queued Components........................................15 BizTalk Server .............................................15 XML Web Services..........................................15 WCF ......................................................15 Summary ......................................................16 vii viii ■CONTENTS ■CHAPTER 2 Introducing Service Broker.................................17 Conversations...................................................17 Dialogs....................................................18 Dialog Lifetime.............................................18 Conversation Groups........................................19 Message Sequencing .......................................20 Reliable Delivery ...........................................21 Error Handling..............................................22 Anatomy of a Service ............................................22 Message Types ............................................23 Contracts..................................................24 Queues....................................................25 Service Programs...........................................26 Routes....................................................26 Security........................................................26 Transport Security..........................................27 Dialog Security.............................................27 Message Processing.............................................28 Performance....................................................29 Benefits........................................................31 Summary.......................................................31 ■CHAPTER 3 Service Broker in Action....................................33 Defining Service Broker Applications...............................33 Message Types .................................................34 Contracts.......................................................37 Queue .........................................................41 Service.........................................................43 Sending Messages ..............................................47 Retrieving and Processing Messages ..............................53 Retrieving Messages.............................................54 Processing Messages............................................57 Error Handling ..................................................62 Error Handling in Service Programs...........................62 Poison-Message Handling...................................66 Ending Conversations with Errors.............................69 Summary ......................................................72 ■CONTENTS ix ■CHAPTER 4 Service Broker Activation ..................................73 Activation Basics................................................73 Startup Strategies ..........................................74 When Is Activation Needed? .................................75 Internal Activation...............................................77 Controlling Message Throughput .............................84 Stored Procedure Signing....................................87 Calling a Stored Procedure in Another Database................95 Using a Single Stored Procedure to Process Many Queues.......99 External Activation..............................................103 Using the Event Notification.................................104 Enabling External Activation.................................105 Implementing the External Console Application................107 Activating the External Console Application ...................110 Configuring ExternalActivator ...............................112 Several Activated External Console Applications...............116 Parallel Activation ..............................................119 Troubleshooting Activation.......................................124 When the Activated Stored Procedure Doesn’t Run.............125 When Messages Remain on the Queue.......................125 Summary......................................................126 ■CHAPTER 5 Service Broker with Managed Code ......................127 The Managed Assembly.........................................128 Architecture and Design of the Managed Assembly.................131 Building a Managed Service Broker Client.........................137 Building a Managed Service Program.............................143 Deriving Your Service Class.................................143 Implementing an Entry Point................................143 Implementing Message Type Handling .......................145 Deploying the Assembly....................................145 Registering the Managed Stored Procedure ..................147 Configuring the Service Broker Activation.....................148 Using the Service Program..................................148 APractical Example ............................................151 Summary .....................................................161 x ■CONTENTS ■CHAPTER 6 Locking and Transaction Management...................163 Conversation Groups and Locks..................................163 State Handling.................................................170 GET CONVERSATION GROUP................................171 The Receive Loop with State Handling........................172 State Handling with a Managed Stored Procedure .............177 APractical Example........................................183 Compensation Logic with Service Broker .....................215 Transaction Management .......................................222 Basic Receive Loop........................................222 Measuring Performance....................................223 Batched Commits .........................................225 Cursor-Based Processing...................................228 Set-Based Processing......................................231 Binary Payload............................................235 Summary......................................................238 ■CHAPTER 7 Distributed Service Broker Applications .................239 Communication................................................239 Service Broker Protocols ...................................240 Sending a Message........................................240 Routing .......................................................242 Routing Algorithm .........................................244 Managing Routes..........................................244 Distributed Applications.........................................246 The Application............................................246 Setting Up Routes .........................................247 Establishing a Communication Channel.......................250 Setting Up Security ........................................253 Summary......................................................267 PART 2 ■ ■ ■ Advanced Service Broker Programming ■CHAPTER 8 Advanced Distributed Service Broker Programming ....271 Transport Security..............................................272 LOCAL Route..............................................272 TRANSPORT Route.........................................273 ■CONTENTS xi Dialog Security.................................................275 Service Broker Security Protocol.............................275 Configuration .............................................278 Encryption.....................................................287 Transport Encryption.......................................287 Dialog Encryption..........................................288 Recommendation..........................................289 Transport Protocol..............................................290 Setting Up Tracing.........................................290 The Captured Service Broker Message.......................291 Replaying Service Broker Messages .........................294 Replacing Certificates...........................................294 Transport Security.........................................294 Dialog Security............................................295 Service Listing Manager.........................................296 Exporting a Service Listing..................................297 Importing a Service Listing..................................300 Summary......................................................304 ■CHAPTER 9 Service-Oriented Database Architecture.................305 Service-Oriented Database Architecture...........................305 SOA .....................................................306 Reasons for SODA Architectures.............................307 Requirements for a SODA Service Provider....................308 Data in SODA..................................................308 Outside Data..............................................309 Inside Data ...............................................310 SODA Features in SQL Server 2008...............................311 XML Support..............................................312 Native Web Services.......................................315 SQLCLR..................................................326 QueryNotifications ........................................337 Summary......................................................350 ■CHAPTER 10 Real-World Application Scenarios........................351 Reliable Web Service Requests...................................352 Service Broker Infrastructure................................352 Implementation of the Web Proxy............................357 Using the Web Proxy in a Smart Client........................371