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

Continuously updating a website

Hi,

Bit of a newbee question:

I'm after hosting a website with basically a table in it that is linked to a MS SQL Server 2005 database, which I want to update on a pretty much continual basis from my own server PC which I'm running a data mining tool that updates the MS SQL Server 2005 database.

Any idea on how I would achieve this, or any pointers would be much appreciated.

Cheers,

Tom

One of the better options would be to explore Replication.

Other than that, you could use the Windows Scheduler to run on a predetermined schedule, executing SQLCmd.exe, which could execute a script file or a stored procedure.

Continuous Merge Replication questions

Ok excuse my lack of knowledge in this department and hopefully someone
can help easily.
When using Merge replication with continuous updating subscribers, it
appears when there is a network outage (as everywhere will get sooner or
later) that it will not try to update until I manually push synchronize.
So that sort of makes it appear useless in my eyes.
Can someone tell me how I can avoid this problem. The only way I have
been able to get around is by setting the subscriber to update every
minute but this is not ideal.
Can the calls to publish and synchronize be coded? if so please help.
And the other query I have is what is the difference between continuous
updating subscribers and transactional replication? Am I missing something?
Cheers,
Tim
You could hardcode the jobsteps to work in a loop (step 3 -> step 2) to
have it automatically restart.
For subscriber updates, merge is similar to transactional replication with
queued updating subscribers. A few differences...
TR works on a transaction basis while merge works on changed data ie 1000
updates to a row will be 1000 stored proc calls for tr while 1 updated row
in merge.
Text and Image changes are treated differently.
Requirement for PK in TR.
More conflict resolvers in merge than queued updating subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Continuation of long SQL statement syntax

Hi All -

I am updating four values. What is the proper syntax to have the
following 4 update statements as one statement?

set objRec = objDB.Execute("Update orientform set session = '" &
strSession & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set fname = '" & strfname
& "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set gender = '" &
strgender & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set lname = '" & strlname
& "' where id = '" & strid & "'")

Thanks,

Joey"update orientform set sessions = '" & strSession & "', fname = '" &
strfname & '", gender = etc etc
where id = '" & strid & "'"

Notes I see you called your command objRec... maybe just habit but you
aren't creating a recordset earlier in the piece are you? Not needed for
updates/inserts/deletes. Also if your id (in the table) has an int dataype
then forget the single quotes around your strid

Jay

<joseph.jasinski@.quinnipiac.edu> wrote in message
news:1102635650.764000.267730@.z14g2000cwz.googlegr oups.com...
> Hi All -
> I am updating four values. What is the proper syntax to have the
> following 4 update statements as one statement?
> set objRec = objDB.Execute("Update orientform set session = '" &
> strSession & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set fname = '" & strfname
> & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set gender = '" &
> strgender & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set lname = '" & strlname
> & "' where id = '" & strid & "'")
> Thanks,
> Joey