Tuesday, December 18, 2012 9:30 AM
I am building a web application which will most likely have quite a bit of traffic, and I need to be able to perform custom logging of errors and various events in the application. The log posts needs to go into a SQL Server Database, so we can perform various lookups in the logged data as time goes by.
We could set up a stored procedure that takes a number of parameters, and simply performs an insert into the log table. I am however worried about the performance of this approach; we'd be bombaring that SP and performing inserts more or less constantly during some periods.
Is SSB useful in a situation like this? Could the work load on the SQL Server be reduced by queuing the messages in SSB instead of processing them immediately? Or is it more likely that the overhead of the SSB processing itself will result in even more work for the SQL Server than simple, instant inserts?
Thanks in advance for any hints or feedback on this!
Wednesday, December 19, 2012 8:15 AMModerator
It seems that you want to record the logs in SQL Server database, the logs are custom error logs and various events.
In SQL Server, we can use Real Time Data Integration with Service Broker.
Real time data integration supports event-driven data movement and transformation between SQL Server instances which host databases with different schemas. The data integration should be transparent to source systems without significantly impacting the systems when events are captured and delivered.
The technique also supports an intermediate format which allows decoupling of schemas between source and destination systems. It allows either system to change schemas without breaking the application in the other system. The data integration provides fast and efficient data delivery to a destination in an event-driven model, without polling the source system for new data.
For more information, please refer to:
TechNet Community Support
Wednesday, December 19, 2012 8:57 AM
Thank you for your reply.
We have successfully set up SSB and are able to perform logging the way we intended to, the real question is whether we are actually gaining anything from using SSB.
We have a web server which runs an ASP.NET application, and it sends log messages to an SQL Server by calling a stored procedure and posting log messages in XML format. These log messages are queued in SSB, and as the queue is polled, the log messages are "unpacked" from the XML format and inserted into a log table instead.
The real question is: Is it likely that performing the inserts right away would be more efficient and cause less strain on the SQL Server machine, rather than moving them via SSB?
Wednesday, December 26, 2012 3:42 PM
The appropriate solution for your situation depends on various factors. The most important factor to consider is how critical is logging to your application ? are there compliance requirement around an audit trail ? is it ok for your application to proceed if writing of the log is delayed ?
Is the rate of logging in your application is very high and multiple threads of the application might try to write logs at the same time, plus you don't want your application to wait for proceeding until the log is written successfully, then using SSB is the right solution. SSB will make the this log insert process scalable and flexible. It will actually even out the load on your SQL Server and will reduce spikes. The only catch is that you should expect a slight delay for the logs to get written into your tables.
If most of the above factors don't exist in your application, then using SSB may be an overkill, only in trems of an elborately coded solution. It is in no way any less efficient and will not cause any extra strain on your SQL Server.
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
Thursday, December 27, 2012 9:57 PM
Thank you for taking the time to read my question and post your reply.
Most -if not all- of the factors you mention do exist in my scenario. I will continue building the logging using SSB. I really appreciate your feedback which makes me feel more confident that it was a good choice to begin with.
Thanks again & best regards