6 Replies - 1048 Views - Last Post: 13 June 2016 - 04:13 AM

#1 Riskinit  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 174
  • Joined: 14-January 10

What is the best way to check connectivity to a server (ping ip add)

Posted 02 June 2016 - 07:14 AM

I am looking at code like this

set nocount on
declare @cmd_dir varchar(20)

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#ping'))
DROP TABLE #ping;

CREATE TABLE #ping (
ROW_NUMBER int IDENTITY (1,1) NOT NULL,
line varchar(100)
)

SET @cmd_dir = 'ping 192.168.0.2'

INSERT #ping (line)
Exec master..xp_cmdshell @cmd_dir


select * from #ping

if not exists(
select 1 from #ping where line like '%time=1ms%' or
line like '%time<1ms%'
)
print 'network slow'
else
print 'network OK'



But I'm not sure if this is a reasonable solution. I would have to ping the same IP address every 5-10 seconds and report the network is slow to a database.

Any advice or better code is greatly appreciated. I'm in a friendly mood today so I'll give the person with the best solution a $5 gift card for amazon.

Is This A Good Question/Topic? 0
  • +

Replies To: What is the best way to check connectivity to a server (ping ip add)

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13566
  • View blog
  • Posts: 54,122
  • Joined: 12-June 08

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 02 June 2016 - 07:27 AM

Why would you be doing that from inside a SQL database? Why just a regular app? Not to mention ever 5-10 seconds?! Seems excessive.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 02 June 2016 - 10:42 AM

as modi said -- doing it from inside ms sql is useless-- ms sql will always use 100% of resources on the server it is on. So your best bet is to have a method in the actual app that tells you it's connection
Was This Post Helpful? 0
  • +
  • -

#4 Riskinit  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 174
  • Joined: 14-January 10

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 02 June 2016 - 11:57 AM

Well the purpose of this stored procedure would be as a heartbeat and I have to escalate to the IT department saying communication has gone down. This was the only thing I could think of to monitor code. I suppose I could do it from an SSIS package. Do you think that is a better alternative here?

DarenR, when you say "ms sql will always use 100% of resources on the server it is on" do you mean the server can't do anything else while I run this stored procedure? I thought a stored procedure only locked out tables it was using in this case #temp.

View Postmodi123_1, on 02 June 2016 - 08:27 AM, said:

Why would you be doing that from inside a SQL database? Why just a regular app? Not to mention ever 5-10 seconds?! Seems excessive.


I am using the SQL database because it's the only method I've researched and applied. There are no full time IT developers in my company.

I don't know what a regular app means here.

The 5-10 seconds is the minimum time I could choose. Very easily I could just go to 60 seconds or even more and probably will but I don't see anything wrong with a ping every 5 seconds.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13566
  • View blog
  • Posts: 54,122
  • Joined: 12-June 08

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 02 June 2016 - 12:00 PM

The bulk of modern day programming languages have options to ping, tracert, etc in them. C#, C++, JAVA, python, power shell, etc. They take up substantially less processing foot print and are built for it versus trying to jerryrig a data storage mechanism into doing the work.
Was This Post Helpful? 1
  • +
  • -

#6 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 03 June 2016 - 05:13 AM

No i am saying ms sql is a resource hog that uses 100% of the systems resources even when you arent really running anything. Basically it holds a resource hostage until you use it. So let's say you have 32 gig ram on a system, it will use all 32 gigs at any given time and basically holds it until you run a query at which time it utilizes the 32 gigs to run the query then it idles but still shows 100% of the resource as being used. (it is basically like a place holder) this is why most companies dont share a server with sql and something else(meaning sql usually has a dedicated server)..... but as modi suggests a lot of the languages have built in functions for pinging.
Was This Post Helpful? 0
  • +
  • -

#7 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 792
  • View blog
  • Posts: 1,886
  • Joined: 24-April 12

Re: What is the best way to check connectivity to a server (ping ip add)

Posted 13 June 2016 - 04:13 AM

I would probably have turned to CLR in C# if I were going to rig this sort of crazy contraption rather than xp_commandshell which is considered something of a security risk and far more limited in its capabilities.

You probably want to back up and look at what problem it is that you want to solve first of all. Sounds like you want an alert that will tell you if the server is available. Of course, the server telling you whether it's available is a problem if it is down. It can't run this code if it's not running.

If the server is unavailable, you probably have one of a few problems. For example, maybe the server is too busy to get to it, or the server is crashed, or the network is crashed. To the end user it's pretty much all the same if the server won't answer their request.

For a "heartbeat" maybe have an external application establish a "permanent" connection to the server (no particular reason to constantly make and break the connection if you're going to be constantly pinging, not to mention loss of the connection itself is an indication that the server is unavailable. Then you could issue some innocuous query against it every 30 seconds or whatever like SELECT @@VERSION. If the server answers, it's available, at least along that segment of the network.

SQL Server from the server to the server will attempt to use shared memory instead of TCP/IP.

Your ping test requires a ping server be setup on that server and only proves that the server is running, not whether anyone outside of the box can get to it. It probably won't even go across the network if that IP address is the same server. So, it's largely pointless, because the server has to be running in order to run the code. And if the server is dead, it's not going to be able to run the test or tell anyone it is dead. You're not really answering the question of whether the server is available, because it's not testing the network other than maybe the local NIC. If the Ping service were to go down you would get a false alarm, although I've never heard of the ping service just crashing as it's a pretty simple and well tested piece of code (it's probably been the same service for over two decades - it's probably less than 100 lines of C code to write a ping server).

Not to mention that this script is not going to call itself. You're probably talking about a job here and I don't see this written as a stored procedure. Now that I think about it, you would be writing this as a SQL Agent job and as a separate service, I suppose it would be calling into the SQL Server, although I imagine the database connection would use shared memory; and the call to Ping is at best testing that the network card is operational, not whether the network is okay unless this is a cross server job. The router the server is connected to could be completely down and this would not notice that. Even you might not be able to get to the server across the network and this test would still be showing everything is okay.

SQL Server is a bit of a memory hog. It has it's own internal operating system that manages memory. So, it basically requests memory in large chunks from Windows. It also somewhat assumes that it owns the box, because in general that's the best way to run SQL Server in terms of ease of maintenance. It won't use memory unless it has a reason. You can put it on a 64GB box and it won't use any significant amount of memory in a year if nothing uses that SQL Server. But when it needs it, it will grab it. And once it grabs it, it probably won't let that memory go. It does try to play nicely with other apps and give memory back to the system if the system requests it, but in reality it's notorious for sponging up all the memory on the box and not giving it back. But there are settings to make it more or less aggressive. You can tell it to get more aggressive with CPU priority and set minimum and maximum amounts of memory to use.

Anyway, think about writing an external app or service to do this test. It needs to be external just to test the network connection. Plus, the loss of a connection is a pretty big clue something went wrong in and of itself without actually querying the database. TCP/IP is an continuous connection (as opposed to UDP/IP which is a momentary connection). So, if it suddenly drops, there was a problem.

In my experience, you don't need much of an alert when the server becomes unavailable if the users have your phone number. They're pretty quick to tell you about it.

This post has been edited by BBeck: 13 June 2016 - 04:31 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1