SQL SERVICE BROKER AND ME

OH MAN!!! This seriously sent me for a loop... mostly cause I kept getting interrupted. 
This is my self help, part 1 for SSB. Fist time I got it to work was a miracle. After that 
it grew legs and ran around for a bit. This will setup the Endpoints and generic 
basics. Just need to work on the Activation, Poisson messaging and Event notifications.

PROPS OUT TO: 
http://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270 
- Just good wholesome stuff

http://rusanu.com/2007/10/31/error-handling-in-service-broker-procedures/ 
- This guys is Mr.SSB. Tons of references

http://www.hilite.me/ - This just made my day.  Thank you for the readability
/*
Notes:
***Check communication between SQL servers

Run this on the sender and receiver, but be
sure to swap the following values respectively:

@LocalServer = 'NITROGEN'
, @LocalDB = 'NITROGEN_SSB'

, @RemoteServer = 'GOLD'
, @RemoteDB = 'GOLD_SSB'
*/

DECLARE
@SQLCMD NVARCHAR(MAX)
, @SenderDB VarChar(128) -- Which way should the data flow
, @LocalServer VarChar(128)
, @LocalDB VarChar(128)
, @LocalBrokerID uniqueIdentifier
, @RouteSend VarChar(128)
, @RouteResponse VarChar(128)
, @LocalDB_ENDPOINT VarChar(128)
, @RemoteServer VarChar(128)
, @RemoteDB VarChar(128)
, @RemoteBrokerID uniqueIdentifier
, @ListenerPort VarChar(4)
, @ServiceNameReceiver VarChar(256)
, @ServiceNameSender VarChar(256)
, @WindowsServiceAccount VarChar(128)
, @SSBNameSpace VarChar(128)


SET NOCOUNT ON

SELECT
@SenderDB = 'GOLD_SSB'

, @RemoteServer = 'GOLD'
, @RemoteDB= 'GOLD_SSB'
, @LocalServer = 'NITROGEN'
, @LocalDB = 'NITROGEN_SSB'

--, @LocalServer= 'GOLD'
--, @LocalDB = 'GOLD_SSB'
--, @RemoteServer = 'NITROGEN'
--, @RemoteDB= 'NITROGEN_SSB'

, @RouteSend = 'RoutePackage_Send'
, @RouteResponse = 'RoutePackage_Response'
, @ListenerPort = '4085'
, @SSBNameSpace = 'TestingSSB'

, @WindowsServiceAccount = 'DOMAIN\sqlservice'

IF @@SERVERNAME <> @LocalServer
BEGIN
PRINT 'ACHTUNG!!!
@LocalServer = ' + @LocalServer + '
ACTUAL LOCAL SERVER NAME IS ' + @@SERVERNAME + '

PLEASE REVIEW AND CORRECT'
RETURN
END

SELECT
@ServiceNameReceiver = '//' + @SSBNameSpace + '/Service_Receiver'
, @ServiceNameSender = '//' + @SSBNameSpace + '/Service_Sender'
,@LocalDB_ENDPOINT = @LocalDB + '_ENDPOINT'

IF OBJECT_ID('tempdb..#tmpSSB') IS NOT NULL DROP TABLE #tmpSSB

CREATE TABLE #tmpSSB(service_broker_guid uniqueIdentifier)


--// ---------------------------------------------------------------------------
--// 1. CREATE SERVICE BROKER ENDPOINT -----------------------------------------
SELECT @SQLCMD = '
USE MASTER
ALTER DATABASE [' + @LocalDB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [' + @LocalDB + '] SET ENABLE_BROKER;
ALTER DATABASE [' + @LocalDB + '] SET MULTI_USER;'
EXEC(@SQLCMD)

SELECT @SQLCMD = '
IF EXISTS(SELECT Name FROM sys.service_broker_endpoints WHERE Name = ''' + @LocalDB_ENDPOINT + ''')
BEGIN
PRINT ''Dropping Existing @LocalDB_ENDPOINT''
DROP ENDPOINT ' + quoteName(@LocalDB_ENDPOINT) + '
END'
EXEC(@SQLCMD)

SELECT @SQLCMD = 'CREATE ENDPOINT ' + @LocalDB_ENDPOINT + '
AS TCP (LISTENER_PORT = ' + @ListenerPort + ')
FOR SERVICE_BROKER(
AUTHENTICATION = WINDOWS
,ENCRYPTION = DISABLED
)

;USE MASTER
GRANT CONNECT ON ENDPOINT::' + @LocalDB_ENDPOINT + ' To ['+ @WindowsServiceAccount +'];

ALTER ENDPOINT ' + @LocalDB_ENDPOINT + ' STATE= STARTED;
'
EXEC(@SQLCMD)
PRINT 'ENDPOINT: @LocalDB_ENDPOINT=' + @LocalDB_ENDPOINT + ', created'
--// ---------------------------------------------------------------------------

SELECT @SQLCMD = '
INSERT INTO #tmpSSB
SELECT
service_broker_guid
FROM
' + quoteName(@RemoteServer) + '.master.sys.databases where name = ''' + @RemoteDB + ''''
EXEC(@SQLCMD)

SELECT @RemoteBrokerID = service_broker_guid FROM #tmpSSB
IF @@ROWCOUNT = 0
BEGIN
SELECT 'REMOTE DB service_broker_guid NOT FOUND!'
RETURN
END

IF OBJECT_ID('tempdb..#tmpSSB') IS NOT NULL TRUNCATE TABLE #tmpSSB

SELECT @SQLCMD = '
INSERT INTO #tmpSSB
SELECT
service_broker_guid
FROM
' + quoteName(@LocalServer) + '.master.sys.databases where name = ''' + @LocalDB + ''''
EXEC(@SQLCMD)

SELECT @LocalBrokerID = service_broker_guid FROM #tmpSSB
IF @@ROWCOUNT = 0
BEGIN
SELECT 'LOCAL DB service_broker_guid NOT FOUND!'
RETURN
END

--// ---------------------------------------------------------------------------
--// X. DROP EXISTING ----------------------------------------------------------
IF @SenderDB = @LocalDB
BEGIN
SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.services WHERE NAME = ''' + @ServiceNameSender + ''')
BEGIN
PRINT ''Dropping Existing Service: ' + @ServiceNameSender + '''
DROP SERVICE ' + quoteName(@ServiceNameSender) + '
END'
EXEC(@SQLCMD)

SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = ''' + @ServiceNameSender + '_QUEUE'')
BEGIN
PRINT ''Dropping Existing Queue: ' + @ServiceNameSender + '_QUEUE''
DROP QUEUE ' + quoteName(@ServiceNameSender + '_QUEUE') + '
END'
EXEC(@SQLCMD)

END
ELSE
BEGIN
SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.services WHERE NAME = ''' + @ServiceNameReceiver + ''')
BEGIN
PRINT ''Dropping Existing Service: ' + @ServiceNameReceiver + '''
DROP SERVICE ' + quoteName(@ServiceNameReceiver) + '
END'
EXEC(@SQLCMD)

SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = ''' + @ServiceNameReceiver + '_QUEUE'')
BEGIN
PRINT ''Dropping Existing Queue: ' + @ServiceNameReceiver + '_QUEUE''
DROP QUEUE ' + quoteName(@ServiceNameReceiver + '_QUEUE') + '
END'
EXEC(@SQLCMD)

END

SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = ''//' + @SSBNameSpace + '/Service_CONTRACT'')
BEGIN
PRINT ''Dropping Existing CONTRACT: //' + @SSBNameSpace + '/Service_CONTRACT''
DROP CONTRACT ' + quoteName('//' + @SSBNameSpace + '/Service_CONTRACT') + '
END'
EXEC(@SQLCMD)

SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.service_message_types WHERE NAME = ''//' + @SSBNameSpace + '/RequestMessage'')
BEGIN
PRINT ''Dropping Existing MESSAGE TYPE : //' + @SSBNameSpace + '/RequestMessage''
DROP MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/RequestMessage') + '
END'
EXEC(@SQLCMD)

SELECT @SQLCMD = N'
IF EXISTS(SELECT * FROM sys.service_message_types WHERE NAME = ''//' + @SSBNameSpace + '/ResponseMessage'')
BEGIN
PRINT ''Dropping Existing MESSAGE TYPE : //' + @SSBNameSpace + '/ResponseMessage''
DROP MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/ResponseMessage') + '
END'
EXEC(@SQLCMD)
--// ---------------------------------------------------------------------------

--// ---------------------------------------------------------------------------
--// CREATE THE FOLLOWING
--// 1. Message Type(s), geeneral
--// 2. Contract(s), geeneral
--// 3. Queue(s), specific to sender or receiver
--// 4. Service(s), specific to sender or receiver

SELECT @SQLCMD = N'
CREATE MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/RequestMessage') + ' VALIDATION = NONE
PRINT ''MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/RequestMessage') + ', created''

CREATE MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/ResponseMessage') + ' VALIDATION = NONE
PRINT ''MESSAGE TYPE ' + quoteName('//' + @SSBNameSpace + '/ResponseMessage') + ', created''

CREATE CONTRACT ' + quoteName('//' + @SSBNameSpace + '/Service_CONTRACT') + '
(' + quoteName('//' + @SSBNameSpace + '/RequestMessage') + ' SENT BY INITIATOR
,' + quoteName('//' + @SSBNameSpace + '/ResponseMessage') + ' SENT BY TARGET )
PRINT ''CONTRACT ' + quoteName('//' + @SSBNameSpace + '/Service_CONTRACT') + ', created'''
EXEC(@SQLCMD)

IF @SenderDB = @LocalDB
BEGIN
SELECT @SQLCMD = N'
CREATE QUEUE ' + quoteName(@ServiceNameSender + '_QUEUE') + '
PRINT ''QUEUE' + + quoteName(@ServiceNameSender + '_QUEUE') + ', created''

CREATE SERVICE ' + quoteName(@ServiceNameSender) + '
ON QUEUE ' + quoteName(@ServiceNameSender + '_QUEUE') + '
(' + quoteName('//' + @SSBNameSpace + '/Service_CONTRACT') + ')
PRINT ''SERVICE' + quoteName(@ServiceNameSender) + ', created''

GRANT SEND ON SERVICE::[' + quoteName(@ServiceNameSender + '] TO PUBLIC ;
GRANT RECEIVE ON ' + quoteName(@ServiceNameSender + '_QUEUE') + ' TO [Public];
PRINT ''PERMISSIONS GRANTED ON ' + quoteName(@ServiceNameSender + '_QUEUE') + ''''
EXEC(@SQLCMD)
END
ELSE
BEGIN
SELECT @SQLCMD = N'
CREATE QUEUE ' + quoteName(@ServiceNameReceiver + '_QUEUE') + '
PRINT ''QUEUE' + + quoteName(@ServiceNameReceiver + '_QUEUE') + ', created''

CREATE SERVICE ' + quoteName(@ServiceNameReceiver) + '
ON QUEUE ' + quoteName(@ServiceNameReceiver + '_QUEUE') + '
(' + quoteName('//' + @SSBNameSpace + '/Service_CONTRACT') + ')
PRINT ''SERVICE' + quoteName(@ServiceNameReceiver) + ', created''

GRANT SEND ON SERVICE::[' + quoteName(@ServiceNameReceiver + '] TO PUBLIC ;
GRANT RECEIVE ON ' + quoteName(@ServiceNameReceiver + '_QUEUE') + ' TO [Public];
PRINT ''PERMISSIONS GRANTED ON ' + quoteName(@ServiceNameReceiver + '_QUEUE') +''''
EXEC(@SQLCMD)
END
--// ---------------------------------------------------------------------------

--// ---------------------------------------------------------------------------
--// 5. CREATE SERVICE ROUTES --------------------------------------------------
IF @SenderDB = @LocalDB
BEGIN
--// CREATE OUT-BOUND ROUTE @SenderDB
SELECT @SQLCMD = N'USE ' + quoteName(@LocalDB) + '
IF EXISTS(SELECT * FROM sys.routes WHERE Name = ''' + @RouteSend + ''')
BEGIN
PRINT ''Dropping Existing @RouteSend''
DROP ROUTE ' + quoteName(@RouteSend) + '
END
CREATE ROUTE ' + quoteName(@RouteSend) + '
WITH SERVICE_NAME = ''' + @ServiceNameReceiver + ''',
BROKER_INSTANCE = ''' + CAST(@RemoteBrokerID AS NVARCHAR(50)) + ''',
ADDRESS = N''TCP://' + @RemoteServer +':' + @ListenerPort + ''''
END
ELSE
BEGIN
--// CREATE OUT-BOUND ROUTE @SenderDB
SELECT @SQLCMD = N'USE ' + quoteName(@LocalDB) + '
IF EXISTS(SELECT * FROM sys.routes WHERE Name = ''' + @RouteSend + ''')
BEGIN
PRINT ''Dropping Existing @RouteSend''
DROP ROUTE ' + quoteName(@RouteSend) + '
END
CREATE ROUTE ' + quoteName(@RouteSend) + '
WITH SERVICE_NAME = ''' + @ServiceNameReceiver + ''',
BROKER_INSTANCE = ''' + CAST(@LocalBrokerID AS NVARCHAR(50)) + ''',
ADDRESS = N''LOCAL'''
END

EXEC(@SQLCMD)
PRINT 'Route : @RouteSend=' + @RouteSend + ', created'

IF @SenderDB = @LocalDB
BEGIN
--// CREATE IN-BOUND ROUTE
SELECT @SQLCMD = N'USE ' + quoteName(@LocalDB) + '
IF EXISTS(SELECT Name FROM sys.routes WHERE Name = ''' + @RouteResponse + ''')
BEGIN
PRINT ''Dropping Existing @RouteResponse''
DROP ROUTE ' + quoteName(@RouteResponse) + '
END
CREATE ROUTE ' + quoteName(@RouteResponse) + '
WITH SERVICE_NAME = ''' + @ServiceNameSender + ''',
BROKER_INSTANCE = ''' + CAST(@LocalBrokerID AS NVARCHAR(50)) + ''',
ADDRESS = N''LOCAL'''
END
ELSE
BEGIN
--// CREATE IN-BOUND ROUTE
SELECT @SQLCMD = N'USE ' + quoteName(@LocalDB) + '
IF EXISTS(SELECT Name FROM sys.routes WHERE Name = ''' + @RouteResponse + ''')
BEGIN
PRINT ''Dropping Existing @RouteResponse''
DROP ROUTE ' + quoteName(@RouteResponse) + '
END
CREATE ROUTE ' + quoteName(@RouteResponse) + '
WITH SERVICE_NAME = ''' + @ServiceNameSender + ''',
BROKER_INSTANCE = ''' + CAST(@RemoteBrokerID AS NVARCHAR(50)) + ''',
ADDRESS = N''TCP://' + @RemoteServer +':' + @ListenerPort + ''''
END

EXEC(@SQLCMD)
PRINT 'Route : @RouteResponse=' + @RouteResponse + ', created'
--// ---------------------------------------------------------------------------

RETURN
/*
--//REVIEW

SELECT * FROM sys.service_broker_endpoints WHERE endpoint_ID > 14
SELECT * FROM sys.routes WHERE name NOT IN ('AutoCreatedLocal')
SELECT * FROM sys.service_queues
WHERE Name NOT IN ('QueryNotificationErrorsQueue','EventNotificationErrorsQueue','ServiceBrokerQueue')
SELECT * FROM sys.service_message_types WHERE message_type_ID > 14
SELECT * FROM sys.service_contracts WHERE service_contract_ID > 14
SELECT * FROM sys.services WHERE service_ID > 14
SELECT * FROM sys.dm_broker_connections
*/

Comments :

0 comments to “SQL SERVICE BROKER AND ME”