Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-09-17 : 02:48:51
|
I want to send SMS text messages. We have written a little App that does this. Examples are "I forgot my password" (needs a quick response) and "You may like to know about XXX" (marketing / information message, slow transmit is OK) [Yeah yeah yeah ... all users are all Opt In and its not Spam before anyone asks ...  )I can have the App Poll an Sproc in the DB which will return the most urgent message, and loop until no more, then sleep for N seconds (although that means that a quick-response message will most likely have to wait a bit).I have multiple databases per server that need this application. I could put the tables on a central database, but I would prefer to have the data in each individual database (so the Client Admin for that database can see who has been sent a Text, their remaining credits, we can move that database to a separate server without worrying about sync. with the Admin database, and so on)Having the Client Tables in the Client Database but also a Queue Table (duplicate data, so to speak) on an Admin database would be OK.The App is running on the Web Server, rather than the Database Server (at present - we have "no unnecessary Apps on SQL boxes" policy)I am keen to avoid running an instance of the App for each Client Database, each separately polling! but I do need to make sure that high priority messages are serviced in the shortest possible time (so if the App is busy servicing 1,000's of low priority messages on Database A it has to somehow become aware of a high priority message on Database B - a central Queue on an Admin database might be the best solution)1) Should the DB be launching the App when there is work to do? rather than the App polling? (this would help with urgent messages)2) Should I use Service Broker to get the new records from Client Database into a centralise queue on Admin Database? (this could then be used across multiple servers, although I don't have any objection to running the Texting App on each server)I'd appreciate your thoughts. |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-09-17 : 06:14:22
|
A couple of (semi-random) thoughts until you get a more considered response from here.Presumably the forgotten password is fairly low volume, does that mean that a trigger to fire your texting app is feasible?What about MSMQ or similar. If I remember rightly that copes with message priority, it might mean you don't need the queue table(s).And no you can't have my mobile number to test it on  steve-----------I used to think I wasn't a morning person but it never got better in the afternoon |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 09:32:37
|
| I think MSMQ, as was, is what is now Service Broker? If so I'd be happy that the "Originator" indicated a priority, and the "queue" at the Receiver end (which I suppose will be a database table) can just deliver the next message using SELECT TOP 1 ... ORDER BY Priority, CreateDateIt would be a cool trick if the App did not have to Poll though, and the Queue Manager could launch the App when the first item arrived in the Queue, and the App could keep asking for records until it was told there were no more and that it should abort; thereafter it would be instantiated again the next time a message arrives.We currently send EMails etc. for Lost Password "there and then" to SMTP. We want to change this so that all emails go through a queuing system. Currently if the Email address is duff the web page is liable to error-out when SMTP fails, and rather than fix this (in the Web App) we'd prefer to centralise the whole lot. Plus we can then initiate Emails / Texts from Sprocs / SQL side, rather than having to do them from Web App end (we have NO SMTP ability directly on the SQL Box so we keep it as locked down as possible - possibly overkill, but there we are!)Thanks for your thoughts. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 09:33:53
|
| P.S. Maybe the App could be permanently instantiated and SQL just be "slow to respond" when there were no records - maybe SQL / Service Broker could sit waiting for a Semaphore that there was something in the queue before sending the next recordset to the App? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-17 : 09:39:53
|
| MSMQ is similar to Service Broker, but is for the middle-tier, not backend. I'd use service broker for this (and a central db) if volume is high.if volume is low, i'd just have the app that handles the submission immediately execute the sms routine synchronously.My 2c |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 09:50:33
|
Thanks Russell. Our experience has been with SMTP (for Email) that it gets snarled up when we have large volumes being sent out, and high priority emails tend to then be delayed. This is with multiple web sites all sending their own emails to the SMTP service direct, and thus concurrently.I think the same is likely with SMS - although we are sending SMS using HTTP, rather than SMTP, to a service provider who is probably much more able to take a high volume of concurrent requests than our crappy in-house SMTP server!But we Want to use the opportunity to change both the new SMS and the existing EMail, wherever they are generated within our systems, into a single "flow" so we have better control - and then we feel we will also be able to benefit better from high throughput SMTP servers (such as SMTP Express from EasyMail Objects - if I remember their name correctly)A marketing mailing would typically be 10,000 - 100,000 recipients. These have been done by bureau in the past, but the clients are finding it hard work to customise the content sufficiently using the bureau services - all the Customer and Product information is on the Web / e-commerce Database and divorcing that from the actual sending service is causing increased-cost-of-working at best, and embarrassing cock-ups at worst  Hence wanting to be in better control of the Flow going forwards.I'd better read up on Service Broker |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-09-17 : 11:21:36
|
| I know even less about Service Broker than I do about MSMQ, however it seems that in 2k8 at least you can assign a priority, so that may resolve the lost password issue.With MSMQ you can assign a trigger that occurs when a message arrives i.e. no polling needed. The trigger can I believe call an app. As the priority is already assigned you will be getting the first item on the queue each time, as the queueing system will let the higher priority objects jump the queue, the rules for the trigger then determine what happens to the message. I am presuming that Service Broker can do some or all of this sort of stuff.For MSMQ I have seen a suggestion that "poisoned" messages - in your case those with a duff email address - can be siphoned off into a seperate area to handle later using the trigger rules. Is something like that feasible with Service Broker?-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 13:03:27
|
| Hope so, because I also have to deal with the response from the App (e.g. "That SMS text cost 1.5 credits" - although I could tell the app the Database and Sproc to call with that information - rather than it calling the Central Queue database back with the result - which Central Database would then have to save into the Client database)Sometimes I wish I wasn't a Detail Person!! |
|
|
|
|
|
|
|
|
Multi-database Queue System