INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
Thanks so much for having a place for us propeller heads to hang out and chat.
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft SQL Server: Setup and Administration FAQ
|
Replication
|
Using Service Broker to update a Reporting Database in Real Time
Posted: 23 Feb 07
|
The purpose of this white paper is to describe how to use the Service Broker include in Microsoft SQL Server 2005 to create a real time reporting solution based on your production OLTP database. This paper will guide you step by step through the process.
For the purposes of this white paper it is assumed that the production database called DB1 exists on SQL01 and that the reporting database called DB2 exists on SQL02. Both servers are running Microsoft Windows Server 2003 and Microsoft SQL Server 2005. No specific service pack is required to use these features.
The goal of this solution are to provide a reporting database which is updated in real time, or near real time from the production OLTP database with no coding changes to the production code, or front end application and add minimal additional load to the production system. We also require delivery confirmation of the data change as well as ordered delivery. Based on all these requirements the Service Broker is an excellent method of delivery. Triggers on the required tables will make an excellent data collection method.
The data flow will work as follows. A user will make a data change in a table (INSERT, UPDATE or DELETE). That change will be captured via a trigger, the effected record or records will be bundled into an XML document and sent to a Service Broker Queue. The Service Broker will then transmit the message to the destination database on the Reporting Server where the XML data will be turned back into relational data and the required fields will be updated in the Reporting database.
The first thing that needs to be done is that both DB1 and DB2 need to have the Service Broker enabled. This is done via the ALTER DATABASE command.
CODEALTER DATABASE DB1
ENABLE_BROKER;
First we will create the end point. The end points can be created on any available TCP port. They do not need to have the same name or use the same port number, however long term management will be easier if they are the same. The end points should be created on both SQL01 and SQL02 before anything else is done.
CODE --> Create EndpointsCREATE ENDPOINT ReportingFeed_EndPoint
STATE=STARTED
AS TCP(LISTERER_PORT=1500, LISTENER_IP=ALL)
FOR SERVICE_BROKER (AUTHENTICATION=WINDOWS)
GO
CODE --> Message TypesCREATE MESSAGE TYPE [tcp://SQL01/DB1/ReportingFeed_MT] VALIDATION=NONE
GO
CREATE MESSAGE TYPE [tcp://SQL02/DB2/ReportingFeed_MT] VALIDATION=NONE
GO
CODE --> ContractCREATE CONTRACT [tcp://SQL01/DB1/ReportingFeed_Contract]
(
[tcp://SQL01/DB1/ReportingFeed_MT] SENT BY ANY,
[tcp://SQL02/DB2/ReportingFeed_MT] SENT BY ANY
)
GO
CODE --> QueuesCREATE QUEUE [tcp://SQL01/DB1/ReportingFeed_Queue] ON PRIMARY
GO
CREATE QUEUE [tcp://SQL02/DB2/ReportingFeed_Queue] ON PRIMARY
GO
CODE --> ServicesCREATE SERVICE [tcp://SQL01/DB1/ReportingFeed_Service]
ON QUEUE [tcp://SQL01/DB1/ReportingFeed_Queue]
(
[tcp://SQL01/DB1/ReportingFeed_Contract]
)
GO
CREATE SERVICE [tcp://SQL02/DB2/ReportingFeed_Service]
ON QUEUE [tcp://SQL02/DB2/ReportingFeed_Queue]
(
[tcp://SQL01/DB1/ReportingFeed_Contract]
)
GO
CODE --> RouteCREATE ROUTE [tcp://SQL01/DB1/ReportingFeed_Route]
WITH SERVICE_NAME = ‘tcp://SQL02/DB2/ReportingFeed_Service],
BROKER_INSTANCE = N’00000000-0000-0000-0000-000000000000’,
ADDRESS = N’TCP://SQL02:1500’
GO
CODE --> Message TypesCREATE MESSAGE TYPE [tcp://SQL01/DB1/ReportingFeed_MT] VALIDATION=NONE
GO
CREATE MESSAGE TYPE [tcp://SQL02/DB2/ReportingFeed_MT] VALIDATION=NONE
GO
CODE --> ContractsCREATE CONTRACT [tcp://SQL02/DB2/ReportingFeed_Contract]
(
[tcp://SQL01/DB1/ReportingFeed_MT] SENT BY ANY,
[tcp://SQL02/DB2/ReportingFeed_MT] SENT BY ANY
)
GO
CODE --> QueuesCREATE QUEUE [tcp://SQL01/DB1/ReportingFeed_Queue] ON PRIMARY
GO
CREATE QUEUE [tcp://SQL02/DB2/ReportingFeed_Queue] ON PRIMARY
GO
CODE --> ServicesCREATE SERVICE [tcp://SQL01/DB1/ReportingFeed_Service]
ON QUEUE [tcp://SQL01/DB1/ReportingFeed_Queue]
(
[tcp://SQL02/DB2/ReportingFeed_Contract]
)
GO
CREATE SERVICE [tcp://SQL02/DB2/ReportingFeed_Service]
ON QUEUE [tcp://SQL02/DB2/ReportingFeed_Queue]
(
[tcp://SQL02/DB2/ReportingFeed_Contract]
)
GO
CODE --> RightsGRANT CONTROL ON SERVICE :: [tcp://SQL02/DB2/ReportingFeed_Queue] TO PUBLIC
GO
CODE --> ROUTECREATE ROUTE [tcp://SQL02/DB2/ReportingFeed_Route]
WITH SERVICE_NAME = ‘tcp://SQL01/DB1/ReportingFeed_Service],
BROKER_INSTANCE = N’00000000-0000-0000-0000-000000000000’,
ADDRESS = N’TCP://SQL01:1500’
GO
CODE --> ssb_SendReportingMessageCREATE PROCEDURE ssb_SendReportingMessage
@message_body XML
AS
BEGIN
DECLARE @Handle_ID uniqueidentifier
BEGIN DIALOG CONVERSATION @Handle_ID
FROM SERVICE [tcp://SQL01/DB1/ReportingFeed_Service]
TO SERVICE [tcp://SQL02/DB2/ReportingFeed_Service]
ON CONTRACT [tcp://SQL02/DB2/ReportingFeed_Contract]
WITH ENCRYPTION=OFF;
SEND ON CONVERSATION @Handle_ID
MESSAGE TYPE [tcp://SQL02/DB2/ReportingFeed_MT]
(@message_body);
END
GO
Now create the triggers on the tables which need to be transferred. The Table T1 will be used for this example. When creating the triggers on the tables be sure to change the Trigger name, the object the trigger is being created on, as well as the first value of each select statement within the trigger. This value is used to tell the procedures on the SQL02 which table the data is coming from. The XML tags should not be changed unless you also modify the tags on SQL02 to match or the parser will not work. Because we are using the * wild card in the select statements column additions and drops from the table do not require modification of the triggers.
CODE --> trg_T1_IUD_ReportingFeedCREATE TRIGGER trg_T1_IUD_ReportingFeed ON T1
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @xmlData as XML
IF NOT EXISTS (SELECT * FROM inserted) -- The trigger is being fired for a delete and there for deleted need to be processed on the reporting database.
BEGIN
SET @xmlData = (SELECT ‘T1’ TableName, ‘D’ CommandType, *
FROM deleted
FOR XML RAW (‘Table’), ROOT(‘ROOT’))
END
ELSE -- An insert or update was sent, so we need to capture only the new data. The old data will already exist on the reporting system.
BEGIN
SET @xmlData = (SELECT ‘T1’, TableName, ‘I’ CommandType, *
FROM inserted
FOR XML RAW (‘Table’), ROOT(‘ROOT’)
END
END
GO
On SQL02 we now need to create a procedure to identify the name of the table that we are processing.
CODE --> asp_IsolateTableCREATE PROCEDURE asp_IsolateTable
@message_body XML,
@table_name sysname OUTPUT,
@CommandType nchar(1) OUTPUT
AS
BEGIN
DECLARE @hdoc int
exec sp_xml_preparedocument @hdoc OUTPUT, @message_body
SELECT DISTINCT @table_name = TableName, @CommandType = CommandType
FROM OPENXML(@hdoc, N’/ROOT/Table’)
WITH (TableName sysname ‘@TableName’,
CommandType nchar(1) ‘@CommandType’) a
Exec sp_xml_removedocument @hdoc
END
GO
CODE --> asp_ProcessTable_T1CREATE PROCEDURE asp_ProcessTable_T1
@message_body XML,
@CommandType nchar(1)
AS
BEGIN
DECLARE @hdoc int
exec sp_xml_preparedocument @hdoc OUTPUT, @message_body
IF @CommandType = ‘D’
BEGIN
/*Your code to either null the values or delete the record goes here. If this procedure is for the base table (ie. The parent table in the OLTP database then you should delete the record. If not then you should null the fields which come from this table.*/
DELETE FROM T1
WHERE T1.Column1 IN (SELECT Col1 FROM OPENXML(@hdoc, N’/ROOT/Table’)
WITH (Col1 int ‘@Col1) a)
END
IF @CommandType = ‘I’
BEGIN
/*Your code should first update and records which exist in the table, then insert any records which do not exist.*/
UPDATE T1
SET T1.Column4 = Col8,
T1.Column9 = Col2
FROM OPENXML(@hdoc, N’/ROOT/Table’)
WITH (Col1 int ‘@Col1’,
Col8 int ‘@Col8’,
Col2 int ‘@Col2’) a
WHERE T1.Column1 = Col1
INSERT INTO T1
(Column1, Column4, Column9)
SELECT Col1, Col8, Col2
FROM OPENXML(@hdoc, N’/ROOT/Table’)
WITH (Col1 int ‘@Col1’,
Col8 int ‘@Col8’,
Col2 int ‘@Col2’) a
WHERE NOT EXISTS (SELECT Column1 FROM T1 WHERE Column1 = Col1)
END
exec sp_xml_removedocument @hdoc
END
GO
CODE --> asp_ProcessQueueCREATE PROCEDURE asp_ProcessQueue AS
BEGIN
DECLARE @message_body as XML
DECLARE @Handle_ID as uniqueidentifier
DECLARE @table_name as sysname
DECLARE @CommandType as nchar(1)
WHILE 1=1 /*We process this within a never ending loop so that we can drain the queue on each run instead of having to launch the procedure one for each message.*/
BEGIN
WAITFOR (
RECEIVE TOP (1) @Handle_ID = conversation_handle,
@message_body=cast(message_body as xml)
From [tcp://SQL02/DB2/ReportingFeed_Queue]
), TIMEOUT 1000 /*If no message is received then we wait for 1 second. If no message arrives in that time continue to the next command which will break us out of the procedure so that we are not running the procedure for no reason wasting CPU and memory resources.*/
IF (@@ROWCOUNT = 0)
BREAK
END CONVERSATION @Handle_ID /*By ending the conversation we are telling SQL01 that we have removed the message from the queue and that it no longer needs to remember that the message exists.*/
IF @message_body IS NOT NULL /*Checking just in case. We don’t want to bother trying to process a message if it is null.*/
BEGIN
exec asp_IsolateTable @message_body=@message_body,
@table_name = @table_name OUTPUT,
@CommandType = @CommandType OUTPUT
IF @table_name IS NULL /*If there is no table name we need to notify the DBAs and give them the XML so that they can identify the problem and correct it.*/
BEGIN
Exec sp_send_dbmail @profile_name=’Your DB Mail Profile’,
@recipients=’YourDBAs@yourcompany.com’,
@subject=’Invalid XML received’,
@message=@message_body
BREAK
END
SET @table_name = ltrim(rtrim(@table_name)) /*Clean up, just in case the XML pads with white space.*/
If @table_name = ‘T1’
Exec asp_ProcessTable_T1 @message_body=@message_body, @CommandType=@CommandType
/*Repeat for each Table you are processing.*/
END
END
GO
CODE --> Alter QueueALTER QUEUE [tcp://SQL02/DB2/ReportingFeed_Queue]
WITH ACTIVATION
(STATUS=ON, PROCEDURE_NAME = dbo.asp_ProcessQueue, MAX_QUEUE_READERS=1, EXECUTE AS OWNER)
GO |
Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum
|
|
 |
|
Talk To Other Members
