Message Queue - From application to SQL Server
-
I'm sure this must be easy - there must be something I'm missing.
We have a server application that needs to log records to SQL Server. The problem is that the burst speed of the app is way above what SQL Server can handle and we can't hold up the app to wait for SQL Server.
Currently we use a home-grown FIFO and bulk inserts (actually done with ODBC) to solve this problem.
I'm wondering if, however, there might be a more efficient way of doing it. Reading the DTS documentation it appears as if I should be able to dump the log messages to a message queue and use DTS to automatically insert them into SQL Server.
I've followed MS's examples but the problem is that the format of the message that SQL Server expects as a Global Variables Message is not entirely transparent, and Google isn't helping me with regard to the format.
Any help on how we might accomplish this goal (or suggestions as to whether or not it is in fact worth it) would be greatly appreciated! Thanks!
-
First off let me state that I don't think dts is the best tool for the job.
But....if you want to persevere, then this link and associated link may help you:
http://www.databasejournal.com/features/mssql/article.php/10894_3321481_2
If you do get this working, please come back to the site and share as I am sure a lot of other people may be interested.
If I was in your position, I would look at a third party application that is built specifically to handle this type of functionality.
------------------------------
The Users are always right - when I'm not wrong! -
I'm rapidly concluding that I'm barking up the wrong tree.
I've found information on how to do it very difficult to come by - and IME that usuaully means I'm trying to do it wrong.
I've also looked at trying to find some part of the replication subsystem that I can hook into - I've done stuff like this with Oracle in the past and got big rewards, but it's all looking pretty opaque in SQL Server.
-
Are there any lulls in your data processing workload or is it a constant stream of logs messages that never slows down?
I would've thought that MSMQ would be ideal. However, it would be easier to roll into a new log file each hour of the day and then have a job in SQL Server Agent bring that file in using DTS or a bulk insert. This is probably what you're doing anyway...
-
I did write a reasonable length reply that for some reason vanished when I tried to post it. Hopefully it won't happen this time!
We have a steady flow of updates with occaional short bursts that can be three, possibly four orders of magnitude faster than the base message rate...
Basically yes, a queued bulk insert is we're doing at the moment, using a home grown FIFO rather than Message Queue (as MQ wasn't available when we wrote that code).
We're now looking at ways we can improve this and MQ is seen as pretty much a no-brainer.
It would be great if we could get from the MQ to SQL Server in an efficient manner that uses minimal middleware code. DTS /seems/ to give us a way to do this I just can't get it to sodding work!
I've now tried using a seperate DTS package to snarf data from the table and send that across a message queue to an import package. The problem is that on the receive end, only the last global variable in the message actually gets assigned. All the others are null.
The messages being sent look OK. Google we and groups searches reveal that other people have run into this problem as well but there seem to be no solutions listed, meaning that there are two possible sceanrios 1) The answer is simple and I'm being really dense or 2) this functionality is broken.
BTW, for anyone interested, the message format is really simple.
Message body...'{03A9EE33-7B1B-483D-9747-976B938494D6}';'{AEA3D453-D345-498B-9B3F-3D52FC42DC93}';'Operator';'String';'TEST';'DateStamp';'Real (8 byte)';'38825.5376299421';
The two GUID are the sender's package ID and version.
The message must have a label of "Global Variables Message" (wthout quotes)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply