Calling a website...

Question

Calling a website...

Ray Laubert

SSCrazy

Points: 2910
More actions
April 9, 2010 at 2:23 pm

#146411

I am trying to create a job that will determine the round trip time from SQL to a website and back again. The website uses a database and I want to track the total response time periodically as well as alert when the time is outside a range.
I have never tried this before, but it must be possible. Anyone know how to do it?
Thanks
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

Viewing 8 posts - 1 through 7 (of 7 total)

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

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

I can't think of a way to do this from within SQL but we've done similar stuff in our application from the client side. You can just have the script/application set a timestamp when it makes the call and when it gets a response then log that to either a file on the web server or the DB.

Ray Laubert SSCrazy Points: 2910 More actions · Answer 2

There is no client app in this case, well there is but I don't have control over it, hence the desire to do this from SQL. It will be useful for several items, including alerts on website performance etc.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

LutzM SSC Guru Points: 107049 More actions · Answer 3

If the communication between website and SQL Server is done through procedures only (which I would assume/recommend/expect) then you could add a begin and end timestamp within your procedures and react based on the result (either store it in the db, send an alert or return it to the calling app).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

You are looking for latence then forget the app layer... just ping it. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I'd tend to agree with PaullB, you can get pretty far with ping. That said - if you're worried about the actual web site responsivenesss, then you'll need to investigate implementing this via CLR procedure.

A quick Google search yields a lot of good choics. This looked to be fairly straightforward:

http://www.generation-nt.com/us/answer/get-url-clr-help-123033421.html

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ray Laubert SSCrazy Points: 2910 More actions · Answer 6

Ping will not accomplish what I need as the web page must process the request that will be part of the website url.

What I want to do is:

declare @startime datetime

declare @stoptime datetime

set @startime = getdate()

-- go to url such as http://www.123.com/Request.htm/?S=234

set @stoptime = getdate()

insert perfomance (startime, stoptime)

values (@startime, @stoptime)

Put this into a store proc and then create a job that runs every so often to verify the site is responding and the response time. A set of alerts can then notify us if there is either an long delay in the response time or if the site does not respond.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

Lowell SSC Guru Points: 323504 More actions · Answer 7

Ray it's easy to do, but trying to do it in TSQL is not a good implementation.

TSQL's the wrong tool for this job.

I'd say it's much better and easier to do in say, .NET or another programming language, and have that application scheduled to run on a schedule; then that app can test the site, and write results to a file or directly to a table in your database.

since TSQL can't read a web page, you end up having to use those types of resources and then call them from TSQL....kind of a waste do part in a program, and the schedule/call in TSQL?, so i'd say do EVERYTHING at the program level, instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!