I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.
We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.
If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?
And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.
Please let me know. Thanks.
Service Broker seems like an excellent choice for the scenario that you have described. It provides isolation mechansims to ensure that two unrelated business transactions can run concurrently.
The primitive for communication in Service Broker is a conversation, which is a reliable, durable session for exchange of messages. Two-party conversations are known as dialogs and they contain two endpoints -- an initiator and a target. Services are nothing but logical entities that can initiate or be the target for a conversation. They are addressable (via routes) and securable (via certificates). But one service can have multiple conversation endpoints to deal with concurrent conversations.
Given that, in general you would create a 'Service' to handle all conversations for some business function which needs to have a distinct identity and location. If you decide to move the service to another server or issue a new certificate, you will affect all conversations targetting or initiating from that service. So if you feel that the 5 business transactions are all distinct and may in the future be performed at different locations, you are better off with 5 services. If that seems impossible or highly unlikely, stick with a single service.
Conversations can be strongly typed by defining formal contracts that specify what type of messages may be sent by each endpoint. Since a service can expose multiple contracts, it can accept conversations belonging to different contracts. In your scenario, you might have different contracts for each of the 5 different business transaction types.
A database can certainly support more than just 60 routes, so don't feel that the number of routes is going to be a performance bottleneck. If you think it might be a managability nightmare, you could consider investing into something we call "Broker Configuration Notification". If you enable this in your database and a new conversation cannot find a route, Service Broker will send a special "Missing Route" message to a service of your choice. You could then implement this "route resolution" service to lookup the route from a directory and create the route in your local database and Service Broker will automatically try to deliver the message of former conversation.
One thing to be aware of in a WAN environment is that Service Broker requires both instances of SQL Server to be able to connect to each other over TCP/IP. That means that if either party is behind a firewall/NAT, the appropriate ports need to be opened for two-way communication.
Hope that helps,
Rushi
Thanks for the information!
David
No comments:
Post a Comment