/Katmai_October2008_Refresh1/FastDataPush/Scripts/1 - Common 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-- Common setup for fast data push.
 18-- Before running, replace the configuration-dependent 
 19-- domain_name and partner_host names.
 20----------------------------------------------------
 21
 22USE master;
 23GO
 24
 25---------------------------------------------------------------------
 26-- Create the broker endpoint using Windows authentication.
 27-- On a secure network, encryption may be disabled to improve speed:
 28-- (AUTHENTICATION = Windows, ENCRYPTION = DISABLED)
 29-- This step can be skipped if services are in the same database instance.
 30---------------------------------------------------------------------
 31
 32IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')
 33	DROP ENDPOINT service_broker_endpoint;
 34GO
 35
 36CREATE ENDPOINT service_broker_endpoint
 37STATE = STARTED
 38AS TCP (LISTENER_PORT = 4022)
 39FOR SERVICE_BROKER (AUTHENTICATION = Windows);
 40GO
 41
 42-- A procedure to create a Windows login and grant it endpoint connection permission.
 43IF EXISTS (SELECT name FROM tempdb.sys.procedures WHERE name LIKE '#usp_windows_login_for_broker_endpoint%')
 44      DROP PROCEDURE #usp_windows_login_for_broker_endpoint;
 45GO
 46
 47CREATE PROCEDURE #usp_windows_login_for_broker_endpoint (
 48      @domain_name VARCHAR(100),
 49      @login_name VARCHAR(50),
 50      @endpoint_name VARCHAR(50))
 51AS
 52BEGIN
 53     SET NOCOUNT ON;
 54
 55     DECLARE @query VARCHAR(1000);
 56     
 57     -- Create the login.
 58     SET @query =
 59         'IF EXISTS (SELECT * FROM sys.syslogins WHERE name = ''' + @domain_name + '\' + @login_name + ''')
 60	     DROP LOGIN [' + @domain_name + '\' + @login_name + ']';
 61     EXEC (@query);
 62
 63     SET @query = 'CREATE LOGIN [' + @domain_name + '\' + @login_name + '] FROM Windows';
 64     EXEC (@query);
 65
 66     -- Grant the login connection access to the endpoint.
 67     SET @query = 'GRANT CONNECT ON ENDPOINT::' + @endpoint_name + ' TO [' + @domain_name + '\' + @login_name + ']';
 68     EXEC (@query);
 69END;
 70GO
 71
 72-- Create a login for the partner machine (partner_host) in the
 73-- shared domain (domain_name) and grant it endpoint connection permission.
 74-- This assumes the availability of Kerberos authentication.
 75-- Note: the '$' is significant.
 76EXEC #usp_windows_login_for_broker_endpoint 'domain_name', 'partner_host$', 'service_broker_endpoint';
 77GO
 78
 79---------------------------------------------------------------------
 80-- Create the data push database.
 81---------------------------------------------------------------------
 82IF EXISTS (SELECT * FROM sys.databases WHERE name = 'data_push_database')
 83	DROP DATABASE data_push_database;
 84GO
 85
 86CREATE DATABASE data_push_database;
 87GO
 88
 89USE data_push_database;
 90GO
 91
 92-- Create messages and contract.
 93CREATE MESSAGE TYPE data_push_message VALIDATION = NONE;
 94CREATE MESSAGE TYPE end_of_stream;
 95CREATE CONTRACT data_push_contract
 96       (
 97        data_push_message SENT BY INITIATOR,
 98        end_of_stream SENT BY INITIATOR
 99       );
100
101----------------------------------------------------
102-- The data push parameters.
103--
104-- Application parameters:
105-- message_quantity: number of messages sent.
106-- message_size: size of message in bytes.
107-- message_processing_time: time for target to process a message.
108--    Format: 'hh:mm:ss:xxx'  hh=hours, mm=minutes, ss=seconds, xxx=milliseconds
109--
110-- Internal parameters:
111-- number_initiator_transactions: number of initiator transactions used.
112--    Notes: 1. Fewer is more efficient since each transaction entails an overhead.
113--           2. Messages are actually sent when transaction commits, so sending a large
114--              number of messages in a transaction can result in increased latency.
115-- initiator_transaction_delay: delay time between initiator transactions.
116--    Format: 'hh:mm:ss:xxx'  hh=hours, mm=minutes, ss=seconds, xxx=milliseconds
117--    Notes: 1. A transaction can be thought of as a burst of message_quantity / 
118--              number_initiator_transactions messages. This delay specifies a time
119--              to wait before the next transaction is run.
120--           2. This parameter can be used to simulate message traffic distributed
121--              over time.
122-- number_dialogs: number of dialogs used to send messages.
123--    Notes: 1. Message ordering only guaranteed with a dialog.
124--           2. Multiple dialogs allows concurrent processing on target.
125--           3. Dialog creation is expensive; dialog reuse is employed here.
126-- dialog_recycle_max_messages: maximum number messages sent on a dialog before
127--    recycling the dialog. Recycling is defined as ending the old dialog and
128--    beginning a new one. A value of -1 indicates no recycling.
129--    Notes: 1. Larger is more efficient since is minimizes the overhead of
130--              creating dialogs.
131--           2. Larger can complicate dialog error processing.
132-- number_target_procedures: number of activated target procedures to receive messages.
133--    Notes: 1. A target proc locks all messages in a dialog when it receives first message
134--              for a dialog, blocking other procs from processing these messages.
135--           2. Thus more dialogs yields increased concurrent processing. However, unless
136--              dialog recycling is used, this should be set to number_dialogs, which
137--              can utilize a target proc for each dialog.
138-- max_messages_per_receive: maximum number of messages per target receive call.
139--    Notes: 1. Larger is more efficient, but can complicate transaction error processing.
140--           2. The maximum value can be set to message_quantity / number_dialogs.
141--
142-- General note: for simplicity, @message_quantity should be evenly divisible
143-- by @number_initiator_transactions x @number_dialogs, since this allows a
144-- constant number of messages to be sent per dialog per transaction. "Remainder"
145-- messages will not be sent to the target.
146--
147-- Example:
148--
149-- I want to send 100000 messages in sets of 10000 with a delay of 10 seconds between
150-- each set. This calls for 10 transactions. Each message is 100 bytes and the target
151-- message processing time is 10 ms. The messages are independent of each other, so use
152-- 5 dialogs and target procedures to get some concurrent processing on the target. Allow
153-- each target proc to receive 2000 messages at a time. Do not recycle dialogs.
154--
155-- INSERT INTO data_push_parameters
156--       VALUES
157--       (
158--       100000,
159--       10000,
160--       '00:00:00:010',
161--       10,
162--       '00:00:10:000',
163--       5,
164--       -1,
165--       5,
166--       2000
167--       );
168--
169--
170CREATE TABLE data_push_parameters (
171      message_quantity BIGINT NOT NULL,
172      message_size INT NOT NULL,
173      message_processing_time CHAR(12) NOT NULL,
174      number_initiator_transactions INT NOT NULL,
175      initiator_transaction_delay CHAR(12) NOT NULL,
176      number_dialogs INT NOT NULL,
177      dialog_recycle_max_messages BIGINT NOT NULL,
178      number_target_procedures INT NOT NULL,
179      max_messages_per_receive BIGINT NOT NULL);
180GO
181
182-- Insert parameter values.
183TRUNCATE TABLE data_push_parameters;
184INSERT INTO data_push_parameters
185       (
186       message_quantity,
187       message_size,
188       message_processing_time,
189       number_initiator_transactions,
190       initiator_transaction_delay,
191       number_dialogs,
192       dialog_recycle_max_messages,
193       number_target_procedures,
194       max_messages_per_receive
195       )
196       VALUES
197       (
198       10000,
199       1000,
200       '00:00:00:000',
201       1,
202       '00:00:00:000',
203       1,
204       -1,
205       1,
206       1000
207       );
208GO
209
210-- Check parameters.
211DECLARE @message_quantity BIGINT;
212DECLARE @number_initiator_transactions INT;
213DECLARE @number_dialogs INT;
214DECLARE @i BIGINT;
215DECLARE @string VARCHAR(50);
216SET @message_quantity = (SELECT message_quantity FROM data_push_parameters);
217SET @number_initiator_transactions = (SELECT number_initiator_transactions FROM data_push_parameters);
218SET @number_dialogs = (SELECT number_dialogs FROM data_push_parameters);
219SET @i = @message_quantity / (@number_dialogs * @number_initiator_transactions);
220SET @i = @i * @number_dialogs * @number_initiator_transactions;
221IF @message_quantity > @i
222BEGIN
223     SET @i = @message_quantity - @i;
224     SET @string = (SELECT CAST( @i AS VARCHAR(50)));
225     PRINT 'Warning: @message_quantity is not evenly divisible by @number_dialogs * @number_initiator_transactions';
226     PRINT @string + ' messages will not be sent to the target';
227END;
228GO