February 18, 2020 at 4:48 am
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
February 18, 2020 at 1:28 pm
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
February 18, 2020 at 2:38 pm
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
February 18, 2020 at 9:13 pm
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.
February 19, 2020 at 1:23 am
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
Change is inevitable... Change for the better is not.
February 19, 2020 at 3:15 am
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
Change is inevitable... Change for the better is not.
February 19, 2020 at 10:24 am
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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 19, 2020 at 2:01 pm
What would you use to run the PowerShell script on a scheduled basis? Please don't say the "Windows Task Scheduler".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2020 at 2:02 pm
February 19, 2020 at 2:15 pm
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
February 19, 2020 at 2:17 pm
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
Change is inevitable... Change for the better is not.
February 19, 2020 at 2:21 pm
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
February 19, 2020 at 2:30 pm
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
Change is inevitable... Change for the better is not.
February 19, 2020 at 2:35 pm
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
Change is inevitable... Change for the better is not.
February 19, 2020 at 3:07 pm
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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply