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

--------------------------------------------------------------------
-- Script for fast data push sample.
--
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------


----------------------------------------------------
-- Initiator setup for fast data push.
-- Before running, customize the configuration-dependent
-- routing to the target service.
----------------------------------------------------

USE data_push_database;
GO

-- The data push procedure: send messages to target.
CREATE PROCEDURE usp_data_push
AS
BEGIN
    SET NOCOUNT ON;

    -- Get initiator parameters.
    DECLARE @message_quantity BIGINT;
    DECLARE @message_size INT;
    DECLARE @number_initiator_transactions INT;
    DECLARE @initiator_transaction_delay CHAR(12);
    DECLARE @number_dialogs INT;
    DECLARE @dialog_recycle_max_messages BIGINT;
    SET @message_quantity = (SELECT message_quantity FROM data_push_parameters);
    SET @message_size = (SELECT message_size FROM data_push_parameters);
    SET @number_initiator_transactions = (SELECT number_initiator_transactions FROM data_push_parameters);
    SET @initiator_transaction_delay = (SELECT initiator_transaction_delay FROM data_push_parameters);
    SET @number_dialogs = (SELECT number_dialogs FROM data_push_parameters);
    SET @dialog_recycle_max_messages = (SELECT dialog_recycle_max_messages FROM data_push_parameters);

    -- Create a message payload of the requested size.
    DECLARE @payload VARCHAR(MAX);
    DECLARE @char VARCHAR(MAX);
    SET @char = '0';
    SELECT @payload = REPLICATE(@char, @message_size);
    
    -- Loop controls.
    DECLARE @messages_per_transaction BIGINT;
    DECLARE @messages_per_dialog_transaction BIGINT;
    DECLARE @transaction_counter INT;
    DECLARE @message_counter BIGINT;
    
    -- Compute messages to send per dialog per transaction:
    -- @message_quantity / (@number_initiator_transactions x @number_dialogs)
    -- Note that integer arithmetic may result in "remainder" messages that will not
    -- be sent.
    SET @messages_per_transaction = @message_quantity / @number_initiator_transactions;
    SET @messages_per_dialog_transaction = @messages_per_transaction / @number_dialogs;

    -- Error variables.
    DECLARE @error_number INT;
    DECLARE @error_message VARCHAR(4000);

    -- Show start time.
    SELECT GETDATE() AS 'Start sending';

    -- Create a table containing requested number of dialogs.
    DECLARE @dialogs TABLE (idx INT, handle UNIQUEIDENTIFIER, recycle_counter BIGINT);
    DECLARE @idx INT;
    DECLARE @handle UNIQUEIDENTIFIER;
    DECLARE @recycle_counter BIGINT;
    SET @idx = 0;
    WHILE @idx < @number_dialogs
    BEGIN
         BEGIN DIALOG CONVERSATION @handle
               FROM SERVICE initiator_service
               TO SERVICE 'target_service'
               ON CONTRACT data_push_contract
               WITH ENCRYPTION = OFF;
         INSERT INTO @dialogs (idx, handle, recycle_counter) VALUES (@idx, @handle, 0);
         SET @idx = @idx + 1;
    END

    -- Loop through transactions.
    SET @transaction_counter = 0;
    WHILE @transaction_counter < @number_initiator_transactions
    BEGIN
        BEGIN TRANSACTION;
        
        -- Loop through dialogs.
        SET @idx = 0;
        WHILE @idx < @number_dialogs
        BEGIN
             -- Send a batch of messages for dialog.
             SET @handle = (SELECT handle FROM @dialogs WHERE idx = @idx);
             SET @recycle_counter = (SELECT recycle_counter FROM @dialogs WHERE idx = @idx);
             SET @message_counter = 0;
             WHILE @message_counter < @messages_per_dialog_transaction
             BEGIN
                  -- Time to recycle dialog?
                  IF @dialog_recycle_max_messages <> -1 AND 
                     @recycle_counter = @dialog_recycle_max_messages
                  BEGIN
                       -- Inform target to end dialog.
                       SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream;
    
                       -- Replace the current dialog.
                       BEGIN DIALOG CONVERSATION @handle
                           FROM SERVICE initiator_service
                           TO SERVICE 'target_service'
                           ON CONTRACT data_push_contract
                           WITH ENCRYPTION = OFF;
                       UPDATE @dialogs SET handle = @handle WHERE idx = @idx;
                       SET @recycle_counter = 0;
                  END
                  
                  -- Send a message.
                  BEGIN TRY
                        BEGIN
                             SEND ON CONVERSATION @handle MESSAGE TYPE data_push_message (@payload);
                        END
                        IF @dialog_recycle_max_messages <> -1
                        BEGIN
                             SET @recycle_counter = @recycle_counter + 1;
                        END
                        SET @message_counter = @message_counter + 1;
                  END TRY
                  BEGIN CATCH
                        SET @error_number = ERROR_NUMBER();
                        SET @error_message = ERROR_MESSAGE();

                       -- Dialog is faulty?
                       DECLARE @dialog_error INT;
                       SET @dialog_error = 1;
                       DECLARE @dialog_state VARCHAR(2);
                       SET @dialog_state = (SELECT state FROM sys.conversation_endpoints
                           WHERE conversation_handle = @handle);
                       IF @@ROWCOUNT = 1
                       BEGIN
                            -- Good dialog is starting or conversing.
                            IF @dialog_state = 'SO' OR @dialog_state = 'CO'
                            BEGIN
                                 SET @dialog_error = 0;
                            END
                       END
                       IF @dialog_error = 1
                       BEGIN
                            -- Record the error.
                            INSERT INTO initiator_processing_errors VALUES(@handle, @error_number,
                                   @error_message, NULL, NULL, NULL, NULL, 0);

                            -- Replace dialog and continue sending.
                            BEGIN DIALOG CONVERSATION @handle
                                  FROM SERVICE initiator_service
                                  TO SERVICE 'target_service'
                                  ON CONTRACT data_push_contract
                                  WITH ENCRYPTION = OFF;
                                  UPDATE @dialogs SET handle = @handle WHERE idx = @idx;
                                  SET @recycle_counter = 0;
                       END
                       ELSE
                       BEGIN
                            -- Record the error and return error.
                            INSERT INTO initiator_processing_errors VALUES(@handle, @error_number,
                                   @error_message, NULL, NULL, NULL, NULL, 0);
                            RETURN 1;
                       END
                  END CATCH
             END
             UPDATE @dialogs SET recycle_counter = @recycle_counter WHERE idx = @idx;
             SET @idx = @idx + 1;
        END
        COMMIT;
        SET @transaction_counter = @transaction_counter + 1;
        
        -- Wait for next transaction.
        IF @transaction_counter < @number_initiator_transactions
        BEGIN
             WAITFOR DELAY @initiator_transaction_delay;
        END
    END

    -- Gracefully end dialogs by informing target.
    BEGIN TRANSACTION;
    SET @idx = 0;
    WHILE @idx < @number_dialogs
    BEGIN
         SET @handle = (SELECT handle FROM @dialogs WHERE idx = @idx);
         BEGIN
              SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream;
         END
         SET @idx = @idx + 1;
    END
    COMMIT;
    
    -- Show end time.
    SELECT GETDATE() AS 'End sending';    

    RETURN 0;
END;
GO

-- Resends all pending messages in sys.transmission_queue
-- belonging to an old conversation on a new conversation.
CREATE PROCEDURE usp_resend_pending (@old_handle UNIQUEIDENTIFIER)
AS
BEGIN
     SET NOCOUNT ON;

     DECLARE @message_type_name SYSNAME;
     DECLARE @message_body VARCHAR(MAX);
     
     -- Get a new dialog.
     DECLARE @handle UNIQUEIDENTIFIER;
     BEGIN DIALOG CONVERSATION @handle
           FROM SERVICE initiator_service
           TO SERVICE 'target_service'
           ON CONTRACT data_push_contract
           WITH ENCRYPTION = OFF;

     -- Declare a cursor to iterate over all the pending messages.
     -- It is important to keep the message order and to keep the original message type.
     DECLARE cursor_pending CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT message_type_name, message_body
            FROM sys.transmission_queue
            WHERE conversation_handle = @old_handle
            ORDER BY message_sequence_number;
     OPEN cursorPending;

     FETCH NEXT FROM cursor_pending INTO @message_type_name, @message_body;
     WHILE (@@FETCH_STATUS = 0)
     BEGIN
          -- Resend the message on the new conversation
          SEND ON CONVERSATION @handle MESSAGE TYPE @message_type_name (@message_body);

          FETCH NEXT FROM cursor_pending INTO @message_type_name, @message_body;
     END
     CLOSE cursor_pending;
     DEALLOCATE cursor_pending;
     
     -- Signal end of stream to target.
     SEND ON CONVERSATION @handle MESSAGE TYPE end_of_stream;
END;
GO

-- Activated store proc for the initiator to receive messages.
-- Dialogs are gracefully ended by the target after receiving
-- an end_of_stream message from the initiator; the end dialog
-- message is then processed here. This method is recommended
-- to avoid "fire and forget" message loss. One message per
-- invocation is OK here for expected low-volume load.
CREATE PROCEDURE initiator_queue_activated_procedure
AS
BEGIN
     SET NOCOUNT ON;
       
     DECLARE @conversation_handle UNIQUEIDENTIFIER,
             @message_type_name SYSNAME,
             @message_body VARCHAR(MAX);

     -- Error variables.
     DECLARE @error_number INT;
     DECLARE @error_message VARCHAR(4000);
     DECLARE @error_severity INT;
     DECLARE @error_state INT;
     DECLARE @error_procedure SYSNAME;
     DECLARE @error_line INT;

     BEGIN TRY
     BEGIN TRANSACTION;

     -- Wait 5 seconds for a message.
     WAITFOR (
          RECEIVE TOP(1)
                  @conversation_handle = conversation_handle,
                  @message_type_name = message_type_name,
                  @message_body = message_body
          FROM initiator_queue), TIMEOUT 5000;

     IF @@ROWCOUNT = 1
     BEGIN
          IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
          BEGIN
               -- Target is ending dialog normally.
               END CONVERSATION @conversation_handle;
          END
          ELSE IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
          BEGIN
               -- Record the error.
               WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
               SELECT
               @error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
               @error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)');
               INSERT INTO initiator_processing_errors VALUES(@conversation_handle, @error_number,
                      @error_message, NULL, NULL, NULL, NULL, 0);

               -- Can messages be resent?
               IF (@error_number IN (-8489, -8462, -9719, -28052))
               BEGIN
                    -- Resend the messages on a new dialog.
                    EXEC usp_resend_pending @conversation_handle;
               END
               ELSE
               BEGIN
                    -- Save the messages in a side table to be processed later.
                    INSERT INTO unsent_messages
                           SELECT message_type_name, message_body FROM sys.transmission_queue
                           WHERE conversation_handle = @conversation_handle;
               END

               -- End the conversation.
               END CONVERSATION @conversation_handle;
          END
     END
     COMMIT;
     END TRY
     BEGIN CATCH
           SET @error_number = ERROR_NUMBER();
           SET @error_message = ERROR_MESSAGE();
           SET @error_severity = ERROR_SEVERITY();
           SET @error_state = ERROR_STATE();
           SET @error_procedure = ERROR_PROCEDURE();
           SET @error_line = ERROR_LINE();

           IF XACT_STATE() = -1
           BEGIN
                -- The transaction is doomed. Only rollback possible.
                -- Note: 5 consecutive rollbacks will disable the queue!
                ROLLBACK TRANSACTION;

                -- Record the error.
                BEGIN TRANSACTION;
                INSERT INTO initiator_processing_errors VALUES(NULL, @error_number, @error_message,
                       @error_severity, @error_state, @error_procedure, @error_line, 1);
                COMMIT;
           END
           ELSE IF XACT_STATE() = 1
           BEGIN
                -- Record error and commit transaction.
                INSERT INTO initiator_processing_errors VALUES(NULL, @error_number, @error_message,
                       @error_severity, @error_state, @error_procedure, @error_line, 0);
                COMMIT;
           END
     END CATCH
END;
GO

-- Create the initiator queue with activated procedure.
CREATE QUEUE initiator_queue
      WITH ACTIVATION (
            STATUS = ON,
            MAX_QUEUE_READERS = 1,
            PROCEDURE_NAME = initiator_queue_activated_procedure,
            EXECUTE AS OWNER);
GO

-- Create initiator service.
CREATE SERVICE initiator_service ON QUEUE initiator_queue (data_push_contract);
GO

-- Any user can send on the service.
GRANT SEND ON SERVICE::initiator_service TO PUBLIC;
GO

-- This table stores unsent messages.
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'unsent_messages')
	DROP TABLE unsent_messages;
GO

CREATE TABLE unsent_messages ( message_type_name SYSNAME, message_body VARCHAR(MAX) );
GO

-- Table to store processing errors.
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'initiator_processing_errors')
	DROP TABLE initiator_processing_errors;
GO

CREATE TABLE initiator_processing_errors (error_conversation UNIQUEIDENTIFIER, error_number INT,
       error_message VARCHAR(4000), error_severity INT, error_state INT, error_procedure SYSNAME NULL,
       error_line INT, doomed_transaction TINYINT)
GO

---------------------------------------------------------------------
-- Routing.
-- Skip the following if services are in the same database instance.
---------------------------------------------------------------------

-- Create a route to the target service.
CREATE ROUTE target_route
	WITH SERVICE_NAME = 'target_service',
	ADDRESS = 'tcp://target_host:4022';
GO

-- In msdb, create an incoming route to the initiator service.
USE msdb;
GO

CREATE ROUTE initiator_route
	WITH SERVICE_NAME = 'initiator_service',
	ADDRESS = 'local';
GO

USE data_push_database;
GO