|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Service Broker - The right approach?I am working on a project that uses a modified version of the A.R.T.S.
database at H.O. This contains data for each client located in a hierarchical location (I.e. Country\Region\City\Store\Client). I have code that can extract the subset of the data for a specific client, what I now need to do is send this data to each client using a guaranteed delivery mechanism. I have done a small test project using MSMQ/DTC in VS.NET 2003 and this seems to be a potentially viable approach, however SQL 2005 Service Broker seems to offer many of the benfits of MSMQ/DTC without the complexities of combining DTC with MSMQ. I intend to use SQL Express on the clients. Q1. Is Service Broker the best way to essentially send a database to a client say on a weekly basis? Q2. If I wanted to send the data to 1000 clients would I create a 1000 dialogs? Hello Christopher,
> I am working on a project that uses a modified version of the A.R.T.S. IMHO, no. Lots of setup for fairly simple task and broker is geared towards > database at H.O. This contains data for each client located in a > hierarchical location (I.e. Country\Region\City\Store\Client). I have > code that can extract the subset of the data for a specific client, > what I now need to do is send this data to each client using a > guaranteed delivery mechanism. I have done a small test project using > MSMQ/DTC in VS.NET 2003 and this seems to be a potentially viable > approach, however SQL 2005 Service Broker seems to offer many of the > benfits of MSMQ/DTC without the complexities of combining DTC with > MSMQ. I intend to use SQL Express on the clients. > > Q1. Is Service Broker the best way to essentially send a database to a > client say on a weekly basis? sending messages, not databases. A lot depends of on if you are simply dumping data down the pipe to the client (where I'd probably look into SSIS first) or if you need to synchronize data between the endpoints (where I might start with Replication and if I needed something finer grained, I'd look into Broker) Another problem you'd have to cope with is that Service Broker is semi-anonymous about services. You don't send messages to a specifc instance of a service, but just to a service. The routing tables take care of delivery messages to a service instance. So to do what you're talking about here, you'd have to have to give each client a different service name (to assure that client got only the data it should) or you'd have to dynamically managing the routing table. Niether idea is especially appealing to me. ;) > Q2. If I wanted to send the data to 1000 clients would I create a 1000 At a minimum, yes.> dialogs? Before you go down this path, you might to give [0] a read. [0]: http://msdn2.microsoft.com/en-us/library/ms166052.aspx Thank you, Kent Tegels DevelopMentor http://staff.develop.com/ktegels/
Show quote
"Kent Tegels" wrote: Thank's for the reply.> IMHO, no. Lots of setup for fairly simple task and broker is geared towards > sending messages, not databases. A lot depends of on if you are simply dumping > data down the pipe to the client (where I'd probably look into SSIS first) > or if you need to synchronize data between the endpoints (where I might start > with Replication and if I needed something finer grained, I'd look into Broker) > > Another problem you'd have to cope with is that Service Broker is semi-anonymous > about services. You don't send messages to a specifc instance of a service, > but just to a service. The routing tables take care of delivery messages > to a service instance. So to do what you're talking about here, you'd have > to have to give each client a different service name (to assure that client > got only the data it should) or you'd have to dynamically managing the routing > table. Niether idea is especially appealing to me. ;) > > > Q2. If I wanted to send the data to 1000 clients would I create a 1000 > > dialogs? > > At a minimum, yes. > > Before you go down this path, you might to give [0] a read. > > [0]: http://msdn2.microsoft.com/en-us/library/ms166052.aspx > > > Thank you, > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > > > Hello Kent, I was afraid you were going to say that. I have spent several days trying to find the best approach that includes guaranteed delivery, delivery in sequence and possibly routing. I've had a brief look at SSIS. It doesn't seem to be part of SQL Express though. Hello Christopher,
> I was afraid you were going to say that. I have spent several days Well, that's exactly what Service Broker does and if that's what you really > trying to find the best approach that includes guaranteed delivery, > delivery in sequence and possibly routing. need, do it. > I've had a brief look at SSIS. It doesn't seem to be part of SQL No, but it can be the target of an SSIS operation if I understand things > Express though. correctly. Thank you, Kent Tegels DevelopMentor http://staff.develop.com/ktegels/ While I'm a great believer in Service Broker, I'm a bigger believer in
simple is better so I would tend to look at backup and restore with FTP or even Email before I used Service Broker for such a low-volume application. -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Kent Tegels" <kteg***@develop.com> wrote in message news:b87ad741d98b8c81c83fa414920@news.microsoft.com... > Hello Christopher, > >> I was afraid you were going to say that. I have spent several days >> trying to find the best approach that includes guaranteed delivery, >> delivery in sequence and possibly routing. > > Well, that's exactly what Service Broker does and if that's what you > really need, do it. >> I've had a brief look at SSIS. It doesn't seem to be part of SQL >> Express though. > > No, but it can be the target of an SSIS operation if I understand things > correctly. > > Thank you, > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > >  |
|||||||||||||||||||||||
Other interesting topics