Post reply

Ping Linked Server

  • 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.

  • October 5, 2009 at 7:58 am

    #1061731

    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()

  • October 5, 2009 at 8:32 am

    #1061779

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

  • October 5, 2009 at 9:33 am

    #1061825

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

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

    Thanks,

    Bradley Jacques

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

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