Hi Everyone.
I am trying to get ideas for a possible technology we could use for a certain scenario.
In a nutshell we want to run a distributed application at hundred of remote stores, anything that happens at the store must be propagated to a central sort of hub server.
Now i have already thought about replication, but maintaining replcation across hundreds or even a thousand remote instances would be a nightmare.Also the App must work offline so if no link exists the app must still work and sort of reliably queue the transactions.
So i been experimenting with service broker, to write transactions, that then queue on to service broker to the central hub, therefore enabling remote stores to continue to work even if no comms exist between the remote app and the hub.
But also maintaining service broker at hundred of remote sites would be an administrative nightmare.
So basically i am trying to pick your brains out there, can anyone reccomend a possible technology out there worth looking at for this solution , the simpler the better, even if it simply pushes database somewhere , must it must be reliable and queue like in case comms are down.
Also perhaps there is some sort of .NET technology i am not aware of, that can run the .NET app at the store and the app will try propagate the transaction up to the hub, and will automatically queue them. I know am i grabbing at straws , but perhaps there is some sort of technology that i dont know of as a dba that could be great for this scenario ?
Any reccomendations just to look into would be greatly appreciated.
Thanx
Since this is an archtiecture question here is my "2"...
You could in theory leverage .net to stream records in a distributed transaction (via DTC) but I dont really see how this would be any simpler in terms of administration. Replication & service broker are actually excellent candidates for this solution and would be the two I would look at the most. The other issue here is latency, how long can it take for the records to be merged together? Service Broker I know is a very "fast" technology. I am not a BizTalk expert but you may want to consider it as well.
Derek
|||You could also look at MSMQ, which in .Net is implemented in the System.Messaging namespace. What follows is a short description of the namespace via Object Explorer in VS2005:
The System.Messaging namespace provides classes that allow you to connect to, monitor, and administer message queues on the network and send, receive, or peek messages.
|||Thanx Guys
As far as i know MSMQ is kinda being replaced by Service Broker, but that may purely be from a DB perspective not .NET.
Looks like Service Broker is the better option then, replication is not an option due to the offline capabilities required by this app, service broker ensures that all transaction can be queue and deleivered later, replication cannot garantee this as a connection the the distributor and susncibers is obviously required.
However i am concerned about the amount of setup and adminsitration that would be required at the hundred of remote instances to enable and maintain service broker.
Thanx
|||Service Broker does not replace MSMQ, it is as you said it is merely a similiar option now available in the database. MSMQ and Service Broker are not the same in terms of their pros/cons. I built a real-time ETL system based upon Service Broker about 18 months ago now. Yukon had just entered Beta 2 whenever that was...Anyway, I ended up making friends with Roger Wolter, Service Broker PM and he kinda coached me though my leading the engieering effort with the new technology. http://blogs.msdn.com/rogerwolterblog/archive/2006/02/28/540803.aspx go there to read Roger's quick comparisons between the technologies.
As far as minimizing dev/admin time, you could build everything the same way as I did with the real time ETL system. For example, we always had an "in" queue, "out" queue, "failed" queue, the activation procs had the same structure etc...This way only the logic specific to the individual service is unique.
HTH,
Derek
|||Hi Derek
Thank you for the feedback.
I would be very interested to discuss and idea with you if you have the time. Sounds like you know a lot about service broker, not many do. I have been experimenting with service broker in an idea based on a realtime tranactional retail system using service broker, primarily so that if comms went down the store could continue to transact even though communication the central loan system was not possible therefore the transaction would be delivered by service broker as soon as the comms were up again. Service broker would always be used to send the transactions realtime.
My biggest concern is the initial setup of hundreds of remote sites with endpoints to a single central hub. I am concerned about the robustness and high avalibilty of servcie broker, by that i mean i would hate to be faced everyday with the prospect of having to manually check x amount of remote sites and troubleshoot service broker issues at each remote instance. Also i am not sure if service broker architecture, in terms of scalabiltiy, is more of a db server to db server design, or like i said would a central hub single endpoint with hundreds of remote site initiator endpoints, be perfectly feasable?
In a nutshell i am going to try do data replication from remote sites via service broker to a central loan environment.
However if i could give you my email address or vica versa i would love to shoot the idea past you, basically i would just like a yes this could work or no this is not going to work for you viewpoint, it has been very difficult to find someone who knows enough about servoce broker to really comment.
Thanx
|||
sure, just go to my consulting site (i wont charge u lol) and select contact. www.sqlserverdbas.com. We can compare contrast and I may try to enlist Roger's input if he has the time.
|||Please mark a reply as an answer too. Service Broker is in my opinion the best new feature of sql 2005 for devs, SQLCLR or not.|||Sounds like you have the basic issues identified. One that you may not have considered is the network connection from the remote sites. Service Broker, distributed transactions both require a TCP/IP connection which may or may not be possible with your network configuration. MSMQ normally uses TCP/IP also but it has an optional SOAP protocol. Replication can use HTTP in some circumstances also.
I agree the Service Broker is probably your best alternative if you can get it to work with your network configuration. You will probably want to look at using forwarding to set up some concentrator machines so you don't have the overhead of hundreds or thousands of TCP/IP connections into your central server. You also want to be sure to test with the predicted volume of replication messages to ensure your server can handle the load. Dialog lifetime management can also be an issue. Most of the samples floating around use one dialog for each message. While this makes for a simple sample, it may not be the most efficient way to handle things. You may want each remote system to open a limited number of permanent dialogs and reuse them instead of opening and closing dialogs continuously. There's a discussion of this issue here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx
For more information, try these blogs:
http://blogs.msdn.com/remusrusanu/
http://rushi.desai.name/Blog/tabid/54/BlogID/1/Default.aspx
http://blogs.msdn.com/rogerwolterblog/default.aspx
And this book:
http://www.amazon.com/gp/product/1932577270/sr=8-1/qid=1151000196/ref=pd_bbs_1/002-4995511-1360840?redirect=true&%5Fencoding=UTF8
|||There you go dude, straight from the man himself :) If you still want to discuss more thats cool, just contact me. And by the way, if do decide to implement SB, buy that book. BOL is good too, but Roger's book is "it".
"D"
|||Thanx Roger
I have already ordered that book, just waiting for it to arrive, yes the network is a major concern for me, however that is also why i am gonna try a few thigns out with SB, as i am not very familiar with messenging and queueing etc (I am more of a Database engine person) i am have ALOT of proof of concept to do. However my general understanding is that when the network is down , the initiator endpoints can still put things on the queue , i hope the central endpoint being "up" is not a must ?
Thanx again, after i have played around with some idea i may post back.
Thanx
|||Yes, when the connection is not available, sent messages will accumulate in the sys.transmission_queue until the connection is available again. Be sure to set your lifetime in the BEGIN DIALOG statement to a long enough value to last through network outages or just don't specify a lifetime and dialogs will last forever.
No comments:
Post a Comment