Using Service Broker instead of Replication

During my SQL Service Broker sessions I talk about using SQL Service Broker as a replacement for SQL Replication.  But I’ve never really documented how to do this other than bits and pieces here and there.  So here I’m going to try and spell out how I put a system like this together for an auto finance company that I used to work for.

Why Service Broker

Before I get into the code a little reasoning behind why we went with SQL Service Broker over SQL Replication is probably good to talk about.

The main reason is that we wanted to be able to do ETL on the data as is moved from the production OLTP database to the reporting database.  We also wanted the ability to easily scale the feed from one to many reporting databases with the reporting servers being in different sites.

Tables

Below you’ll find some SQL Code that we’ll use to create some sample tables.  In our OLTP database we have two tables LoanApplication and Customer, and in our Reporting database we have a single table LoanReporting.  As data is inserted or updated in the LoanApplication and Customer table that data will then be packaged up into an XML document and sent to the reporting database.  In the case of my sample code here everything is on one server, but the databases could be easily enough moved to separate servers.

[sql]IF EXISTS (SELECT * FROM sys.databases WHERE name = ‘Sample_OLTP’)
DROP DATABASE Sample_OLTP
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = ‘Sample_Reporting’)
DROP DATABASE Sample_Reporting
GO

CREATE DATABASE Sample_OLTP
CREATE DATABASE Sample_Reporting
GO

ALTER DATABASE Sample_OLTP SET NEW_BROKER
ALTER DATABASE Sample_Reporting SET NEW_BROKER
GO
ALTER DATABASE Sample_OLTP SET TRUSTWORTHY ON
ALTER DATABASE Sample_Reporting SET TRUSTWORTHY ON
GO

USE Sample_OLTP
GO
CREATE TABLE LoanApplication
(ApplicationId INT IDENTITY(1,1),
CreateTimestamp DATETIME,
LoanAmount MONEY,
SubmittedOn DATETIME,
ApprovedOn DATETIME,
LoanStatusId INT,
PrimaryCustomerId INT,
CoSignerCustomerId INT)
GO
CREATE TABLE Customer
(CustomerId INT IDENTITY(1,1).
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(255))
GO
USE Sample_Reporting
GO
CREATE TABLE LoanReporting
(ApplicationId INT,
CreateTimestamp DATETIME,
LoanAmount MONEY,
SubmittedOn DATETIME,
ApprovedOn DATETIME,
LoanStatusId INT,
PrimaryCustomerId INT,
PrimaryFirstName VARCHAR(50),
PrimaryLastName VARCHAR(50),
PrimaryEmailAddress VARCHAR(255),
CoSignerCustomerId INT,
CoSignerFirstName VARCHAR(50),
CoSignerLastName VARCHAR(50),
CoSignerEmailAddress VARCHAR(255))
GO[/sql]

Service Broker Objects

With this system I use a single pair of service broker queues to handle all the data transfer. This way transactional consistency can be maintained as the data flows in.  These SQL Service Broker objects should be created in both the Sample_OLTP and the Sample_Reporting database..

[sql]CREATE MESSAGE TYPE ReplData_MT
GO
CREATE CONTRACT ReplData_Ct
(ReplData_MT SENT BY ANY)
GO
CREATE QUEUE ReplData_Source_Queue
GO
CREATE QUEUE ReplData_Destination_Queue
GO
CREATE SERVICE ReplData_Source_Service
ON QUEUE ReplData_Source_Queue
(ReplData_Ct)
GO
CREATE SERVICE ReplData_Destination_Service
ON QUEUE ReplData_Destination_Queue
(ReplData_Ct)
GO[/sql]

The Routes

In the OLTP database you create a route like this (just change the BROKER_INSTANCE to match your server).
[sql]CREATE ROUTE ReplData_Route
WITH SERVICE_NAME=’ReplData_Destination_Service’,
BROKER_INSTANCE=’566C7F7A-9373-460A-8BCC-5C1FD4BF49C9′,
ADDRESS=’LOCAL'[/sql]

In the reporting database you create a route like this (just change the BROKER_INSTANCE to math your server).

[sql]CREATE ROUTE ReplData_Route
WITH SERVICE_NAME=’ReplData_Source_Service’,
BROKER_INSTANCE=’A4EC5E44-60AF-4CD3-AAAD-C3D467AC682E’,
ADDRESS=’LOCAL'[/sql]

Stored Procedures on the OLTP Database

In the OLTP database we need just a single stored procedure.  This stored procedure will handle the sending of the message so that we don’t have to put that same code in each table.

[sql]CREATE PROCEDURE SendTriggerData
@XMLData XML
AS
BEGIN
DECLARE @handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @handle
FROM SERVICE ReplData_Source_Service
TO SERVICE ‘ReplData_Destination_Service’
ON CONTRACT ReplData_Ct
WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @handle
MESSAGE TYPE ReplData_MT
(@XMLData)
END
GO[/sql]

OLTP database Triggers

The triggers that are on each table on the OLTP database are kept as small as possible so that we put as little additional load on the OLTP server as possible.  Obviously there will be some additional load on the OLTP database, but we want to keep that to a minimum.

[sql]CREATE TRIGGER t_LoanApplication ON LoanApplication
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @xml XML

SET @xml = (SELECT *
FROM inserted as LoanApplication
FOR XML AUTO, ROOT(‘root’))

EXEC SendTriggerData @xml
END
GO

CREATE TRIGGER t_Customer ON Customer
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @xml XML

SET @xml = (SELECT *
FROM inserted as Customer
FOR XML AUTO, ROOT(‘root’))

EXEC SendTriggerData @xml
END
GO[/sql]

Procedures on the Reporting Database

The reporting database is where the real work happens.  Here we take the XML document, identify which table the data is from then pass the XML document to a child procedure which then processes the data and updates the table.

[sql]CREATE PROCEDURE ProcessOLTPData_LoanApplication
@xml XML
AS
DECLARE  @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

UPDATE LoanReporting
SET ApplicationId=a.ApplicationId,
CreateTimestamp = a.CreateTimestamp,
LoanAmount=a.LoanAmount,
SubmittedOn=a.SubmittedOn,
ApprovedOn=a.ApprovedOn,
LoanStatusId=a.LoanStatusId
FROM OPENXML (@hDoc, ‘/root/LoanApplication’)
WITH (ApplicationId INT ‘@ApplicationId’,
CreateTimestamp DATETIME ‘@CreateTimestamp’,
LoanAmount MONEY ‘@LoanAmount’,
SubmittedOn DATETIME ‘@SubmittedOn’,
ApprovedOn DATETIME ‘@ApprovedOn’,
LoanStatusId INT ‘@LoanStatusId’,
PrimaryCustomerId INT ‘@PrimaryCustomerId’,
CoSignerCustomerId INT ‘@CoSignerCustomerId’) a
WHERE a.ApplicationId = LoanReporting.ApplicationId

INSERT INTO LoanReporting
(ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId)
SELECT ApplicationId, CreateTimestamp, LoanAmount, SubmittedOn, ApprovedOn, LoanStatusId, PrimaryCustomerId, CoSignerCustomerId
FROM OPENXML (@hDoc, ‘/root/LoanApplication’)
WITH (ApplicationId INT ‘@ApplicationId’,
CreateTimestamp DATETIME ‘@CreateTimestamp’,
LoanAmount MONEY ‘@LoanAmount’,
SubmittedOn DATETIME ‘@SubmittedOn’,
ApprovedOn DATETIME ‘@ApprovedOn’,
LoanStatusId INT ‘@LoanStatusId’,
PrimaryCustomerId INT ‘@PrimaryCustomerId’,
CoSignerCustomerId INT ‘@CoSignerCustomerId’) a
WHERE NOT EXISTS (SELECT * FROM LoanReporting WHERE a.ApplicationId = LoanReporting.ApplicationId)

EXEC sp_xml_removedocument @hDoc
GO
CREATE PROCEDURE PRocessOLTPData_Customer
@xml XML
AS
DECLARE  @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

UPDATE LoanReporting
SET PrimaryEmailAddress = EmailAddress,
PrimaryFirstName = FirstName,
PrimaryLastName = LastName
FROM OPENXML(@hDoc, ‘/root/Customer’)
WITH (CustomerId INT ‘@CustomerId’,
FirstName VARCHAR(50) ‘@FirstName’,
LastName VARCHAR(50) ‘@LastName’,
EmailAddress VARCHAR(255) ‘@EmailAddress’) a
WHERE PrimaryCustomerId = a.CustomerId

UPDATE LoanReporting
SET CoSignerEmailAddress = EmailAddress,
CoSignerFirstName = FirstName,
CoSignerLastName = LastName
FROM OPENXML(@hDoc, ‘/root/Customer’)
WITH (CustomerId INT ‘@CustomerId’,
FirstName VARCHAR(50) ‘@FirstName’,
LastName VARCHAR(50) ‘@LastName’,
EmailAddress VARCHAR(255) ‘@EmailAddress’) a
WHERE CoSignerCustomerId = a.CustomerId

EXEC sp_xml_removedocument @hDoc
GO

CREATE PROCEDURE ProcessOLTPData
AS
DECLARE @xml XML
DECLARE @handle UNIQUEIDENTIFIER, @hDoc INT

WHILE 1=1
BEGIN
SELECT @xml = NULL, @handle = NULL

WAITFOR (RECEIVE TOP (1) @handle=conversation_handle,
@xml=CAST(message_body AS XML)
FROM ReplData_Destination_Queue), TIMEOUT 1000

IF @handle IS NULL
BREAK

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

IF EXISTS (SELECT *
FROM OPENXML(@hDoc, ‘/root/LoanApplication’)
)
BEGIN
EXEC ProcessOLTPData_LoanApplication @xml
END

IF EXISTS (SELECT *
FROM OPENXML (@hDoc, ‘/root/Customer’)
)
BEGIN
EXEC PRocessOLTPData_Customer @xml
END

EXEC sp_xml_removedocument @hDoc

END CONVERSATION @handle
END
GO[/sql]

You can now set the queue to run the ProcessOLTPData Stored Procedure as an activation procedure and it will process the data as it comes in.

Hopefully someone finds this useful as a framework in their shop.  If you have any questions please feel free to post them here.

Denny

Share

9 Responses

  1. Hi Denny,

    I find this method very interesting. Just curious why you choose openxml instead of xml nodes to process data. Also what are the performance gains you have seen using this method, instead of using traditional replication, if any.

  2. Awesome stuff Denny. its just occurred to me that this would be a great way to solve the “pushing out” of reference data from the upcoming Master Data Services (in SQL Server R2) to the systems-of-record that, by definition, should reflect that same reference data.

    Its really got me thinkking…so thank you!

    -Jamie Thomson

  3. We have implemented this on an enterprise level application for Reporting. Although simular to this implementation, we used a CLR Table valued function written in C# to parse incoming messages and that is called from an activation stored procedure on the reporting database. This CLR UDF creates an executable sql statement that is executed within the activation SP. This way we were able to create a single generic means to parse and decode messages for any table in the source database. The source database prepares the messages via generic triggers across hundreds of tables.

    We have found this to be a great option in an environment that required multiple fail over systems.

  4. I think the SendTriggerData stored procedure needs to read “TO SERVICE ‘ReplData_Destination_[B]Service[/B]’”.

    Also, I’m not sure how to set a queue to run a stored procedure as an activation procedure. I should be able to figure it out with a bit of searching, but you might want to be more clear.

    Thanks for the great information.

  5. Really good food for thought. Thanks.

    I’d like to take this further and publish a data-stream acquired and stored in one Production table to several additional instances at the home office, from a Rackspace-hosted SQL cluster, via an ADDRESS = ‘TCP://…’ for each ROUTE. I’m thinking that additional security measures [I][B]may[/B][/I] need to be taken, despite the fact that the data are completely meaningless in themselves (tokens and numbers that only have meaning when parsed and interpreted internally). As I am a Service Broker utter novice, can you succinctly guide me to where additional, say encryption, would be warranted?

    The viability of this in place of really awkward MS Repl. across both the ‘net to local and as a replacement for the “project the ‘good’ data to an indexed view – replicate the view to another database as a table” involving over 800M rows and growing cannot be over-stressed…

    ANY help would be appreciated, for which “Thanks in advance”.

  6. SSNewbie,
    You’ll be happy to know that data sent between instances is already encrypted to protect the connection. When you setup the endpoints you use certificates which you exchange and import so that the connections between the machines are encrypted with SSL encryption (the same kind of encryption that web servers use).

    You could use Service Broker to replicate large amounts of data like that, with no problem. The latency, like with replication, will depend on the amount of data, and bandwidth available.

  7. Regarding this:  “You can now set the queue to run the ProcessOLTPData Stored Procedure as an activation procedure and it will process the data as it comes in.”

    What queue?  Which database?  
  8. Hi guys – I got this working.  A few tips/gotchas for anyone who needs it:

    1) 
    You need to get the service_broker_guid from the sys.databases table and put that in the broker_instance when creating the routes.  Do this for BOTH databases:

    Like this:
    SELECT service_broker_guid, * FROM sys.databases WHERE name LIKE ‘%databasename%’
       
    USE <DATABASENAME>
    go
     alter ROUTE ReplData_Route
     WITH SERVICE_NAME=’ReplData_Destination_Service’,
     BROKER_INSTANCE=’751DD202-FB95-4132-AE3E-26FDB2F11311′, — <– service_broker_guid from sys.databases
     ADDRESS=’LOCAL’ — if you’re running this script on same server as the target and source DB, this can be LOCAL

    2) I wasn’t sure which queue should get the activation, so created it on both, like below.  It’s working.  As I test more, I’ll figure out which one isn’t needed and delete it:

    USE <ReportingDB>
    go

    ALTER QUEUE [ReplData_Source_Queue]
    WITH
    STATUS = ON,
    ACTIVATION(
     STATUS = ON,
     PROCEDURE_NAME = [ProcessOLTPData],
     MAX_QUEUE_READERS = 1,
     EXECUTE AS SELF
    );
    ALTER QUEUE [ReplData_Destination_Queue]
    WITH
    STATUS = ON,
    ACTIVATION(
     STATUS = ON,
     PROCEDURE_NAME = [ProcessOLTPData],
     MAX_QUEUE_READERS = 1,
     EXECUTE AS SELF
    );

    3)  If trying to debug errors, query this view.  Has detailed error messages:
    sys.transmission_queue

    Anyway thanks for the awesome script.  Really came in helpful, as there aren’t many samples out there for using SB for data movement.

Leave a Reply to klb1970Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?