Verify Server online status using sql server query

  • Rock

    Old Hand

    Points: 346

    Team

    I have populated list of ServerName from the sql server query. I would like to add a column stating 'Ping Status' > The result should be reachable or not reachable.

    Shall we use EXEC master.dbo.xp_cmdshell 'ping Servername' for the rendered server results in 1 line? - However, this provides me whole results (12-15 lines of ping status o/p of single server)

    Suggest me.

    Result

    Column Names > ServerName & PingStatus

    Row 1 > Server1  Not reachable

    Row 2 > Server2 Online or reachable

    • This topic was modified 1 month, 1 week ago by  Rock.
  • Grant Fritchey

    SSC Guru

    Points: 396384

    Personally, I wouldn't do this from within SQL Server. Instead, I'd set up a PowerShell script to make all the connections. It can still save it all back to a database, but you don't have to be doing weird stuff from xp_cmdshell that way.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thom A

    SSC Guru

    Points: 98563

    Are you actually trying to test if the server is online or if a service on the server is online? Just because you can ping a server, doesn't mean that the service you need is running.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22548

    A failed ping also doesn't mean the server is "up".  Firewall, for example, could block a ping but the server could still be up.

    That being said, if you want fewer results from ping, try using the argument "-n <number>" to reduce the number of times the ping is performed.  For example:

    ping -n 1 localhost

    This will ping localhost 1 time instead of the default of 4.

    One downside of using SQL, specifically ping, to do this is a failed ping can happen on a server that is up and how often are you running this ping check?  If you run it too frequently, your table will grow quickly.  If you don't run it frequently enough, you may miss windows where downtime happened.

    There are other options and tools that will monitor server uptime that will be more reliable than a ping.  Tools like cacti (https://www.cacti.net/) can do this type of monitoring.  NOTE - I do not work for them and am not affiliated in any way; it is just one of the tools I have used in the past.

  • Jeff Moden

    SSC Guru

    Points: 995976

    Grant Fritchey wrote:

    Personally, I wouldn't do this from within SQL Server. Instead, I'd set up a PowerShell script to make all the connections. It can still save it all back to a database, but you don't have to be doing weird stuff from xp_cmdshell that way.

    Heh... ok then... let's see some PowerShell code, Grant. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995976

    Rock wrote:

    Team

    I have populated list of ServerName from the sql server query. I would like to add a column stating 'Ping Status' > The result should be reachable or not reachable.

    Shall we use EXEC master.dbo.xp_cmdshell 'ping Servername' for the rendered server results in 1 line? - However, this provides me whole results (12-15 lines of ping status o/p of single server)

    Suggest me.

    Result

    Column Names > ServerName & PingStatus

    Row 1 > Server1  Not reachable

    Row 2 > Server2 Online or reachable

    You can use INSERT/EXEC to capture the output of the command into a table for analysis to reach your final goal.  While you're doing such a thing, are you interested in capturing disk status of the servers, as well?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98563

    One idea, using Powershell, would be something like this.

    Firstly set up some test objects:

    CREATE TABLE dbo.ServerList (ServerID int IDENTITY PRIMARY KEY,
    ServerName sysname);

    CREATE TABLE dbo.ConnectionTest (TestID int IDENTITY PRIMARY KEY,
    ServerID int NOT NULL,
    TestTime datetime2(0) NOT NULL,
    Success bit NOT NULL);
    ALTER TABLE dbo.ConnectionTest ADD CONSTRAINT FK_Server FOREIGN KEY (ServerID) REFERENCES dbo.ServerList (ServerID);
    GO

    INSERT INTO dbo.ServerList (ServerName)
    VALUES('srvsql2012dev'),
    ('srvsql2012uat'),
    ('srvwww01'),
    ('srvdc1'),
    ('srvdc2');

    Then a Powershell script like below:

    $Servers = Invoke-Sqlcmd -Query "SELECT ServerID, ServerName FROM dbo.ServerList" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"

    foreach ($Server in $Servers)
    {
    $ServerID = $Server.ServerID
    $ServerName = $Server.ServerName
    If (Test-Connection -ComputerName $ServerName -Count 1 -ErrorAction silentlycontinue)
    {
    Invoke-Sqlcmd -Query "INSERT INTO dbo.ConnectionTest (ServerID, TestTime, Success) VALUES ($ServerID,SYSDATETIME(),1);" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"
    }
    else
    {
    Invoke-Sqlcmd -Query "INSERT INTO dbo.ConnectionTest (ServerID, TestTime, Success) VALUES ($ServerID,SYSDATETIME(),0);" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"
    }
    }

    Which might then insert some data like the below:

    TestID      ServerID    TestTime                    Success
    ----------- ----------- --------------------------- -------
    1 1 2020-02-19 10:20:14 1
    2 2 2020-02-19 10:20:14 1
    3 3 2020-02-19 10:20:14 1
    4 4 2020-02-19 10:20:17 0
    5 5 2020-02-19 10:20:17 1

    Note, this was written for Powershell, not Powershell Core. Test-Connection is quite different in Powershell Core, and this will not work correctly in it, as Test-Connection does not fail in the event that the ping times out. In Powershell, if you attempt to use Test-Connection against a host that does not respond (but is in the DNS), you'll get the below:

    PS C:\> Test-Connection -ComputerName UnresponsiveHost -Count 1
    Test-Connection : Testing connection to computer 'UnresponsiveHost' failed: A non-recoverable error occurred during a database
    lookup
    At line:1 char:1
    + Test-Connection -ComputerName UnresponsiveHost -Count 1
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (UnresponsiveHost:String) [Test-Connection], PingException
    + FullyQualifiedErrorId : TestConnectionException,Microsoft.PowerShell.Commands.TestConnectionCommand

    On the other hand, if you try this in Powershell Core, you'll get the below:

    PS C:\> Test-Connection -ComputerName UnresponsiveHost -Count 1
    Pinging UnresponsiveHost [192.168.1.1] with 32 bytes of data:
    Request timed out.
    Ping complete.

    Source Destination Replies
    ------ ----------- -------
    Local UnresponsiveHost {System.Net.NetworkInformation.PingReply}

    Considering that Powershell is still the default CLI and Powershell Core isn't installed "out of the box", even on Windows 10 1909, I made the assumption that Powershell was the correct choice.

    • This reply was modified 1 month, 1 week ago by  Thom A. Reason: Caveat about Powershell Core

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995976

    What would you use to run the PowerShell script on a scheduled basis?  Please don't say the "Windows Task Scheduler".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98563

    Jeff Moden wrote:

    What would you use to run the PowerShell script on a scheduled basis?  Please don't say the "Windows Task Scheduler".

    SQL Server Agent can run Powershell tasks natively.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Jeff Moden wrote:

    What would you use to run the PowerShell script on a scheduled basis?  Please don't say the "Windows Task Scheduler".

    Agent.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995976

    Thom A wrote:

    Jeff Moden wrote:

    What would you use to run the PowerShell script on a scheduled basis?  Please don't say the "Windows Task Scheduler".

    SQL Server Agent can run Powershell tasks natively.

    Can you pass parameters to such a job?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98563

    Jeff Moden wrote:

    Thom A wrote:

    Jeff Moden wrote:

    What would you use to run the PowerShell script on a scheduled basis?  Please don't say the "Windows Task Scheduler".

    SQL Server Agent can run Powershell tasks natively.

    Can you pass parameters to such a job?

    What I have above doesn't have parameters, but you could create a powershell file or declare functions in the powershell task and use switches; if that's what you're asking.

    Of you're asking "can you pass parameters to a Powershell job step" the answer is no; but you can't pass a parameter to a T-SQL job step either. The Command it runs is stored in the job.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995976

    Ok.  Thanks, Thom.

    Now, if the OP would get back to his post and answer my question about disk status, I might be able to help a bit more.  I have code that current monitors the disk status and connectivity of 275 servers and they're not all SQL Servers.  It even has a "Removable Media Finder" section in it and it's all done with T-SQL and a couple of simple calls to xp_CmdShell.  It auto-magically sends out a nicely formatted email called the "Enterprise Disk Status Morning Report" as well as saving the data in a table so that you can run "out of space" predictions.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995976

    Ah... and I forgot to mention that, in the process, it tests for connectivity and, if it fails that, it also does a ping test and reports on both along with the complete disk status and removable media finder.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Mr. Brian Gale

    SSC-Insane

    Points: 22548

    Jeff - that script sounds useful.  Any chance you could post it on here or link to it on github or something?

    Currently, we are using RedGate for our monitoring of that plus cacti, but I'm always open to trying new things!  I'd be curious to see how it compares.  I don't have 275 servers I manage, I only have about 30, and of those 30, only 5 are SQL Servers, but I find cacti to be a bit unreliable.  Nice for a free tool, but due to the failover software we are using, it reports back incorrectly after a failover.  With RedGate, we are only monitoring the SQL instances, so that leaves 25 that I am manually managing.

Viewing 15 posts - 1 through 15 (of 23 total)

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