RSB: SQL Server Service Broker

[Martin Nilsson]

SQL Server Service Broker
http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx

This looks interesting. Would that be something to support in RSB? I don't say that you should implement it but if you also think it looks interesting and what opinions you have.

[Corey Kaylor]

I have a branch in my fork of the project that has initial support for SQL Service Broker. It has a few known bugs and improvements I haven't had time to work through yet, but yes it does make a few scenarios nice. I specifically started working on it because of a few items below I felt were nice to have. I'm not sure if Ayende will want this in the project after I work through the issues though, it goes against the xcopy deployment requirement.

1. Can setup a notification queue to notify when to start processing messages, rather than a Thread.Sleep polling mechanism.
2. Two nodes can share the same queue, but also be addressed inidividually (in my implementation of subqueues).
3. Depending on the SQL Server setup and queue deployment setup you can have full failover where there is no loss of time in message processing.
4. Does not require DTC, but still supports it if necessary.
5. It's fast. When I'm done I will setup a comparisson on its performance over the other transport implementations.

[Martin Nilsson]

Cool!
What makes it fail regarding xcopy requirement?

[Simone Busoli]

Maybe that it requires SQLServer to be installed? ;)

[Martin Nilsson]

how is that different from MSMQ?

[Corey Kaylor]

Yes in this case having sql installed is no different from msmq. However, creating the schema for the queues and history tables etc. I have created a somewhat flexible option if the account running the queues has enough permission to create the database etc. it will create the schema for you as a "user instance" mostly made for the unit tests. However, I would still recommend for production systems that you create the schema manually with the included sql scripts.

[Matt Burton]

This is great Corey - thanks for sharing it! I would be very
interested to see the perf numbers on this one. One question - being a
service broker noob, I don't quite understand the relationship between
the queue Uri and the queue created in the database. When I ran the
tests I wound up with a user instance testqueue database, but poking
around in there I couldn't see where / how the Uri used in the test
(like "tcp://localhost:2204/h") was used - I mean, it works, just
trying to understand how, is all. One of the benefits of Rhino Queues
was since it's going over TCP it can be load balanced effectively -
does the same apply here or would I be leaning on clustering database
servers behind a single logical endpoint?

Thanks,
Matt

[Corey Kaylor]

In Management Studio under the Service Broker folder in the database you'll see Routes, Queues, Services, etc. There you should be able to see how I'm deriving the uri. In Service Broker those three things are essentially what makes everything work. The service is the endpoint pointing to a queue, the routes are how a particular service is routed. I use a uri based naming convention for the services because by default Service Broker cannot understand how to route services it is initially unaware of. This enables an easy hook to create new routes automatically going to the proper endpoint. Service Broker can be load balanced, but my initial effort is to support failover (with clustering) as the primary goal. Then if I find it is necessary to support load balancing I will do so.

[Corey Kaylor]

A run of the code below, similar to what Ayende posted for his performance test with rhino queues. 10,000 separate transactions runs in about 8.2 seconds on my machine. I'm not sure the size of the data he was using in his tests though.

[Fact]
        public void PerfTest()
        {
            var sp = Stopwatch.StartNew();

            for (int i = 0; i < 10000; i++)
            {
                using (var tx = new TransactionScope())
                {
                    queueManager.Send(queueUri, queueUri,
                                   new MessageEnvelope
                                   {
                                       Data = Encoding.Unicode.GetBytes("hello"),
                                   });


                    if (i % 10 == 0)
                        Console.WriteLine(i);
                    tx.Complete();
                }
            }

            Console.WriteLine("{0:#,#}", sp.ElapsedMilliseconds);


        }

[Martin Nilsson]

Nice!
Great work! I'll grab your repository and try it out soon.

[Ayende Rahien]

Corey,

Please perform _two_ operations in the transacton, see what is going on then

[Corey Kaylor]

Using the code below sending two messages per transaction within 10,000 separate transactions runs in 14 seconds. I'm not sure what else you were suggesting, unless you were wanting to see if the transaction was promoted to DTC what the performance looked like then? This sample would still not have been promoted to DTC though if that was your goal.

[Fact]
        public void PerfTest()
        {
            var sp = Stopwatch.StartNew();

            for (int i = 0; i < 10000; i++)
            {
                using (var tx = new TransactionScope())
                {
                    queueManager.Send(queueUri, queueUri,
                                   new MessageEnvelope
                                   {
                                       Data = Encoding.Unicode.GetBytes("hello"),
                                   });

                    queueManager.Send(queueUri, queueUri,
                                   new MessageEnvelope
                                   {

                                       Data = Encoding.Unicode.GetBytes("hello2"),

[Corey Kaylor]

Reading Ayende's latest blog post http://ayende.com/Blog/archive/2009/11/03/development-only-code.aspx I read it with a somewhat guilty conscience because in the service broker queue implementation I have violated this. However, in my case there is a legitimate use case for "resetting the database" in production code. The specific use case is when our windows application deployed through click once. With rhino queues this was easy and less of a concern. I'm interested in hearing others approaches for how they might achieve my goal of the following.

1. Messages sitting in queue on startup for the windows application are unimportant and should be purged.
2. Schema changes i.e. version conflicts with the current schema must be recreated without any interaction of the user or extra complexity of deployment that would fall outside of click once.

I'm leaning towards conditional compilation so that I deploy a specific build for our click once application, but looking for better suggestions.

[nightwatch77]

Corey, I wouldn't worry about that at all. Usually you don't deploy
your application twice a day so you don't have to automate the
database setup. And regarding the real-world usage, I prefer SQL to
MSMQ or RQ, because SQL server has way much better management tools
and you have all the power of T-SQL at your hands. RSB needs to have
much better management & monitoring tools if it's going to be used in
large (enterprise/industry-level?) applications.
BTW some time ago, don't remember where, I have read an article
comparing SQL service broker to MSMQ and stating that SSB is flawed by
design. Don't remember where it was and can't find it, but did you
notice anything 'strange' in how SSB queues work?
BTW are you going to show us your implementation?

Best regards,
Rafal

On Nov 3, 4:37 pm, Corey Kaylor <co...@kaylors.net> wrote:
> Reading Ayende's latest blog posthttp://ayende.com/Blog/archive/2009/11/03/development-only-code.aspxI read

[Corey Kaylor]

If you're referring to this article. Yes I've read it although I don't agree. It really depends on what is important to you. The comment about poison messages in the article is valid. However, I have prevented that by never rolling back the dequeue call. If it fails I resend the same message back to the transport endpoint. The consume method is called within its own isolated transactionscope preventing a failure rollback for the dequeue as well. The comments in the article about distributed transactions aren't valid either because my implementation will support it if it requires a promotion to DTC, but in most cases won't need it. The comments in the article about not all resources are sql server resources might be valid, but for myself it holds true for 99% of our resources and I don't mind catering to that 99%.

Regardless of the frustrations you might have with RSB, it has been a great thing for us and if it wasn't so nicely designed and extensible I wouldn't have even attempted to do this in the first place. The nice thing about open source software is where you feel things are lacking or not perfect for your scenario you can contribute to make it better, or tweak it and make it work for your needs.

It's not hiding it's located here. I may blog about it and our successes with RSB in the next few weeks.

[nightwatch77]

Thanks, I'm not frustrated with RSB, I had some problems with
performance and didn't choose it for production.
But overall, I like its design and philosphy.

Best regards
RG

On Nov 3, 8:53 pm, Corey Kaylor <co...@kaylors.net> wrote:
> If you're referring to this

> article<http://javiercrespoalvez.com/2009/03/using-sql-service-broker-in-net....>.

> here<http://github.com/CoreyKaylor/rhino-esb/tree/servicebroker>.

[nightwatch77]

Corey & all others interested in RSB,

Today I've been hunting for mysterious distributed transactions
appearing out of nowhere in production systems and hanging there
forever. And I have made quite unpleasant discoveries along the way.
First of all, I learnt that TransactionScope is not as smart as
Microsoft says. They say that TransactionScope will not promote the
transaction to distributed if there's only one resource manager
involved. I thought that if I have one SQL server and only one
database I will also be using exactly 1 resource manager, so I will
not have distributed transactions when making connections to that
database. Wrong! If you open two database connections inside
TransactionScope you will be promoted to a distributed transaction!
This will happen even if these two connections are identical, the same
database, the same connection string (!)
Up to today I thought distributed transactions are used when the
transaction scope crosses the process boundary (be it on the client or
on the server) - but obviously i've been wrong.
Now, why I am posting this here - mainly because Corey's work on
Service Broker transport will probably not improve performance by not
using distributed transactions. Each transaction will be promoted to
distributed as soon as you open a second connection to your database.
And you almost certainly will open new connection when executing some
application logic in a message hander - for example a new ORM session
or ADO.Net database update.
Also, this is no wonder Ayende had performance problems with MSMQ when
executing operations on two queues in a single transaction. Microsoft
simply chose to promote the transaction to distributed, no matter if
there's only one transactional resource or more.

Next issue: default isolation level with MS distributed transactions
is Serializable. This kills last bits of performance remaining after
promoting to distributed transaction. Fortunately it can be changed.

And more: sometimes distributed transactions will get 'zombified'.
They will hang in DTC forever and will not be aborted even after they
time out. The problem is that they will hold locks in SQL server,
deadlocking with other transactions in the database. I don't know why
this happens, googled a bit and found that it can happen when
application server dies during a distributed transaction. In our
production system the ASP.Net host process is recycled several times a
day, so I suspect sometimes a transaction is zombified during such
recycle. And this is a 'normal' recycle, not a process crash. If the
DTC transaction hangs we have a catastrophic failure reported by our
customer (the system throws SQL transaction timeout when executing any
action) and the admin has to manually kill dtc transactions.

Summing up: Microsoft is certainly abusing distributed transactions.
They are slow, execute longer, put more locks on database and reduce
system performance. MSDTC is a nasty pig without any useful monitoring/
diagnostic tools. I don't know what's the purpose of building system
on top of a message bus when you have to pay such performance penalty.

Ah, and please note I didn't perform any tests with RSB - I'm talking
about my own message queuing library built on top of SQL server table.
But I suspect the problem is also affecting Corey's work. With MSMQ
transport it's natural that you will have a distributed transaction so
this is not a big surprise.

Can you verify if I'm correct?
Best regards
Rafal

On Nov 3, 8:53 pm, Corey Kaylor <co...@kaylors.net> wrote:
> If you're referring to this

> article<http://javiercrespoalvez.com/2009/03/using-sql-service-broker-in-net....>.

> here<http://github.com/CoreyKaylor/rhino-esb/tree/servicebroker>.

[Ayende Rahien]

Rafal,
You are correct in all your assumptions.
I am considering adding a non transactional mode for RSB, which won't be as safe as using the DTC but will be MUCH more performant.
Without using the DTC, we will simply gather all the messages in memory and only send them at the end of the message processing.
Thus, we would get similar behavior to now, where if a message has failed, nothing get sent.

[Corey Kaylor]

Discouraging findings...

This was not something I had run into yet on the Receive side because I haven't used the Service Broker implementation with our systems yet, mostly proving message routing etc and happily seeing that things were never promoted to DTC. I did manage to put together a sample doing things as you've described where a connection is opened within a consumer and replied again and it is promoted to DTC. The sending side is a bit different, I read after you raised concerns that SQL Server 2008 does its best to not promote if the connection string is identical. Which is likely why in my second performance test it didn't take a huge performance hit because it was able to run through the entire test without DTC.

Maybe the idea for a non-transactional mode option would be ideal.

[Corey Kaylor]

I suppose if you were using SQL Server 2008. It might be an option to keep things transactional and have the Queue schema reside within the same database the consumers are reading from. I'm not even sure if this is entirely reasonable or at what point sql server will still promote the transaction, but I'll try to put together a sample to see what happens. It would end up looking something like below.

[nightwatch77]

Hi, I have run a simple test on SQL 2008 and the transaction got
promoted to DTC just as with SQL 2005 on opening a second connection
with same connection string.
But maybe there is an option to tell it to try harder to keep the
transaction local.

R

[Corey Kaylor]

Here is the information I read on the matter (link). To be more specific , it seems to only work when the same pooled connection is returned from the connection pool. So if two connections are open at the same time it will get promoted. Unless I misread something from the post. So it appears in order for it to work the flow would occur as follows.

Start TransactionScope

   Open Connection

     Dequeue

   Close Connection - Returning connection to pool

   Begin consumer block

     Open Connection - Returns pooled connection already associated with the transaction

       Read from db in consumer here

       //sending reply before closing connection would get promoted, unless the same connection is re-used in code

     Close Connection

     Send Reply

      Open Connection

        Enqueue message to send

      Close Connection

     End Reply

   End consumer block

   Any cleanup message processing 

End TransactionScope

[nightwatch77]

Ayende, I think building non-transactional RSB could be a bit
difficult. Reliability is the key, if you call 'Send' you expect that
the infrastructure will take responsibility for the message and do its
best to deliver it. In any case it cannot lose the message. If
messages are simply stored in memory and sent to some queue after
transaction they can be lost if the process dies, but also if the
queue doesn't accept them for some reason (and the sender will never
know that). So you would need some persistent store for the outgoing
messages.
What about NServicebus, they have been 'in business' for several
years, didn't they have problems with distributed transactions? I
can't find anything at NServicebus discussion list...
R

On Nov 9, 9:02 pm, Ayende Rahien <aye...@ayende.com> wrote:
> Rafal,
> You are correct in all your assumptions.
> I am considering adding a non transactional mode for RSB, which won't be as
> safe as using the DTC but will be MUCH more performant.
> Without using the DTC, we will simply gather all the messages in memory and
> only send them at the end of the message processing.
> Thus, we would get similar behavior to now, where if a message has failed,
> nothing get sent.
>

[Ayende Rahien]

They have the exact same issue, as far as I can see. I spoke about this with Udi, and he confirmed that he had much the same experience.
As far as I understand, he is simply making tradeoffs, and using non transactional queues (which we can now do in RSB as well) when perf matters.

And yes, there is a small chance of a problem when you are storing them in memory, but I think that the chance of a process crash between the transaction commit and sending the messages is small enough that we can safely ignore it.

[nightwatch77]

On Nov 10, 11:06 pm, Ayende Rahien <aye...@ayende.com> wrote:
> They have the exact same issue, as far as I can see. I spoke about this with
> Udi, and he confirmed that he had much the same experience.
> As far as I understand, he is simply making tradeoffs, and using non
> transactional queues (which we can now do in RSB as well) when perf matters.

Today I've been running simple test - two database inserts in separate
db session each (NHibernate), repeated 10000 times.
If these inserts were inside TransactionScope, the test took 2'54'',
without TransactionScope - 2'17'', so the overhead of distributed
transactions was about 20% (and I have run 10000 distributed
transactions, not a huge single transaction). Average 57 distributed
transactions/sec vs 72 normal transaction pairs / sec (that is, 144
inserts/sec).
This doesn't look bad at all (however, at production system where I
was troubleshooting DTC transaction problems, the overhead seems to be
much bigger - maybe because of more complex transactions and reduced
concurrency whenever a distributed transaction occurs). Corey, maybe
you could check the Service Broker performance with and without
distributed transactions?

> And yes, there is a small chance of a problem when you are storing them in
> memory, but I think that the chance of a process crash between the
> transaction commit and sending the messages is small enough that we can
> safely ignore it.

I was rather thinkin about errors occurring when trying to actually
insert messages to some queue outside the transaction.
In such situation the code executing in transaction will never know
about these errors, so your library must take additional steps to
ensure that these messages don't get lost. You might retry sending
several times, persist the messages for manual handling later, notify
the user somehow or report the errors to some error handler in
application.... looks complicated to me

[nightwatch77]

and a little side note about NHibernate: the HiLo key generator seems
to have some problems when used with distributed transactions - if
inserts are run concurrently it eventually causes deadlock and
transaction is killed by SQL server. I also have a question: what will
be the behavior of HiLo generator when the distributed transaction is
rolled back - will the next ID number also roll back if it has been
incremented in that transaction? This would be bad of course....
R

[Ayende Rahien]

What?!
What version of NH are you using?
I specifically worked on that part to make sure that this works fine.

[Corey Kaylor]

Yes I will put together a more comprehensive set of comparisons. I want to make some changes to the way the current queue is setup based on the earlier suggestions, allowing the use of the same db for service broker and transactional store and remove the auto-creation etc.

Another thing to consider for this is whether or not using the System.Transaction + SqlTransaction for both scenarios of a System.Transaction that is promoted to DTC and not. I have run tests some time ago that indicated that using SQL transactions combined with System.Transaction provided the best performance over relying on the sqlconnections enlistment. There is likely documentation somewhere on this, but it would probably be nice to see the difference when using it within RSB.

begin systemtransaction

    open connection

      begin sqltransaction

      commit sqltransaction

    close connection

commit systemtransaction

vs.

begin systemtransaction

    open connection

      //connection is auto-enlisted

    close connection

commit systemtransaction

[nightwatch77]

Nhibernate.dll file version: 2.1.0.1003
If I have some spare time I'll put together a test case.
R

On Nov 12, 11:12 pm, Ayende Rahien <aye...@ayende.com> wrote:
> What?!
> What version of NH are you using?
> I specifically worked on that part to make sure that this works fine.
>

[Jason Meckley]

are you sure it's hilo that's causing deadlocks? I use HiLo and RSB
with DTC. If I do encounter a deadlock, it's because of the exeuction
plans of my queries, not HiLo.

[Corey Kaylor]

I have updated the service broker queues and my fork / branch of rhino-esb for the above mentioned changes. I have also updated a unit test to demonstrate how it might be possible to avoid DTC promotion if using SQL Server 2008.

I will start to run comparisons against each transport tomorrow. I have run one test so far with the service broker as follows 10,000 messages each with their own transaction. Send > Consume > Open Connection > Close Connection > Reply > Consume. My results varied between 1 min 7 sec and 1 min 15 sec and was never promoted to DTC.

[nightwatch77]

Corey, have you got your performance test results? I'd be interested
to know them...
BTW, I've been experimenting with message queue implemented as an
ordinary sql server table and got quite nice results after fine-tuning
the queries (especially locking) - the system is able to process
hundreds of messages per second (running inside ms virtual PC, on a
laptop, it handled over 400 messages/sec and about 150 inserts/sec).
What's more, it nicely handles load balancing - there can be many
message readers on a single queue running concurrently without any
nasty interferences. Regarding the distributed transactions - I will
live with them for now and accept whatever good or bad they bring.

Best regards
RG

[Matt Burton]

Rafal -

Any chance you can share your implementation? Some great numbers there
- was in the initial stages of coming up with a similar queue
implementation myself for a side project that has to run in a shared
hosting environment - would love to take a look if that's possible.

Thanks,
Matt

> --
>
> You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.
> To post to this group, send email to rhino-t...@googlegroups.com.
> To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
>
>
>

[nightwatch77]

Matt,

1. Take note the 400 messages/sec result is for 'empty' messages,
handlers don't do anything. I wanted just to test what's the raw
performance of my queues.
2. Regarding the implementation - I'm unable to share the code now,
it's a part of a commercial project and I would have to put some work
into that to make it public. It's a message bus library quite similar
to RSB, but based entirely on SQL tables used as queues. But I can
give you and your SQL a hint - use WITH(READPAST, UPDLOCK) query
option for retrieving messages from queue. And here are some examples
of how to implement a queue table with READPAST: http://www.mssqltips.com/tip.asp?tip=1257
Before I ever knew there is a 'READPAST' hint in SQL I tried many
techniques but none of them was as effective and simple as that.

Best regards
RG

[Matt Burton]

Thanks for sharing what information you can - most appreciated. I was
planning on following the guidance here:

http://johnnycoder.com/blog/2007/06/20/whats-the-best-way-to-manage-a-database-queue/

With that and your tips it looks like I'm armed with enough
information to be dangerous now :)

Again - this is just a small project - I just need to have a durable
queued transport for handling commands and events in a shared hosting
environment - speed is nice but not critical.

Thanks again,
Matt

[Corey Kaylor]

I have only run MSMQ and service broker so far.

ServiceBroker 1000 msgs round trip between two separate endpoints opening a connection in the consumer and reading one row, each test used threadCount=1

1st Run

  DTC: 13 sec

  No DTC: 7 sec

2nd Run

  DTC: 7 sec

  No DTC: 4 sec

MSMQ same test

1st Run

  Transactional: unsuccessful tests, and I don't see anywhere in the unit tests that use transactional queues did I miss something?

  NonTransactional: 8 sec

2nd Run

  NonTransactional: 6 sec

With the service broker I have been able to achieve as much as 700 msgs being received per second, although it averages around 250.

I agree the SQL based queues regardless if it's broker or tables seems to be nice. Be careful however with a potential consumer causing a transaction to be rolled back releasing any locks on the message allowing one of your other processing threads to pick it up again before you're ready for it to. One additional nice thing about the broker IMO is the out of process sending and receiving, but there are plenty of drawbacks as well depending on your needs.