Ping Linked Server

Question

Ping Linked Server

Howard C. BAchtel-438731

SSCrazy

Points: 2384
More actions
October 5, 2009 at 7:28 am

#138539

I have a linked server that has proven to be somewhat less than reliable. I would like to create a small job to test the connection and notify me (and others) when the connection fails. This would give us a more timely reference of when the connection fails and more time to address the fundamental issue when production jobs fail.
I can pick a small query and run, which would not be a problem, but would like to omit actual data transfer if possible.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 1

Are you familiar with powershell?

It's microsofts scripting utility, and it has the ability to ping servers. Here's a script that will allow you to ping a list of servers. I pulled it from Ying Li's Blog. You can adapt it to run the appropriate SQL after it pings the server to see if it's up / down. I would also recommend getting another server... it's much cheaper than the man hours that are going to be involved in duct tape fixes.

Thanks!

Bradley Jacques

$erroractionpreference = "SilentlyContinue"

$a = New-Object -comobject Excel.Application

$a.visible = $True

$b = $a.Workbooks.Add()

$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Machine Name"

$c.Cells.Item(1,2) = "Ping Status"

$d = $c.UsedRange

$d.Interior.ColorIndex = 19

$d.Font.ColorIndex = 11

$d.Font.Bold = $True

$d.EntireColumn.AutoFit($True)

$intRow = 2

$colComputers = get-content C:\Myworkplace\Clientlist1.txt

foreach ($strComputer in $colComputers)

{

$c.Cells.Item($intRow, 1) = $strComputer.ToUpper()

# This is the key part

$ping = new-object System.Net.NetworkInformation.Ping

$Reply = $ping.send($strComputer)

if ($Reply.status –eq “Success”)

{

$c.Cells.Item($intRow, 2) = “Online”

}

else

{

$c.Cells.Item($intRow, 2) = "Offline"

}

$Reply = ""

$intRow = $intRow + 1

}

$d.EntireColumn.AutoFit()

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 2

I am not familiare with powershell but it looks like I should be. Thanks for the suggestion.

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 3

Here's a great article on an introduction to powershell for the DBA.

http://www.mssqltips.com/tip.asp?tip=1680

Thanks,

Bradley Jacques