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

Conver String to Date

Hi all,
I am trying to convert the string value of '12.01.50' to a propert date
value of 12/01/1950, I have tried various things but cant seem to get it int
o
the right format although I can convert it to a date type. Can anyone help?
Thanks PhilYou either have to do the conversion going directly from string to string. O
r you have to go from
string to datetime and then string again. You can't just go from string to d
atetime, since datetime
doesn't have any format (the client application does the formatting). So, so
mething like:
CONVERT(varchar(zz), CONVERT(datetime, '12.01.50' , xxx), yyy)
Where xxx and yyy are the appropriate formatting codes (documented in Books
Online, CONVERT). I
prefer to do formatting in the client app, though. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:24C094A5-2B67-49B3-A40D-BECF53AB5530@.microsoft.com...
> Hi all,
> I am trying to convert the string value of '12.01.50' to a propert date
> value of 12/01/1950, I have tried various things but cant seem to get it i
nto
> the right format although I can convert it to a date type. Can anyone hel
p?
> Thanks Phil

Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>