October 18, 2005 at 1:19 am
Hi, Due to the enormous ammount of servers and databases I created a script to gather information using linked servers. Every now and then one (or more) server(s) can be unavailable ( maintenace / reboot etc etc ) and my script will end on an "not available" error.How can I script (TSQL) a check-mechanism
"if linked servers is not responding -skip - else - run the rest " ??
Please help me out on this.
GKramer The Netherlands
October 19, 2005 at 12:52 pm
You could do it that way:
Start your day with pinging your servers first.
Place all your linked servers in a table like below on a master server:
Server_ID Server_Name Status
1 Server#1 Yes
2 Server#2 Yes
etc
-Loop thru that table from min serverid to the max serverid using While.
-Ping servers and store results in a temp table.
Like:
Set @cmd='ping ' + @servername
INSERT INTO #t EXEC master.dbo.xp_cmdshell @cmd
-Select only Errors LIKE '%timed out%' or LIKE '%host unreachable%' from that temp table
and insert them with the serverid (servername) into perm tab
-Update your first table and set Status column to the appropriate values:
Yes-If server is available
No-If you can't reach it
Run your other jobs based on status 'yes'
October 19, 2005 at 3:58 pm
The solution posted is a good start, but I would not rely on ping alone ... The OS could be alive and SQL Server down is one sitiation I've seen in the past. Also, sometimes ICMP traffic (that carries ping) is disabled - an example is an internal SQL server and one in a DMZ - exposed to the internet. I prefer to use an OSQL command in liue of the ping and just have is perform SELECT @@SERVERNAME. This always works. You'll have to modify the SELECT for errors a bit but it's easy. Besides if you use the OSQL coommand you don't even need to PING !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 20, 2005 at 1:36 am
Barsuk,
Searching for a sollution I found the part you mentioned but putting it into a cursor checking a selection of our servers was a liitle more difficult due to the datatypes to be used.
declare @servername sysname
declare @Result int
declare @String01 sysname
DECLARE Auto_state CURSOR FAST_FORWARD FOR
select servername
from tables
open Auto_state
fetch next from Auto_state into @servername
while @@fetch_status = 0
begin
set @String01 = 'ping '+@servername
--print @String01
exec @result = master..xp_cmdshell @String01 , NO_OUTPUT
IF (@result = 0)
PRINT @servername +' is reachable'
ELSE
PRINT 'No ping possible to '+@servername
fetch next from Auto_state into @servername
end
deallocate Auto_state
Rudy,
You are right not to rely on the ping alone. I will invoke you hints into my script.
Thank you guys for helping me out.
October 20, 2005 at 9:22 am
What Rudy was saying above is the following:
Declare all variables, encompass the code in stored procedure and run it as job regularly on your master server.
The above code you can include in the same SP too.
select @servername=min(servername) from YourMasterTable
while @servername is not null
Begin
Set @cmd = 'osql -E -S ' + @servername + ' -q "SELECT @@ServerName"'
Exec @Status = master..xp_cmdShell @cmd
If (@Status = 0)
Print 'SQL Server is Live: ' + @servername
Else
Insert this record into the table and retrieve it later for notification/analysys
select @servername=min(servername) from YourMasterTable where servername > @servername
end
October 20, 2005 at 11:46 am
October 21, 2005 at 12:02 am
Barsuk,
The part you where telling about (almost simulair) has been invoked already and its even complexer now. (automated updating on some status fields and sending e-mails regarding (un)availebility ).
Right now I'm dealing with some Foreign server authentication using the -E switch but one of the DA's is looking why this occurs.
Glynne,
set @isql = 'osql -S' + rtrim(@ServerName) + ' -U<user> -P<password> -n -h-1 -I40 -Q"select @@version"'
is not such a good Idea.....-U<user> -P<password> --> hard coding (your)(a) SA account with its PW ???
Ever heard about SOX ( Sarbanes Oxley ) Better use the -E switch.
Guus Kramer
The Netherlands
October 21, 2005 at 1:36 am
If you aren't too far down the road of rolling your own, I've used a product called ServersAlive to good effect to monitor a pretty wide variety of servers, SQL and otherwise. Nice feature set including monitoring, notification, corrective action (scripted), "expected results", disk space, etc. All for not a lot of $, the "enterprise" version is a whole $239.
URL is http://www.serversalive.com/ and no I'm not affiliated with them in any way, just like the product.
Joe
October 21, 2005 at 2:02 am
Joe,
For network and servermonitoring we use MOM. It's hugh, pretty fast, and enourmous flexible for all kind of monitoring of various platforms, applications and tools.
The availability check will be for DBA purposes only because, as I mentioned before, many scripts run agains a lot of different servers ( approx. 240 ) on approx 800 database. Due to maintenace or reboot ( or if a server dies ) these scripts come to a halt and reports will be incomplete.
And to be honest; Creating such a script is a challange !!
Guus
October 24, 2005 at 9:27 am
We use MOM as well. However, MOM is only polling about once every 15-30 minutes (so you have a window when things could be down and you are not aware) . I've never had issues with the OSQL method. I execute it as a scheduled task every 5 minutes. The OSQL as step 1 and an second step to send me email if the first step fails only. As for the number of servers, well, if you add them as linked servers (as a part of the build process) maintenance is a non-issue. If you utilize an MSX server it's even easier for scheduling and maintenance.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 24, 2005 at 11:10 am
I am thinking about getting Idera SQl Diag for certain servers. Nice tool, but on a busy server it looks like it creates some overhead ( by running server traces)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply