December 11, 2010 at 11:08 pm
Hi,
I can communicate between two different database instances on
the same Server using SQL Server Service Broker ("SSB") as follow,
Database name: localsb_db1
-- creating localsb_db1 as the first database
create database localsb_db1
WITH TRUSTWORTHY ON;
GO
use localsb_db1;
GO
-- Create Master Key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'hellopassword';
GO
-- Creating Database table to be used for logging data
CREATE TABLE [dbo].[t_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_t_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Stored Procedure that will handle the receiving of Messages
-- This was created from the SQL Server 2005 Templates
CREATE PROCEDURE [dbo].[OnReceiveMessage]
AS
declare @message_type int
declare @dialog uniqueidentifier,
@ErrorSave INT,
@ErrorDesc NVARCHAR(100),
@message_body XML;
while (1 = 1)
begin
begin transaction
-- Receive the next available message from the queue
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message
received
@message_body=message_body, -- the message
contents
@dialog = conversation_handle -- the identifier
of the dialog this message was received on
FROM QUEUE1
), TIMEOUT 3000 -- if the queue is empty for three second, give
UPDATE and go away
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
-- Check for errors in Receive
SET @ErrorSave = @@ERROR ;
IF (@ErrorSave <> 0)
BEGIN
ROLLBACK TRANSACTION ;
SET @ErrorDesc = N'An error has occurred.' ;
END CONVERSATION @dialog
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;
INSERT INTO t_log VALUES(@ErrorDesc,NULL)
END
ELSE
-- Check for the End Dialog message.
If (@message_type <> 2) -- End dialog message
BEGIN
-- Log Message in Database
INSERT INTO t_log VALUES('Successfully
Received',@message_body);
-- Send the message back to the sender.
SET @message_body = '<msg> i got the message
</msg>';
SEND ON CONVERSATION @dialog -- send it back on the
dialog we received the message on
MESSAGE TYPE SendMessageType -- Must always
supply a message type
(@message_body); -- the message contents
are XML
END CONVERSATION @dialog
END
ELSE
BEGIN
-- End Conversation and Notify other side of the
conversation
END CONVERSATION @dialog
END
-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue and the response
-- wouldn't be sent.
commit transaction
end
GO
Configuring Service Broker on localsb_db1
as follow,
-- Create a Type for Send Message type
CREATE MESSAGE TYPE SendMessageType
VALIDATION = WELL_FORMED_XML;
-- Create a Type for Receive Message Type
CREATE MESSAGE TYPE ReceiveMessageType
VALIDATION = WELL_FORMED_XML;
-- Create Contract to be used
-- you can use SENT BY ANY and use a single message instead
-- however for the sake of showing the contract functionality
-- i created two messsage types that can be sent by each
CREATE CONTRACT [MainContract]
(
[SendMessageType] SENT BY INITIATOR,
[ReceiveMessageType] SENT BY TARGET
);
-- Create a QUEUE
CREATE QUEUE QUEUE1
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = OnReceiveMessage,
MAX_QUEUE_READERS = 5,
Execute AS dbo') ;
-- Create a Service to be used
CREATE SERVICE SERVICE1
ON QUEUE [QUEUE1]
(
[MainContract]
);
Database name: localsb_db2
-- creating localsb_db1 as the first database
create database localsb_db2
WITH TRUSTWORTHY ON;
GO
use localsb_db2;
GO
-- Create Master Key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'hellopassword';
GO
-- Creating Database table to be used for logging data
CREATE TABLE [dbo].[t_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_t_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Stored Procedure that will handle the receiving of Messages
CREATE PROCEDURE [dbo].[OnReceiveMessage]
AS
declare @message_type int
declare @dialog uniqueidentifier,
@ErrorSave INT,
@ErrorDesc NVARCHAR(100),
@message_body XML;
while (1 = 1)
begin
begin transaction
-- Receive the next available message from the queue
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message
received
@message_body=message_body, -- the message
contents
@dialog = conversation_handle -- the identifier
of the dialog this message was received on
FROM QUEUE2
), TIMEOUT 3000 -- if the queue is empty for three second, give
UPDATE and go away
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
-- Check for errors in Receive
SET @ErrorSave = @@ERROR ;
IF (@ErrorSave <> 0)
BEGIN
ROLLBACK TRANSACTION ;
SET @ErrorDesc = N'An error has occurred.' ;
END CONVERSATION @dialog
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;
INSERT INTO t_log VALUES(@ErrorDesc,NULL)
END
ELSE
-- Check for the End Dialog message.
If (@message_type <> 2) -- End dialog message
BEGIN
-- Log Message in Database
INSERT INTO t_log VALUES('Successfully
Received',@message_body);
-- Send the message back to the sender.
SET @message_body = '<msg> i got the message
</msg>';
SEND ON CONVERSATION @dialog -- send it back on the
dialog we received the message on
MESSAGE TYPE ReceiveMessageType -- Must
always supply a message type
(@message_body); -- the message contents
are XML
END
ELSE
BEGIN
-- End Conversation and Notify other side of the
conversation
END CONVERSATION @dialog
END
-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue and the response
-- wouldn't be sent.
commit transaction
end
GO
Configuring Service Broker on localsb_db2
as follow,
-- Create a Type for Send Message type
CREATE MESSAGE TYPE SendMessageType
VALIDATION = WELL_FORMED_XML;
-- Create a Type for Receive Message Type
CREATE MESSAGE TYPE ReceiveMessageType
VALIDATION = WELL_FORMED_XML;
-- Create Contract to be used
-- you can use SENT BY ANY and use a single message instead
-- however for the sake of showing the contract functionality
-- i created two messsage types that can be sent by each
CREATE CONTRACT [MainContract]
(
[SendMessageType] SENT BY INITIATOR,
[ReceiveMessageType] SENT BY TARGET
);
-- Create a QUEUE
CREATE QUEUE QUEUE2
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = OnReceiveMessage,
MAX_QUEUE_READERS = 5,
Execute AS 'dbo') ;
-- Create a Service to be used
CREATE SERVICE SERVICE2
ON QUEUE [QUEUE2]
(
[MainContract]
);
Once all above has been setup successfully, my T-SQL to send message as
follow
Use localsb_db1
GO
-- Send a Message from service1 on localsb_db1 to
-- service2 on localsb_db2.
DECLARE @dialog_handle uniqueidentifier,
@msg XML
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract] ;
set @msg = '<name> This is Little Nick!!!</name>';
-- Now Sending a message note we are using
-- SendMessageType since it is defined in the contract
-- that it is to be sent only by initiater
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE SendMessageType (@msg)
I need help to communicate between two different database instances on
the different Server using SQL Server Service Broker ("SSB")
Hopefully, someone can show me how to do that.
Really need help
December 12, 2010 at 2:13 am
You need to setup endpoints.
The best Service Broker basics/walkthrough I've seen, by Adam Machanic:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2010 at 4:33 am
there are a couple of nice articles regarding SSB at SSC too.
A simple walk trough I've written can be found at:http://www.sqlservercentral.com/articles/Service+Broker/2897/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2010 at 4:35 am
Craig Farrell (12/12/2010)
You need to setup endpoints.The best Service Broker basics/walkthrough I've seen, by Adam Machanic:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Sir,
I've study the article. I've a question.
Let's say, my Database
1. localsb_db1 on 192.168.1.1
2. localsb_db2 on 192.168.1.2
I want to create SSB Endpoint as follow
CREATE ENDPOINT SSB_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 9998
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO
where I should run this scripts? On 192.168.1.1 or 192.168.1.2 or both?
December 12, 2010 at 11:29 am
Each instance, or server, needs its own endpoint.
You'll also need to generate the route on each side to the other, so you can send close dialog messages and the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2010 at 6:56 pm
tq sir. i'll try. will let you know if me facing any problem.
December 13, 2010 at 12:23 am
Little Nick (12/12/2010)
tq sir. i'll try. will let you know if me facing any problem.
Please do. I'm sorry if you feel I'm being vague or sending you off to other topics for no good reason, but I've found that SSB is one of those things that either you "get", or you don't. Once you get it, it makes sense. Until you do, all the sample code in the world can't help you.
I'm trying to help you "get" it. Let's hope it works. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2010 at 1:03 am
SSB is something you need to prepare !
Make a checklist and tick the items you actually perform, as you do it !
Off course, Adam Machanics articles are wonderful, and I certainly don't want to mimimize them for their content, but in my little article there are a set of scripts, pointing to each stage when discovering SSB and its topology.
Like Craig stated, once you get it with SSB, it can do a great job for you, until it fails, then you're pretty much on your own.
Prepare for troubleshooting !
Here are some scripts that may help you : http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 13, 2010 at 1:23 am
🙂
December 13, 2010 at 1:27 am
Also keep in mind there may be some firewall configuration stuff to perform on both sides !
(allow the port numbers)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 13, 2010 at 3:45 am
Sir,
Based on Adam Mechanics article, http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
The sample saying that using port 9998.
It's ok If I'm using port 8221? This port is working properly.
FYI, my port 9998 is not working.
December 13, 2010 at 5:12 am
If that one is free on your server, that should be ok.
However, I must admit, I haven't found a list of "reserved ports" yet.
There must be some kind of convention, but I don't have it.
I'm not a network/firewall specialist.
What's in a number ?
I my examples I used 55552. I guess that should be high enough to avoid any "reserved" list.
cfr sqlserver error numbers.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2010 at 9:58 pm
Craig Farrell (12/12/2010)
You need to setup endpoints.The best Service Broker basics/walkthrough I've seen, by Adam Machanic:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Sir,
Now, I can communicate between 2 different database server on the different server.
My code as follow,
On Server 188.188.188.100
USE master
------------------------------------------------------------------------------
-- SET UP TRANSPORT SECURITY
------------------------------------------------------------------------------
-- drop receiver endpoint
IF EXISTS(SELECT * FROM sys.endpoints WHERE NAME = N'ServiceBrokerEndPoint')
DROP ENDPOINT ServiceBrokerEndPoint
-- drop existing receiver certificate
IF EXISTS(SELECT * FROM sys.certificates WHERE NAME = N'CertificateAuditDataReceiver')
DROP CERTIFICATE CertificateAuditDataReceiver
-- drop master key
IF EXISTS(SELECT * FROM sys.symmetric_keys WHERE NAME = N'##MS_DatabaseMasterKey##')
DROP MASTER KEY
GO
-- create a master key the for master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Put_Your_Custom_Password_For_Receivers_Master_DB_Here'
GO
-- create certificate for the service broker TCP endpoint for secure communication
-- between servers
CREATE CERTIFICATE CertificateAuditDataReceiver
WITH
-- BOL: The term subject refers to a field in the metadata of
--the certificate as defined in the X.509 standard
SUBJECT = 'CertAuditDataReceiver',
-- set the start date
START_DATE = '01/01/2007',
-- set the expiry data
EXPIRY_DATE = '01/01/2010'
-- enables the certifiacte for service broker initiator
ACTIVE FOR BEGIN_DIALOG = ON
GO
-- save certificate to a file
-- copy this file to all servers whose databases will be audited so we can restore it there
-- and thus enable the secure connection
BACKUP CERTIFICATE CertificateAuditDataReceiver
TO FILE = 'c:\CertificateAuditDataReceiver.cer'
GO
-- create endpoint which will be used to send audited data to the
-- MasterAuditServer ServiceBrokerEndPoint
CREATE ENDPOINT ServiceBrokerEndPoint
-- set endpoint to activly listen for connections
STATE = STARTED
-- set it for TCP traffic only since service broker supports only TCP protocol
-- by convention, 4022 is used but any number between 1024 and 32767 is valid.
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER
(
-- authenticate connections with our certificate
AUTHENTICATION = CERTIFICATE CertificateAuditDataReceiver,
-- default is REQUIRED encryption but let's just set it to SUPPORTED
-- SUPPORTED means that the data is encrypted only if the
-- opposite endpoint specifies either SUPPORTED or REQUIRED.
ENCRYPTION = SUPPORTED
)
GO
-- finally grant the connect permissions to public
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint TO PUBLIC
USE master
-- one audit database and table for all
IF DB_ID('MasterAuditDatabase') IS NOT NULL
DROP DATABASE MasterAuditDatabase
CREATE DATABASE MasterAuditDatabase
GO
-- enable service broker
ALTER DATABASE MasterAuditDatabase SET ENABLE_BROKER
GO
USE MasterAuditDatabase
GO
-- get service broker guid for MasterAuditDatabase.
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.usp_SendAuditData stored procedure
-- it's '8A9C7C9A-C37F-4060-A1FE-7E451EF0F41C' for my MasterAuditDatabase
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()
GO
IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
DROP TABLE dbo.MasterAuditTable
GO
-- Master Audit Table
CREATE TABLE dbo.MasterAuditTable
(
Id BIGINT IDENTITY(1,1),
SourceServer sysname NOT NULL,
SourceDB sysname NOT NULL,
SourceTable sysname NOT NULL,
UserID NVARCHAR(500) NOT NULL,
-- D = Delete, I = Insert, U = Update
DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
ChangedData XML NOT NULL,
ChangeDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(MAX) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_WriteAuditData
GO
-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
DECLARE @msgBody XML
DECLARE @dlgId uniqueidentifier
WHILE(1=1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- insert messages into audit table one message at a time
;RECEIVE top(1)
@msgBody= message_body,
@dlgId= conversation_handle
FROMdbo.TargetAuditQueue
-- exit when the whole queue has been processed
IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
BREAK;
END
SELECT @msgBody, @dlgId
DECLARE @SourceServer sysname, @SourceDB sysname, @SourceTable sysname,
@user-id NVARCHAR(500), @DMLType CHAR(1), @ChangedData XML
-- xml datatype and its capabilities rock
SELECT@SourceServer = T.c.query('/AuditMsg/SourceServer').value('.[1]', 'sysname'),
@SourceDB = T.c.query('/AuditMsg/SourceDb').value('.[1]', 'sysname'),
@SourceTable = T.c.query('/AuditMsg/SourceTable').value('.[1]', 'sysname'),
@user-id = T.c.query('/AuditMsg/UserId').value('.[1]', 'NVARCHAR(50)'),
@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
@ChangedData = T.c.query('*')
FROM@msgBody.nodes('/AuditMsg/ChangedData') T(c)
INSERT INTO dbo.MasterAuditTable(SourceServer, SourceDB, SourceTable, UserID, DMLType, ChangedData)
SELECT @SourceServer, @SourceDB, @SourceTable, @user-id, @DMLType, @ChangedData
-- No need to close the conversation because auditing never ends
-- you can end conversations if you want periodicaly with a scheduled job
-- END CONVERSATION @dlgId WITH CLEANUP
IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- insert error into the AuditErrors table
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECTERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @msgBody
DECLARE @errorId BIGINT, @dbName nvarchar(128)
SELECT @errorId = scope_identity(), @dbName = DB_NAME()
RAISERROR (N'Error while receiving Service Broker message. Error info can be found in ''%s.dbo.AuditErrors'' table with id: %I64d', 16, 1, @dbName, @errorId) WITH LOG;
END CATCH;
END
END
GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = N'//Audit/DataWriter')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = N'TargetAuditQueue')
DROP QUEUE dbo.TargetAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = N'//Audit/Contract')
DROP CONTRACT [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE NAME = N'//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
IF EXISTS(SELECT * FROM sys.routes WHERE NAME = 'RouteDataReceiver')
DROP ROUTE RouteDataReceiver
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the queue to run the usp_WriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = usp_WriteAuditData,-- sproc to run when the queue receives a message
MAX_QUEUE_READERS = 50,-- max concurrently executing instances of sproc
EXECUTE AS 'dbo' );
-- create a target service that will accept inbound audit messages
CREATE SERVICE [//Audit/DataWriter]
AUTHORIZATION dbo -- set the owner to dbo
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
-- create service with transport and name of the machine in the address address
GRANT SEND ON SERVICE::[//Audit/DataWriter] TO PUBLIC
GO
-- Create Transport Route
CREATE ROUTE [RouteDataReceiver]
WITH ADDRESS = N'TRANSPORT'
On Server 188.188.188.188
USE master
------------------------------------------------------------------------------
-- SET UP TRANSPORT SECURITY
------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM sys.endpoints WHERE NAME = 'ServiceBrokerEndPoint')
DROP ENDPOINT ServiceBrokerEndPoint
IF EXISTS(SELECT * FROM sys.certificates WHERE NAME = 'CertificateAuditDataReceiver')
DROP CERTIFICATE CertificateAuditDataReceiver
IF EXISTS(SELECT * FROM sys.certificates WHERE NAME = 'CertificateAuditDataSender')
DROP CERTIFICATE CertificateAuditDataSender
IF EXISTS(SELECT * FROM sys.server_principals WHERE NAME = 'LoginAuditDataSender')
DROP LOGIN LoginAuditDataSender
IF EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'UserAuditDataSender')
DROP USER UserAuditDataSender
IF EXISTS(SELECT * FROM sys.symmetric_keys WHERE NAME = '##MS_DatabaseMasterKey##')
DROP MASTER KEY
-- create the login that will be used to send the audited data through the Endpoint
CREATE LOGIN LoginAuditDataSender WITH PASSWORD = 'Login_Audit_DataSender_Password'
GO
-- Create a user for our login
CREATE USER UserAuditDataSender FOR LOGIN LoginAuditDataSender
GO
-- create a master key the for master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Put_Your_Custom_Password_For_Senders_Master_DB_Here'
GO
-- create certificate for the service broker TCP endpoint for secure communication
-- between servers
CREATE CERTIFICATE CertificateAuditDataSender
WITH
-- BOL: The term subject refers to a field in the metadata of
--the certificate as defined in the X.509 standard
SUBJECT = 'CertAuditDataSender',
-- set the start date
START_DATE = '01/01/2007',
-- set the expiry data
EXPIRY_DATE = '01/01/2010'
-- enables the certifiacte for service broker initiator
ACTIVE FOR BEGIN_DIALOG = ON
GO
-- copy the certificate create on the master target server
-- to c:\ disk and recreate it here with the data sender user authorization
CREATE CERTIFICATE CertificateAuditDataReceiver
AUTHORIZATION UserAuditDataSender
FROM FILE = 'c:\CertificateAuditDataReceiver.cer'
GO
-- create endpoint which will be used to send audited data to the
-- MasterAuditServer
CREATE ENDPOINT ServiceBrokerEndPoint
-- set endpoint to activly listen for connections
STATE = STARTED
-- set it for TCP traffic only since service broker supports only TCP protocol
-- by convention, 4022 is used but any number between 1024 and 32767 is valid.
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER
(
-- authenticate connections with our certificate
AUTHENTICATION = CERTIFICATE CertificateAuditDataSender,
-- default is REQUIRED encryption but let's just set it to SUPPORTED
-- SUPPORTED means that the data is encrypted only if the
-- opposite endpoint specifies either SUPPORTED or REQUIRED.
ENCRYPTION = SUPPORTED
)
GO
-- finally grant the connect permissions to login for the endpoint
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint TO LoginAuditDataSender
USE master
IF DB_ID('TestDb1') IS NOT NULL
DROP DATABASE TestDb1
CREATE DATABASE TestDb1
GO
-- enable service broker
ALTER DATABASE TestDb1 SET ENABLE_BROKER
GO
USE TestDb1
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.routes WHERE NAME = 'RouteDataSender')
DROP ROUTE RouteDataSender
IF EXISTS(SELECT * FROM sys.services WHERE NAME = N'tcp://188.188.188.188:4022/TestDb1/Audit/DataSender')
DROP SERVICE [tcp://188.188.188.188:4022/TestDb1/Audit/DataSender]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = N'InitiatorAuditQueue')
DROP QUEUE InitiatorAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = N'//Audit/Contract')
DROP CONTRACT [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE NAME = N'//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a route on which the messages will be sent to receiver
CREATE ROUTE RouteDataSender
AUTHORIZATION dbo
WITH
-- target server's service to which the data will be sent
SERVICE_NAME = '//Audit/DataWriter',
-- target server's MasterAuditDatabase Service Broker id
-- (change it to yours and remove < and >)
BROKER_INSTANCE = <'1BB07DD1-1FBF-4CB4-BA1C-280E950077F6'>,
-- IP and PORT of the target server
ADDRESS = 'TCP://188.188.188.100:4022'
GO
GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the initiator queue
CREATE QUEUE dbo.InitiatorAuditQueue
-- create an initiator service that will send audit messages to target service
CREATE SERVICE [tcp://188.188.188.188:4022/TestDb1/Audit/DataSender]
AUTHORIZATION dbo
ON QUEUE dbo.InitiatorAuditQueue -- no contract means service can only be the initiator
-- create service with IP and PORT of the initiator (this) server
GRANT SEND ON SERVICE::[tcp://188.188.188.188:4022/TestDb1/Audit/DataSender] TO PUBLIC
GO
-- drop support objects
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
DROP TABLE dbo.AuditDialogs
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_SendAuditData
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(MAX) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
-- Table that will hold dialog id for database
-- These dialogs will be reused.
CREATE TABLE dbo.AuditDialogs
(
DbId INT NOT NULL,
DialogId UNIQUEIDENTIFIER NOT NULL
)
GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
@AuditedData XML
)
AS
BEGIN
------------------------------------------------------
-- CHECK INITIATOR QUEUE FOR ERRORS IF NEEDED
------------------------------------------------------
BEGIN TRY
DECLARE @dlgId UNIQUEIDENTIFIER
-- Check if our database already has a dialog id that was previously used
-- if it does reuse the conversation
SELECT@dlgId = DialogId
FROMdbo.AuditDialogs
WHEREDbId = DB_ID()
-- if we're reusing a dialog conversation then
-- check if it is in a good state for conversation ...
IF @dlgId IS NOT NULL
AND NOT EXISTS(SELECT*
FROMsys.conversation_endpoints
WHEREconversation_handle = @dlgId AND state IN ('SO', 'CO'))
BEGIN
-- ... if it isn't then delete it from our saved dialogs table ...
DELETE
FROMdbo.AuditDialogs
WHEREDbId = DB_ID() AND DialogId = @dlgId
-- ... optionally you can end the conversation here,
-- but it is better to end it from target server
-- END CONVERSATION @dlgId WITH CLEANUP
-- ... and set it to null to create a new dialog
SELECT@dlgId = NULL
END
IF @dlgId IS NULL
BEGIN
-- Begin the dialog, with the new Id
BEGIN DIALOG CONVERSATION @dlgId
FROM SERVICE [tcp://188.188.188.188:4022/TestDb1/Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
-- this is a MasterAuditDatabase Service Broker Id
-- (change it to yours and remove < and >)
<'1BB07DD1-1FBF-4CB4-BA1C-280E950077F6'>
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
INSERT INTO dbo.AuditDialogs(DbId, DialogId)
SELECTDB_ID(), @dlgId
END
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/Message] (@AuditedData)
END TRY
BEGIN CATCH
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECTERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
DECLARE @errorId BIGINT, @dbName nvarchar(128)
SELECT @errorId = scope_identity(), @dbName = DB_NAME()
RAISERROR (N'Error while sending Service Broker message. Error info can be found in ''%s.dbo.AuditErrors'' table with id: %I64d', 16, 1, @dbName, @errorId) WITH LOG;
END CATCH
END
------------------------------------------------------------------------
-- S A M P L E T A B L E W I T H A U D I T I N G
------------------------------------------------------------------------
GO
-- Create Sample Table
IF OBJECT_ID('Person') IS NOT NULL
DROP TABLE Person
GO
CREATE TABLE Person
(
ID INT PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
DateOfBirth SMALLDATETIME
)
-- Create Trigger that will audit data
GO
IF OBJECT_ID ('trgPersonAudit','TR') IS NOT NULL
DROP TRIGGER trgPersonAudit
GO
CREATE TRIGGER dbo.trgPersonAudit
ON Person
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @auditBody XML
DECLARE @DMLType CHAR(1)
-- after delete statement
IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SELECT@auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
@DMLType = 'D'
END
-- after update or insert statement
ELSE
BEGIN
SELECT@auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
-- after update statement
IF EXISTS (SELECT * FROM deleted)
SELECT @DMLType = 'U'
-- after insert statement
ELSE
SELECT@DMLType = 'I'
END
-- get table name dynamicaly but
-- for performance this should be changed to constant in every trigger like:
-- SELECT@tableName = 'Person'
DECLARE @tableName sysname
SELECT@tableName = tbl.name
FROMsys.tables tbl
JOIN sys.triggers trg ON tbl.[object_id] = trg.parent_id
WHEREtrg.[object_id] = @@PROCID
SELECT @auditBody =
'<AuditMsg>
<SourceServer>' + @@servername + '</SourceServer>
<SourceDb>' + DB_NAME() + '</SourceDb>
<SourceTable>' + @tableName + '</SourceTable>
<UserId>' + SUSER_SNAME() + '</UserId>
<DMLType>' + @DMLType + '</DMLType>
<ChangedData>' + CAST(@auditBody AS NVARCHAR(MAX)) + '</ChangedData>
</AuditMsg>'
-- Audit data asynchrounously
EXEC dbo.usp_SendAuditData @auditBody
GO
-- we want this trigger to fire last for each command so that if there are other triggers
-- that update the table they finish their job before auditing
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'delete'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'insert'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'update'
1st scenario
When I execute T-SQL as follow
USE TestDb1
-- test if it works
INSERT INTO dbo.Person
SELECT 1, 'James', 'Cameron', GETDATE() - 1000 UNION ALL
SELECT 2, 'Andy', 'Serkis', GETDATE() - 100 UNION ALL
SELECT 3, 'Michael', 'Jackson', GETDATE() - 10 UNION ALL
SELECT 4, 'Dennis', 'Mitchel', GETDATE() - 1
When 2 server is online. My result as follow,
1. Person table on Server 188.188.188.188 having row as expected.
2. MasterAudit Table on Server 188.188.188.100 having row as expected.
2nd scenario
When I execute T-SQL as follow
USE TestDb1
-- test if it works
INSERT INTO dbo.Person
SELECT 1, 'James 2', 'Cameron 2', GETDATE() - 1000 UNION ALL
SELECT 2, 'Andy 2', 'Serkis 2', GETDATE() - 100 UNION ALL
SELECT 3, 'Michael 2', 'Jackson 2', GETDATE() - 10 UNION ALL
SELECT 4, 'Dennis 2', 'Mitchel 2', GETDATE() - 1
When Server 188.188.188.188 is online and
Server 188.188.188.100 is offline. My result as follow,
1. Person table on Server 188.188.188.188 having row as expected.
2. But MasterAudit Table on Server 188.188.188.100 not having row as expected.
My question as follow,
1. How to change my code to make it my 2nd scenario both server not having row as expected if 1 server is online and 1 server is offline.
2. As a result, when 188.188.188.100 is online back, both server having row as expected.
Need help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply