/Kilimanjaro_Trunk/FastDataPush/Scripts/2 - Initiator setup.sql

  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