Is this the right tool?

I think Service Broker is the right tool for what I want to do but need confirmation. Here is what I want to do.

Our company sells small business consulting services. When a client signs up with us, we put a server at their site that runs our app (using SQL Server 2005). Once the server is setup at their site and operational we constantly send certain information from that server to our central corporate server. There could be thousands of records every day being sent from each of these servers to our corporate server. We're using webservices now to transfer this data from each server but I'm afraid we're reaching a point where webservices will no longer work because of the volume of data.

1) I assume that Service Broker was created, in part at least, to address these types of situations?

2) If #1 is correct, is there any step by step examples/instruction that shows me how to use Service Broker to communicate between two SQL Server databases on two different servers?

Thanks very much.

Amos.

Yes, Service Broker is explicitly designed with these scenarios in mind. It provides a reliable loosely coupled infrastructure for the communication:
- message delivery is guaranteed to be Exactly Once In Order (no message duplicates, no messages out of order)
- the infrastructure can gracefully handle availability problems (lack of connectivity to the corporate server will be transparent to the operations site, applications will never notice it)
- durable, transactional semantics for message delivery (messages cannot be lost between sender and recipient, delivery correctness is guaranteed even in the event of a connection interrupt, application crash etc)
- message queuing for handling load spikes, service interrupts, operational upgrades etc
- loosely coupled, designed with separate administrative control between the conversing parties (sender/receiver can upgrade separately the service application, can have separate administrative and security control etc)
- integration of data and messages in the database, benefiting from the existing recoverability of the database (backup/restore), availability (failover clustering, database mirroring), programmability (T-SQL, ADO.Net), monitoring (integration with SQL Profiler), relational semantics (e.g. ability to run a SELECT over a queue to see the messages pending to be received) etc etc.

For examples and samples, I recommend you visit the team site at www.sqlservicebroker.com, read the articles mentioned on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516904&SiteID=1 and other articles about it that you can find. If you can get hold of Roger's book 'The rational guide to SQL Service Broker', it's a great read on the subject. The easiest way to set up communication between two machines is using the tool mentioned at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=339850&SiteID=1

HTH,
~ Remus