/Kilimanjaro_Trunk/FastDataPush/Scripts/2 - Initiator setup.sql
SQL | 409 lines | 278 code | 50 blank | 81 comment | 0 complexity | 50e194abd6bb4d6bb4a48a00f46fbe13 MD5 | raw file
1-------------------------------------------------------------------- 2-- Script for fast data push sample. 3-- 4-- This file is part of the Microsoft SQL Server Code Samples. 5-- Copyright (C) Microsoft Corporation. All Rights reserved. 6-- This source code is intended only as a supplement to Microsoft 7-- Development Tools and/or on-line documentation. See these other 8-- materials for detailed information regarding Microsoft code samples. 9-- 10-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 11-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 12-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A 13-- PARTICULAR PURPOSE. 14-------------------------------------------------------------------- 15 16 17---------------------------------------------------- 18-- Initiator setup for fast data push. 19-- Before running, customize the configuration-dependent 20-- routing to the target service. 21---------------------------------------------------- 22 23USE data_push_database; 24GO 25 26-- The data push procedure: send messages to target. 27CREATE PROCEDURE usp_data_push 28AS 29BEGIN 30 SET NOCOUNT ON; 31 32 -- Get initiator parameters. 33 DECLARE @message_quantity BIGINT; 34 DECLARE @message_size INT; 35 DECLARE @number_initiator_transactions INT; 36 DECLARE @initiator_transaction_delay CHAR(12); 37 DECLARE @number_dialogs INT; 38 DECLARE @dialog_recycle_max_messages BIGINT; 39 SET @message_quantity = (SELECT message_quantity FROM data_push_parameters); 40 SET @message_size = (SELECT message_size FROM data_push_parameters); 41 SET @number_initiator_transactions = (SELECT number_initiator_transactions FROM data_push_parameters); 42 SET @initiator_transaction_delay = (SELECT initiator_transaction_delay FROM data_push_parameters); 43 SET @number_dialogs = (SELECT number_dialogs FROM data_push_parameters); 44 SET @dialog_recycle_max_messages = (SELECT dialog_recycle_max_messages FROM data_push_parameters); 45 46 -- Create a message payload of the requested size. 47 DECLARE @payload VARCHAR(MAX); 48 DECLARE @char VARCHAR(MAX); 49 SET @char = '0'; 50 SELECT @payload = REPLICATE(@char, @message_size); 51 52 -- Loop controls. 53 DECLARE @messages_per_transaction BIGINT; 54 DECLARE @messages_per_dialog_transaction BIGINT; 55 DECLARE @transaction_counter INT; 56 DECLARE @message_counter BIGINT; 57 58 -- Compute messages to send per dialog per transaction: 59 -- @message_quantity / (@number_initiator_transactions x @number_dialogs) 60 -- Note that integer arithmetic may result in "remainder" messages that will not 61 -- be sent. 62 SET @messages_per_transaction = @message_quantity / @number_initiator_transactions; 63 SET @messages_per_dialog_transaction = @messages_per_transaction / @number_dialogs; 64 65 -- Error variables. 66 DECLARE @error_number INT; 67 DECLARE @error_message VARCHAR(4000); 68 69 -- Show start time. 70 SELECT GETDATE() AS 'Start sending'; 71 72 -- Create a table containing requested number of dialogs. 73 DECLARE @dialogs TABLE (idx INT, handle UNIQUEIDENTIFIER, recycle_counter BIGINT); 74 DECLARE @idx INT; 75 DECLARE @handle UNIQUEIDENTIFIER; 76 DECLARE @recycle_counter BIGINT; 77 SET @idx = 0; 78 WHILE @idx < @number_dialogs 79 BEGIN 80 BEGIN DIALOG CONVERSATION @handle 81 FROM SERVICE initiator_service 82 TO SERVICE 'target_service' 83 ON CONTRACT data_push_contract 84 WITH ENCRYPTION = OFF; 85 INSERT INTO @dialogs (idx, handle, recycle_counter) VALUES (@idx, @handle, 0); 86 SET @idx = @idx + 1; 87 END 88 89 -- Loop through transactions. 90 SET @transaction_counter = 0; 91 WHILE @transaction_counter < @number_initiator_transactions 92 BEGIN 93 BEGIN TRANSACTION; 94 95 -- Loop through dialogs. 96 SET @idx = 0; 97 WHILE @idx < @number_dialogs 98 BEGIN 99 -- Send a batch of messages for dialog. 100 SET @handle = (SELECT handle FROM @dialogs WHERE idx = @idx); 101 SET @recycle_counter = (SELECT recycle_counter FROM @dialogs WHERE idx = @idx); 102 SET @message_counter = 0; 103 WHILE @message_counter < @messages_per_dialog_transaction 104 BEGIN 105 -- Time to recycle dialog? 106 IF @dialog_recycle_max_messages <> -1 AND 107 @recycle_counter = @dialog_recycle_max_messages 108 BEGIN 109 -- Inform target to end dialog. 110 SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream; 111 112 -- Replace the current dialog. 113 BEGIN DIALOG CONVERSATION @handle 114 FROM SERVICE initiator_service 115 TO SERVICE 'target_service' 116 ON CONTRACT data_push_contract 117 WITH ENCRYPTION = OFF; 118 UPDATE @dialogs SET handle = @handle WHERE idx = @idx; 119 SET @recycle_counter = 0; 120 END 121 122 -- Send a message. 123 BEGIN TRY 124 BEGIN 125 SEND ON CONVERSATION @handle MESSAGE TYPE data_push_message (@payload); 126 END 127 IF @dialog_recycle_max_messages <> -1 128 BEGIN 129 SET @recycle_counter = @recycle_counter + 1; 130 END 131 SET @message_counter = @message_counter + 1; 132 END TRY 133 BEGIN CATCH 134 SET @error_number = ERROR_NUMBER(); 135 SET @error_message = ERROR_MESSAGE(); 136 137 -- Dialog is faulty? 138 DECLARE @dialog_error INT; 139 SET @dialog_error = 1; 140 DECLARE @dialog_state VARCHAR(2); 141 SET @dialog_state = (SELECT state FROM sys.conversation_endpoints 142 WHERE conversation_handle = @handle); 143 IF @@ROWCOUNT = 1 144 BEGIN 145 -- Good dialog is starting or conversing. 146 IF @dialog_state = 'SO' OR @dialog_state = 'CO' 147 BEGIN 148 SET @dialog_error = 0; 149 END 150 END 151 IF @dialog_error = 1 152 BEGIN 153 -- Record the error. 154 INSERT INTO initiator_processing_errors VALUES(@handle, @error_number, 155 @error_message, NULL, NULL, NULL, NULL, 0); 156 157 -- Replace dialog and continue sending. 158 BEGIN DIALOG CONVERSATION @handle 159 FROM SERVICE initiator_service 160 TO SERVICE 'target_service' 161 ON CONTRACT data_push_contract 162 WITH ENCRYPTION = OFF; 163 UPDATE @dialogs SET handle = @handle WHERE idx = @idx; 164 SET @recycle_counter = 0; 165 END 166 ELSE 167 BEGIN 168 -- Record the error and return error. 169 INSERT INTO initiator_processing_errors VALUES(@handle, @error_number, 170 @error_message, NULL, NULL, NULL, NULL, 0); 171 RETURN 1; 172 END 173 END CATCH 174 END 175 UPDATE @dialogs SET recycle_counter = @recycle_counter WHERE idx = @idx; 176 SET @idx = @idx + 1; 177 END 178 COMMIT; 179 SET @transaction_counter = @transaction_counter + 1; 180 181 -- Wait for next transaction. 182 IF @transaction_counter < @number_initiator_transactions 183 BEGIN 184 WAITFOR DELAY @initiator_transaction_delay; 185 END 186 END 187 188 -- Gracefully end dialogs by informing target. 189 BEGIN TRANSACTION; 190 SET @idx = 0; 191 WHILE @idx < @number_dialogs 192 BEGIN 193 SET @handle = (SELECT handle FROM @dialogs WHERE idx = @idx); 194 BEGIN 195 SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream; 196 END 197 SET @idx = @idx + 1; 198 END 199 COMMIT; 200 201 -- Show end time. 202 SELECT GETDATE() AS 'End sending'; 203 204 RETURN 0; 205END; 206GO 207 208-- Resends all pending messages in sys.transmission_queue 209-- belonging to an old conversation on a new conversation. 210CREATE PROCEDURE usp_resend_pending (@old_handle UNIQUEIDENTIFIER) 211AS 212BEGIN 213 SET NOCOUNT ON; 214 215 DECLARE @message_type_name SYSNAME; 216 DECLARE @message_body VARCHAR(MAX); 217 218 -- Get a new dialog. 219 DECLARE @handle UNIQUEIDENTIFIER; 220 BEGIN DIALOG CONVERSATION @handle 221 FROM SERVICE initiator_service 222 TO SERVICE 'target_service' 223 ON CONTRACT data_push_contract 224 WITH ENCRYPTION = OFF; 225 226 -- Declare a cursor to iterate over all the pending messages. 227 -- It is important to keep the message order and to keep the original message type. 228 DECLARE cursor_pending CURSOR LOCAL FORWARD_ONLY READ_ONLY 229 FOR SELECT message_type_name, message_body 230 FROM sys.transmission_queue 231 WHERE conversation_handle = @old_handle 232 ORDER BY message_sequence_number; 233 OPEN cursorPending; 234 235 FETCH NEXT FROM cursor_pending INTO @message_type_name, @message_body; 236 WHILE (@@FETCH_STATUS = 0) 237 BEGIN 238 -- Resend the message on the new conversation 239 SEND ON CONVERSATION @handle MESSAGE TYPE @message_type_name (@message_body); 240 241 FETCH NEXT FROM cursor_pending INTO @message_type_name, @message_body; 242 END 243 CLOSE cursor_pending; 244 DEALLOCATE cursor_pending; 245 246 -- Signal end of stream to target. 247 SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream; 248END; 249GO 250 251-- Activated store proc for the initiator to receive messages. 252-- Dialogs are gracefully ended by the target after receiving 253-- an end_of_stream message from the initiator; the end dialog 254-- message is then processed here. This method is recommended 255-- to avoid "fire and forget" message loss. One message per 256-- invocation is OK here for expected low-volume load. 257CREATE PROCEDURE initiator_queue_activated_procedure 258AS 259BEGIN 260 SET NOCOUNT ON; 261 262 DECLARE @conversation_handle UNIQUEIDENTIFIER, 263 @message_type_name SYSNAME, 264 @message_body VARCHAR(MAX); 265 266 -- Error variables. 267 DECLARE @error_number INT; 268 DECLARE @error_message VARCHAR(4000); 269 DECLARE @error_severity INT; 270 DECLARE @error_state INT; 271 DECLARE @error_procedure SYSNAME; 272 DECLARE @error_line INT; 273 274 BEGIN TRY 275 BEGIN TRANSACTION; 276 277 -- Wait 5 seconds for a message. 278 WAITFOR ( 279 RECEIVE TOP(1) 280 @conversation_handle = conversation_handle, 281 @message_type_name = message_type_name, 282 @message_body = message_body 283 FROM initiator_queue), TIMEOUT 5000; 284 285 IF @@ROWCOUNT = 1 286 BEGIN 287 IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' 288 BEGIN 289 -- Target is ending dialog normally. 290 END CONVERSATION @conversation_handle; 291 END 292 ELSE IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' 293 BEGIN 294 -- Record the error. 295 WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb) 296 SELECT 297 @error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'), 298 @error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)'); 299 INSERT INTO initiator_processing_errors VALUES(@conversation_handle, @error_number, 300 @error_message, NULL, NULL, NULL, NULL, 0); 301 302 -- Can messages be resent? 303 IF (@error_number IN (-8489, -8462, -9719, -28052)) 304 BEGIN 305 -- Resend the messages on a new dialog. 306 EXEC usp_resend_pending @conversation_handle; 307 END 308 ELSE 309 BEGIN 310 -- Save the messages in a side table to be processed later. 311 INSERT INTO unsent_messages 312 SELECT message_type_name, message_body FROM sys.transmission_queue 313 WHERE conversation_handle = @conversation_handle; 314 END 315 316 -- End the conversation. 317 END CONVERSATION @conversation_handle; 318 END 319 END 320 COMMIT; 321 END TRY 322 BEGIN CATCH 323 SET @error_number = ERROR_NUMBER(); 324 SET @error_message = ERROR_MESSAGE(); 325 SET @error_severity = ERROR_SEVERITY(); 326 SET @error_state = ERROR_STATE(); 327 SET @error_procedure = ERROR_PROCEDURE(); 328 SET @error_line = ERROR_LINE(); 329 330 IF XACT_STATE() = -1 331 BEGIN 332 -- The transaction is doomed. Only rollback possible. 333 -- Note: 5 consecutive rollbacks will disable the queue! 334 ROLLBACK TRANSACTION; 335 336 -- Record the error. 337 BEGIN TRANSACTION; 338 INSERT INTO initiator_processing_errors VALUES(NULL, @error_number, @error_message, 339 @error_severity, @error_state, @error_procedure, @error_line, 1); 340 COMMIT; 341 END 342 ELSE IF XACT_STATE() = 1 343 BEGIN 344 -- Record error and commit transaction. 345 INSERT INTO initiator_processing_errors VALUES(NULL, @error_number, @error_message, 346 @error_severity, @error_state, @error_procedure, @error_line, 0); 347 COMMIT; 348 END 349 END CATCH 350END; 351GO 352 353-- Create the initiator queue with activated procedure. 354CREATE QUEUE initiator_queue 355 WITH ACTIVATION ( 356 STATUS = ON, 357 MAX_QUEUE_READERS = 1, 358 PROCEDURE_NAME = initiator_queue_activated_procedure, 359 EXECUTE AS OWNER); 360GO 361 362-- Create initiator service. 363CREATE SERVICE initiator_service ON QUEUE initiator_queue (data_push_contract); 364GO 365 366-- Any user can send on the service. 367GRANT SEND ON SERVICE::initiator_service TO PUBLIC; 368GO 369 370-- This table stores unsent messages. 371IF EXISTS (SELECT name FROM sys.tables WHERE name = 'unsent_messages') 372 DROP TABLE unsent_messages; 373GO 374 375CREATE TABLE unsent_messages ( message_type_name SYSNAME, message_body VARCHAR(MAX) ); 376GO 377 378-- Table to store processing errors. 379IF EXISTS (SELECT name FROM sys.tables WHERE name = 'initiator_processing_errors') 380 DROP TABLE initiator_processing_errors; 381GO 382 383CREATE TABLE initiator_processing_errors (error_conversation UNIQUEIDENTIFIER, error_number INT, 384 error_message VARCHAR(4000), error_severity INT, error_state INT, error_procedure SYSNAME NULL, 385 error_line INT, doomed_transaction TINYINT) 386GO 387 388--------------------------------------------------------------------- 389-- Routing. 390-- Skip the following if services are in the same database instance. 391--------------------------------------------------------------------- 392 393-- Create a route to the target service. 394CREATE ROUTE target_route 395 WITH SERVICE_NAME = 'target_service', 396 ADDRESS = 'tcp://target_host:4022'; 397GO 398 399-- In msdb, create an incoming route to the initiator service. 400USE msdb; 401GO 402 403CREATE ROUTE initiator_route 404 WITH SERVICE_NAME = 'initiator_service', 405 ADDRESS = 'local'; 406GO 407 408USE data_push_database; 409GO