How to Enqueue Messages With Service Broker in a Timely Fashion
Charles
instance. The problem is that when the database or network is down the
application has nowhere to put the data. To overcome this, I have decided to
save the data to a local instance and use Service Broker to move the data
from the local table to the remote table.
I have the mechanism and understanding now to create the SB objects on the
remote instance to receive the messages, but I'm not sure how to get the
data into the local queue.
What I am looking for is a timely method to remove rows from the local table
and put them into the queue, when SB will take over.
Can anyone suggest a method of doing this? What I imagine is some SQL Server
process that waits for a row to be inserted into the local table and then
moves it to the queue. It would then go back and wait for the next row, and
so on. If this were in a sproc, I don't know how it would be activated, and
the fact that it would run forever might be a problem too.
I should add that I am not looking for the method of enqueuing the data -
which is well-documented - but the method of looping on the local table
removing rows to the queue ad infinitum.
Any ideas welcome.
TIA
Charles
bob Simms
news:uNSfzJC#JHA....@TK2MSFTNGP04.phx.gbl...
> I have an application that puts data into a table in a remote database
> instance. The problem is that when the database or network is down the
> application has nowhere to put the data. To overcome this, I have decided
> to save the data to a local instance and use Service Broker to move the
> data from the local table to the remote table.
>
> I have the mechanism and understanding now to create the SB objects on the
> remote instance to receive the messages, but I'm not sure how to get the
> data into the local queue.
>
> What I am looking for is a timely method to remove rows from the local
> table and put them into the queue, when SB will take over.
>
Your best bet sounds like an insert trigger on the table, so that every time
rows are inserted, the trigger fires. In the body of the trigger there will
be a special in-memory table called INSERTED which will contain the rows
that have just been inserted into the table. You can then copy those into
an XML message and place them into the queue.
HTH
--
Bob Simms
Senior Learning Consultant
QA - transforming performance through learning
www.qa.com
Andrew J. Kelly
As Bob stated a trigger might be the best bet. But if you only want certain
rows put into the queue (IE: only ones inserted from a particular app etc.)
you may find that if you use a stored procedure to insert the rows that
might be a good place to put the data into the q.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Charles" <bl...@nowhere.com> wrote in message
news:uNSfzJC%23JHA...@TK2MSFTNGP04.phx.gbl...
Erland Sommarskog
Charles (bl...@nowhere.com) writes:
> I have an application that puts data into a table in a remote database
> instance. The problem is that when the database or network is down the
> application has nowhere to put the data. To overcome this, I have
> decided to save the data to a local instance and use Service Broker to
> move the data from the local table to the remote table.
>
> I have the mechanism and understanding now to create the SB objects on the
> remote instance to receive the messages, but I'm not sure how to get the
> data into the local queue.
>
> What I am looking for is a timely method to remove rows from the local
> table and put them into the queue, when SB will take over.
>
> Can anyone suggest a method of doing this? What I imagine is some SQL
> Server process that waits for a row to be inserted into the local table
> and then moves it to the queue. It would then go back and wait for the
> next row, and so on. If this were in a sproc, I don't know how it would
> be activated, and the fact that it would run forever might be a problem
> too.
save it to a table, and then queue it with Service Broker? Why not just
send it? Then Service Broker will store it its internal tables, until it
is delivered.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Charles
Hi Bob
A trigger was one thing I wondered about. My initial aim was to make this as
bolt-on as possible, so that meant not modifying any existing objects, but
as you have also suggested a trigger that makes me think that I should now
consider the option.
Thanks
Charles
"bob Simms" <bob....@qa.com> wrote in message
news:9967B6E8-B366-4CB5...@microsoft.com...
Charles
Hi Erland
I was trying to avoid changing the existing schema. If I accept that this is
a change to the original design then it does make most sense to simply write
to the queue and dispense with the local table idea. I don't actually have a
need for the local table, so I think I will head down this route and see
where it leads me.
Thanks
Charles
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C38EEB5E...@127.0.0.1...
Charles
Hi Andrew
In this case, the intention is to move every row to the remote database. As
I replied to Bob, my first thought was to avoid modifying existing objects;
including sprocs. However, the local table is filled by a sproc, so it would
be a straight-forward step to write to the queue as well.
Cheers
Charles
"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:ORb12YD%23JHA...@TK2MSFTNGP04.phx.gbl...
Charles
Hi Erland
A thought occurs to me. I might quite like to keep the data locally, in a
'visible' form, until I know it has been transferred successfully to the
remote table. I'm thinking that my existing sproc could continue to write
the data to a local table, and also put it into the queue. Then, when the
remote end has received the message and put the data into the remote table,
it sends a reply message containing the PK of the data it was sent. When the
reply gets back to the sender it can use the PK to remove the row from the
local table.
That might all seem unnecessarily complicated, and I don't know what happens
if the reply doesn't get back, if indeed that can ever happen? I imagine
that the delivery of the reply is guaranteed just like the outgoing message.
Is a reply actually necessary? The books and blogs I have read all show the
receiver sending a reply, but perhaps that is redundant if all messages are
guaranteed? Obviously, for my modified scheme to work, I would need the
reply, but then again if I go with your original suggestion perhaps I don't
need a reply, and that would make things run more quickly?
Charles
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C38EEB5E...@127.0.0.1...
Andrew J. Kelly
That is what service broker does for you (among other things). It is a
guaranteed delivery system so the messages won't get lost. There is no need
for an intermediate table, just place the messages in the queue and have
another service do the delivery and insert them on the remote server.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Charles" <bl...@nowhere.com> wrote in message
news:u1lw8lJ%23JHA...@TK2MSFTNGP02.phx.gbl...
Charles
Hi Andrew
The reason I thought of using an [intermediate] local table was so that I
could inspect the data in the normal way in the event that it can't be
transferred to the remote server. I haven't decided yet whether I will want
to do anything with it locally, but I can see that unless I have good reason
I should forego the local table.
Thanks
Charles
"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:O9Iue%23L%23JHA...@TK2MSFTNGP03.phx.gbl...
Tom Moreau
You can do a SELECT on a queue.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Charles" <bl...@nowhere.com> wrote in message
news:OgaGBHN%23JHA...@TK2MSFTNGP02.phx.gbl...
Charles
Hi Tom
I would get back the XML message though, wouldn't I? I realise I could then
'unformat' the message but it's not in what I think of as its native table
form. I admit, still doable though.
Charles
"Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
news:OB7irUN%23JH...@TK2MSFTNGP05.phx.gbl...
Tom Moreau
Well, SQL 2005 has XML as a native datatype, with associated methods, so
reconstituting the data in tabular form would be straightforward.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Charles" <bl...@nowhere.com> wrote in message
news:ObvwdKO%23JHA...@TK2MSFTNGP05.phx.gbl...
Erland Sommarskog
> A thought occurs to me. I might quite like to keep the data locally, in
> a 'visible' form, until I know it has been transferred successfully to
> the remote table. I'm thinking that my existing sproc could continue to
> write the data to a local table, and also put it into the queue. Then,
> when the remote end has received the message and put the data into the
> remote table, it sends a reply message containing the PK of the data it
> was sent. When the reply gets back to the sender it can use the PK to
> remove the row from the local table.
>
> That might all seem unnecessarily complicated, and I don't know what
> happens if the reply doesn't get back, if indeed that can ever happen? I
> imagine that the delivery of the reply is guaranteed just like the
> outgoing message.
Well, there is one reason the you would never get a reply back: it is
for some reason not possible to deliver the message on the other end.
Everytime your activation procedure retrieves and processes the message
there is an error (supposedly because of a bug in your code). Eventually
the queue is turned off, and someone decides to receive the posion message
into thin air to make the queue start running.
> Is a reply actually necessary? The books and blogs I have read all show
> the receiver sending a reply, but perhaps that is redundant if all
> messages are guaranteed? Obviously, for my modified scheme to work, I
> would need the reply, but then again if I go with your original
> suggestion perhaps I don't need a reply, and that would make things run
> more quickly?
My initial thinking was that a reply would not be needed, and certainly
skipping replies would be better for performance. But it would also mean
an increase in risk for data being dropped entirely.
But assuming that your activation routine would be very simple, the
risk for a bug occuring in production may be low, and thus you may
prefer to ignore it.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
Charles
In this particular case, there will be no constraints on the receiving table
that will stop the message form being stored. The only reason I can think
of, therefore, why the message could not be saved is disk full, which would
be rather fatal. There would be other alarm bells going off if that
happened, so perhaps I can ditch the reply.
Thanks
Charles
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C39EF629...@127.0.0.1...
Charles
Agreed. I think I have decided not to use a local table; just the queue.
Cheers
Charles
"Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
news:ObH8SvO%23JHA...@TK2MSFTNGP03.phx.gbl...
Dan Guzman
> Is a reply actually necessary? The books and blogs I have read all show
> the receiver sending a reply, but perhaps that is redundant if all
> messages are guaranteed? Obviously, for my modified scheme to work, I
> would need the reply, but then again if I go with your original suggestion
> perhaps I don't need a reply, and that would make things run more quickly?
You must never "fire and forget" with Service Broker in order to avoid
leaking conversation handles. When the target ends the conversation, an
EndDialog (or Error) message is send back to the initiator. The initiator
needs to issue an END CONVERSATION in response to clean things up.
A common pattern is to use an initiator queue activated proc for this
purpose. The initiator queue activated proc just issues an END CONVERSATION
in response to messages and optionally logs unexpected messages (e.g. Error)
to an error table for troubleshooting and to facilitate monitoring.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Charles" <bl...@nowhere.com> wrote in message
news:u1lw8lJ%23JHA...@TK2MSFTNGP02.phx.gbl...
Charles
Thanks for the reply. So, I need a reply to allow a conversation to be ended
at both ends, but what if the conversation never ends? In my scenario there
will be a continuous stream of messages from the initiator to the target,
forever; or at least until there is an exceptional situation, like the
database is taken off-line or it fails over or something like that. It seems
to me that the conversation shouldn't end in normal operation. Or, is a
conversation a one-message-only dialog? I had thought that the stream of
messages would all be part of the same conversation, or have I got that
wrong?
Charles
"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:%23Q4LnRk%23JHA...@TK2MSFTNGP04.phx.gbl...
Tom Moreau
It's possible to have a 1-message conversation, followed by END DIALOG. You
keep the conversation open only as long as necessary. For example, if you
called home one a day, each of those is a separate conversation and there is
no need to keep the phone off the hook.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Charles" <bl...@nowhere.com> wrote in message
news:ezhx2%23n%23JHA...@TK2MSFTNGP03.phx.gbl...
Charles
Hi Tom
Each message is distinct and stands alone. They are all on the same topic,
and relate to the same thing, as each is a real-time update of information
previously sent, and they can come at a rate of 10 per second, forever.
I suppose I am concerned that by striking up a new conversation 10 times per
second it will affect throughput.
At that rate of messaging, what would you say is the appropriate strategy?
Charles
"Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
news:Of8JGQo%23JHA...@TK2MSFTNGP03.phx.gbl...
Roger Wolter[MSFT]
If you are sending a stream of messages from one database to another, the
best practice would be to open a conversation and use it forever.
Conversations are persistent objects so unless some kind of error causes the
conversation to terminate you can use the same one for months as long as you
don't specify a lifetime. If you receive a conversation error message, just
end the one you are using and open another one. Some people have created
pools of conversations to handle high message volumes but one conversation
should handle 10 messages a second with no problem.
Opening a conversation and leaving it open doesn't violate the fire and
forget rule.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Charles" <bl...@nowhere.com> wrote in message
news:uzefhip%23JHA...@TK2MSFTNGP05.phx.gbl...
Charles
Hi Roger
Thanks, that clears things up.
Cheers
Charles
"Roger Wolter[MSFT]" <rwo...@online.microsoft.com> wrote in message
news:A0A9B573-1F92-49D3...@microsoft.com...
Dan Guzman
Hi, Charles.
> Thanks for the reply. So, I need a reply to allow a conversation to be
> ended at both ends, but what if the conversation never ends? In my
> scenario there will be a continuous stream of messages from the initiator
> to the target, forever; or at least until there is an exceptional
> situation, like the database is taken off-line or it fails over or
> something like that. It seems to me that the conversation shouldn't end in
> normal operation. Or, is a conversation a one-message-only dialog? I had
> thought that the stream of messages would all be part of the same
> conversation, or have I got that wrong?
I assumed each of your messages was on a different conversation and is why I
cautioned about making sure the both sides ended the conversation. As Roger
mentioned, no problem keeping a single conversation open for long periods
for your message stream. You'll still need a means to handle the errors and
start a new conversation when needed.
Charles
Hi Dan
Yes, it does help. Many thanks.
Charles
"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:eAQ4Cr3%23JHA...@TK2MSFTNGP04.phx.gbl...