/Katmai_October2008_Refresh1/FastDataPush/Scripts/1 - Common setup.sql
SQL | 228 lines | 104 code | 18 blank | 106 comment | 0 complexity | 2c024df89c1f9c28577b84c6296de9d2 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-- 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