/Katmai_October2008_Refresh1/FastDataPush/Scripts/1 - Common 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.

--------------------------------------------------------------------



----------------------------------------------------

-- Common setup for fast data push.

-- Before running, replace the configuration-dependent 

-- domain_name and partner_host names.

----------------------------------------------------



USE master;

GO



---------------------------------------------------------------------

-- Create the broker endpoint using Windows authentication.

-- On a secure network, encryption may be disabled to improve speed:

-- (AUTHENTICATION = Windows, ENCRYPTION = DISABLED)

-- This step can be skipped if services are in the same database instance.

---------------------------------------------------------------------



IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')

	DROP ENDPOINT service_broker_endpoint;

GO



CREATE ENDPOINT service_broker_endpoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (AUTHENTICATION = Windows);

GO



-- A procedure to create a Windows login and grant it endpoint connection permission.

IF EXISTS (SELECT name FROM tempdb.sys.procedures WHERE name LIKE '#usp_windows_login_for_broker_endpoint%')

      DROP PROCEDURE #usp_windows_login_for_broker_endpoint;

GO



CREATE PROCEDURE #usp_windows_login_for_broker_endpoint (

      @domain_name VARCHAR(100),

      @login_name VARCHAR(50),

      @endpoint_name VARCHAR(50))

AS

BEGIN

     SET NOCOUNT ON;



     DECLARE @query VARCHAR(1000);

     

     -- Create the login.

     SET @query =

         'IF EXISTS (SELECT * FROM sys.syslogins WHERE name = ''' + @domain_name + '\' + @login_name + ''')

	     DROP LOGIN [' + @domain_name + '\' + @login_name + ']';

     EXEC (@query);



     SET @query = 'CREATE LOGIN [' + @domain_name + '\' + @login_name + '] FROM Windows';

     EXEC (@query);



     -- Grant the login connection access to the endpoint.

     SET @query = 'GRANT CONNECT ON ENDPOINT::' + @endpoint_name + ' TO [' + @domain_name + '\' + @login_name + ']';

     EXEC (@query);

END;

GO



-- Create a login for the partner machine (partner_host) in the

-- shared domain (domain_name) and grant it endpoint connection permission.

-- This assumes the availability of Kerberos authentication.

-- Note: the '$' is significant.

EXEC #usp_windows_login_for_broker_endpoint 'domain_name', 'partner_host$', 'service_broker_endpoint';

GO



---------------------------------------------------------------------

-- Create the data push database.

---------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'data_push_database')

	DROP DATABASE data_push_database;

GO



CREATE DATABASE data_push_database;

GO



USE data_push_database;

GO



-- Create messages and contract.

CREATE MESSAGE TYPE data_push_message VALIDATION = NONE;

CREATE MESSAGE TYPE end_of_stream;

CREATE CONTRACT data_push_contract

       (

        data_push_message SENT BY INITIATOR,

        end_of_stream SENT BY INITIATOR

       );



----------------------------------------------------

-- The data push parameters.

--

-- Application parameters:

-- message_quantity: number of messages sent.

-- message_size: size of message in bytes.

-- message_processing_time: time for target to process a message.

--    Format: 'hh:mm:ss:xxx'  hh=hours, mm=minutes, ss=seconds, xxx=milliseconds

--

-- Internal parameters:

-- number_initiator_transactions: number of initiator transactions used.

--    Notes: 1. Fewer is more efficient since each transaction entails an overhead.

--           2. Messages are actually sent when transaction commits, so sending a large

--              number of messages in a transaction can result in increased latency.

-- initiator_transaction_delay: delay time between initiator transactions.

--    Format: 'hh:mm:ss:xxx'  hh=hours, mm=minutes, ss=seconds, xxx=milliseconds

--    Notes: 1. A transaction can be thought of as a burst of message_quantity / 

--              number_initiator_transactions messages. This delay specifies a time

--              to wait before the next transaction is run.

--           2. This parameter can be used to simulate message traffic distributed

--              over time.

-- number_dialogs: number of dialogs used to send messages.

--    Notes: 1. Message ordering only guaranteed with a dialog.

--           2. Multiple dialogs allows concurrent processing on target.

--           3. Dialog creation is expensive; dialog reuse is employed here.

-- dialog_recycle_max_messages: maximum number messages sent on a dialog before

--    recycling the dialog. Recycling is defined as ending the old dialog and

--    beginning a new one. A value of -1 indicates no recycling.

--    Notes: 1. Larger is more efficient since is minimizes the overhead of

--              creating dialogs.

--           2. Larger can complicate dialog error processing.

-- number_target_procedures: number of activated target procedures to receive messages.

--    Notes: 1. A target proc locks all messages in a dialog when it receives first message

--              for a dialog, blocking other procs from processing these messages.

--           2. Thus more dialogs yields increased concurrent processing. However, unless

--              dialog recycling is used, this should be set to number_dialogs, which

--              can utilize a target proc for each dialog.

-- max_messages_per_receive: maximum number of messages per target receive call.

--    Notes: 1. Larger is more efficient, but can complicate transaction error processing.

--           2. The maximum value can be set to message_quantity / number_dialogs.

--

-- General note: for simplicity, @message_quantity should be evenly divisible

-- by @number_initiator_transactions x @number_dialogs, since this allows a

-- constant number of messages to be sent per dialog per transaction. "Remainder"

-- messages will not be sent to the target.

--

-- Example:

--

-- I want to send 100000 messages in sets of 10000 with a delay of 10 seconds between

-- each set. This calls for 10 transactions. Each message is 100 bytes and the target

-- message processing time is 10 ms. The messages are independent of each other, so use

-- 5 dialogs and target procedures to get some concurrent processing on the target. Allow

-- each target proc to receive 2000 messages at a time. Do not recycle dialogs.

--

-- INSERT INTO data_push_parameters

--       VALUES

--       (

--       100000,

--       10000,

--       '00:00:00:010',

--       10,

--       '00:00:10:000',

--       5,

--       -1,

--       5,

--       2000

--       );

--

--

CREATE TABLE data_push_parameters (

      message_quantity BIGINT NOT NULL,

      message_size INT NOT NULL,

      message_processing_time CHAR(12) NOT NULL,

      number_initiator_transactions INT NOT NULL,

      initiator_transaction_delay CHAR(12) NOT NULL,

      number_dialogs INT NOT NULL,

      dialog_recycle_max_messages BIGINT NOT NULL,

      number_target_procedures INT NOT NULL,

      max_messages_per_receive BIGINT NOT NULL);

GO



-- Insert parameter values.

TRUNCATE TABLE data_push_parameters;

INSERT INTO data_push_parameters

       (

       message_quantity,

       message_size,

       message_processing_time,

       number_initiator_transactions,

       initiator_transaction_delay,

       number_dialogs,

       dialog_recycle_max_messages,

       number_target_procedures,

       max_messages_per_receive

       )

       VALUES

       (

       10000,

       1000,

       '00:00:00:000',

       1,

       '00:00:00:000',

       1,

       -1,

       1,

       1000

       );

GO



-- Check parameters.

DECLARE @message_quantity BIGINT;

DECLARE @number_initiator_transactions INT;

DECLARE @number_dialogs INT;

DECLARE @i BIGINT;

DECLARE @string VARCHAR(50);

SET @message_quantity = (SELECT message_quantity FROM data_push_parameters);

SET @number_initiator_transactions = (SELECT number_initiator_transactions FROM data_push_parameters);

SET @number_dialogs = (SELECT number_dialogs FROM data_push_parameters);

SET @i = @message_quantity / (@number_dialogs * @number_initiator_transactions);

SET @i = @i * @number_dialogs * @number_initiator_transactions;

IF @message_quantity > @i

BEGIN

     SET @i = @message_quantity - @i;

     SET @string = (SELECT CAST( @i AS VARCHAR(50)));

     PRINT 'Warning: @message_quantity is not evenly divisible by @number_dialogs * @number_initiator_transactions';

     PRINT @string + ' messages will not be sent to the target';

END;

GO