Verify Server online status using sql server query

Question

Verify Server online status using sql server query

Rock

Old Hand

Points: 367
More actions
February 18, 2020 at 4:48 am

#3726636
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 4 years, 2 months ago by Rock.

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

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

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Thom A SSC Guru Points: 99126 More actions · Answer 2

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.
Larnu.uk

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 3

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

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.

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: 1003865 More actions · Answer 5

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.

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: 99126 More actions · Answer 6

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 4 years, 2 months 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.
Larnu.uk

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

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.

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: 99126 More actions · Answer 8

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.
Larnu.uk

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 9

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

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.

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: 99126 More actions · Answer 11

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.
Larnu.uk

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 12

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.

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: 1003865 More actions · Answer 13

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.

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: 24690 More actions · Answer 14

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.