Conversation Groups

I am thinking of updating my SQL monitoring application to use Service Broker.

Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.

So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.

Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.

Conversatiuon groups are always local, as in no conversation group info ever goes across the wire with a message. The purpose of conversation groups is to lock together logically related conversations.

To have each server do only one check at a time, is eanough to restrict the activated max count to 1. This way there's at most one instance of the activated procedure doing one check. Multiple requests can be sent to the same server, they'll simply be queued up and wait their turn.

The problem you describe can also be aproached as a pub-sub problem (see https://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx). The publisher (your central administrative service) publishes 'check requests'. All instances you monitor are subscribed to this publisher and receive the request, perform the check and report the result.

HTH,
~ Remus

|||

Thanks for the reply.

I don't think I gave you enough info. All my monitoring processes are running on the same server. I connect out to the 'monitored' servers using linked servers. I can't have the queues on the 'monitored' server as most of them are SQL 2000.

The whole monitoring is run from one SQL 2005 server.

I will take a look at the article you mentioned.

Thanks