Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic can
get quite extensive and, although desirable, it should not interfere with the
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom service
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queues
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code to
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
...Mike
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.

> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike
|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)

> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.

> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike
|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike
|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike