Using service broker to send table updates to another DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Damon Allison - 10 Jan 2006 22:14 GMT

Hello,

We are trying to determine how to alleviate performance bottlenecks on
our OLTP database. Since about 80% of our queries are "reporting" type
queries, we are going to push OLTP data to a new "reporting-friendly"
type of database.

As updates (insert/update/delete) operations hit a table, we want to
update our reporting database. Typically we could use replication,
however we want to massage/transform the data before going to this
reporting database. A rather trivial example might be concatenating
fields :

create table person (
    id int,
    firstname varchar(50),
    lastname varchar(50)
)

While our reporting database may be defined as:

create table person (
    id int,
    name varchar(100)
)

As an insert/update/delete statement hits, we want to capture that
change and replay on the reporting side.

I am looking at service broker because it provides the async, guaranteed
message passing we could potentially use to send updates to the
reporting database. Perhaps we could have a trigger that is responsible
for sending a message via service broker? Would service broker work in
this scenario? Any other alternatives (event notification, notification
classes?) that could provide what we are looking to do?

Thanks,
Damon Allison

Reply to this Message

Remus Rusanu [MSFT] - 10 Jan 2006 23:00 GMT

Service Broker could be used to create some form of trigger based
replication, but sounds that what you need is better achieved with database
snapshots, see http://msdn2.microsoft.com/en-us/library/ms175876.aspx

How long of a delay between OLTP state and reporting is acceptable? I.e. if
the report runs on a 1 hour delayed snapshot, is it acceptable?
Do you need transactional replication? I.e. is acceptable for the reporting
database to see a state that on the source database would correspond to a
partially commited transaction?
Any plan to move the reporting database on a separate hosting machine?
Any plan to have consolidation of multiple OLTP databases into one single
reporting database?

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

> Hello,
>
[quoted text clipped - 34 lines]
> Thanks,
> Damon Allison

Reply to this Message

Damon Allison - 11 Jan 2006 14:03 GMT

Thank you Reums for your reply. Comments inline.

> Service Broker could be used to create some form of trigger based
> replication, but sounds that what you need is better achieved with database
> snapshots, see http://msdn2.microsoft.com/en-us/library/ms175876.aspx

The idea is the reporting database would be near real-time to the main
OLTP database. Snapshots sound rather drastic in this case. Also, how
would we transform data for reporting purposes as mentioned in my post?

> How long of a delay between OLTP state and reporting is acceptable? I.e. if
> the report runs on a 1 hour delayed snapshot, is it acceptable?

The desired delay is near real time (< 1 min). The thought process is
this : 80% of the queries issued against our database are SELECT types
with multiple WHERE constraints that tend to require more indexing. By
separating out these queries into some form of "OLTP Assist" or
"reporting" database, we can keep the OLTP database fast and index the
"reporting" database for more complex select statements.

> Do you need transactional replication? I.e. is acceptable for the reporting
> database to see a state that on the source database would correspond to a
> partially commited transaction?

Yes, transactional replication is required.

> Any plan to move the reporting database on a separate hosting machine?

Yes, we would setup the reporting database on a different physical machine.

> Any plan to have consolidation of multiple OLTP databases into one single
> reporting database?

Not at this time, but the possibility is there.

Thank you,
Damon

Reply to this Message

Remus Rusanu [MSFT] - 12 Jan 2006 00:58 GMT

See this thread I answered some time ago on a similar question:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm
/thread/6c7a3f6ae3126b1d/06df8654a4e975a3

Even though is possible to obtain a solution that pushes the updates using
Service Broker, by simply adding an insert/update/delete trigger on the
table that sends INSERTED/DELETED pseudo-table content on a dialog message,
is more difficult to come up with a solution that maintains transactional
consistency. Is easy to apply all the updates from one transaction on the
replica and keep the transaction boundary. To achieve this simply send all
updates from one transaction (that is one @@spid) on the same dialog and
send some sort of transaction id with each message for the target to
recognize the transaction boundaries. But is more difficult to apply all
transactions in the same order as they happened on the original database.
One way to achieve the same order would be to send all updates on the same
dialog, but this would serialize all transactions on the original database
as they would try to acquire the lock on this dialog, in order to send the
updates.

Perhaps is obvious, but I will call out that w/o preserving the order in
which updates are applied, one could end up with the situation where the
original database executed 'insert into account (key, amount) values (1,
1000)' then 'update account set amount=500 where key=1', but the replica
executes them in the reverse order. The original database will have one
record with amount 500, the replica will have one record with amount 1000
(since the update was a no-op).

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

> Thank you Reums for your reply. Comments inline.
>
[quoted text clipped - 35 lines]
> Thank you,
> Damon

Reply to this Message