One of our clients has a public web page and they needed to ensure that it is always up and accessible. Pythian already has a monitoring stack that includes website and pages monitoring.
I wondered if this could be done from SQL Server using built-in Windows modules to access external web resources. There are various the methods we can use.
Adam Machanic has a nice blog describing how to use Object Automation extended stored procedures. That code, however, was designed for SQL Server 2000; to use it in SQL Server 2005 and after, you will need to turn on OLE Automation using sp_configure.
Adam also recommended using CLR for SQL 2005 and later as it is safer, more stable, and more capable, which is completely true.
Another old-fashioned method is using a VBSCRIPT file, then calling it using XP_CMDSHELL and Windows Script Host (WSH).
The VBScript should work like this:
This is the VBS script:
‘ Parameter holding URL
url= WScript.Arguments.Item(0)
‘url= "https://www.pythian.com/mawla"
‘ Using MSXML 6.0 ; shipping with Win Vista , Win7, win 2008 and is installed with Win server 2003 Sp2
‘ Download it for older versions
‘ https://www.microsoft.com/downloads/details.aspx?FamilyID=993C0BCF-3BCF-4009-BE21-27E85E1857B1;displaylang=en
Set HttpRequest = CreateObject("Msxml2.XMLHTTP.6.0")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("Wscript.Shell")
‘ Create text file beside the script file. Should have proper permissions.
‘ Don’t use CurDir or Oshell.currentdirectory global variables, they will refer to %windows%\system32
Set obj_File = objFSO.createTextFile(oFSO.GetParentFolderName(Wscript.ScriptFullName) ; "\check_url.txt", true )
‘ Make the connection
HttpRequest.open "HEAD", Url , False
On Error Resume Next
HttpRequest.send
If Err.Number <> 0 Then
‘ Error happend , write to file
obj_File.Write "Error : "
obj_File.Write Err.Number
obj_File.Write ": "
obj_File.Write Err.Description
‘WScript.echo "Error : "
‘WScript.echo Err.Number
‘WScript.echo ": "
‘WScript.echo Err.Description
Else
‘ No Errors , write URL status to file
obj_File.Write "HTTP status: "
obj_File.Write HttpRequest.status
obj_File.Write " "
obj_File.Write HttpRequest.statusText
‘WScript.echo "HTTP status: "
‘WScript.echo HttpRequest.status
‘WScript.echo " "
‘WScript.echo HttpRequest.statusText
End If
Calling the code should be easy using Xp_cmdshell (which must be enabled) and CSscript
DECLARE @url NVARCHAR(2000)
SET @url = ‘https://www.pythian.com/’;
EXEC (‘Exec master..Xp_cmdshell ”Cscript //B c:\app\check_url.vbs “‘+@url+‘””’) ;
GO
EXEC master..xp_cmdshell ‘type c:\app\check_url.txt’
GO
HTTP status: 200 OK
CLR: The more elegant, safer, newer, and more stable way.
By creating a CLR stored procedure that takes URL as a parameter, we are using the System.Net namespace as the basis here; it provides a simple programming interface for many of the protocols used on networks. I used the WebRequest and WebResponse classes to interact with the URL and return its status using HttpStatusCode. For FTP, I used FTPWebRequest and FtpWebResponse.
The URL can be HTTP, HTTPS, or FTP, and must begin with “%Protocol%://”. I wrote the CLR in C# using Microsoft Visual Studio 2008. The code is quite simple, so it can be easily written in Notepad, saved as .CS file, and compiled using C# compiler CSC.EXE.
Here is the code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Web;
using System.Net;
public partial class check_url_status
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void check_url(string url)
{
if (url.Trim().Substring(0, 6).ToLower() != "ftp://" &amp;amp;amp;amp;amp; url.Trim().Substring(0, 7).ToLower() != "https://" &amp;amp;amp;amp;amp; url.Trim().Substring(0, 8).ToLower() != "https://")
{
SqlContext.Pipe.Send("URL must be a valid HTTP:// , HTTPS:// Or FTP:// URL ");
return;
}
if (url.Trim().Substring(0, 7).ToLower() == "https://" || url.Trim().Substring(0, 8).ToLower() == "https://")
{
try
{
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url.Trim());
using (HttpWebResponse rsp = (HttpWebResponse)req.GetResponse())
{
SqlContext.Pipe.Send(rsp.StatusCode.ToString());
}
}
catch (WebException e)
{
SqlContext.Pipe.Send(e.ToString().Substring(0, e.ToString().IndexOf(" at ")));
}
}
else if (url.Trim().Substring(0, 6).ToLower() == "ftp://")
{
try
{
FtpWebRequest req = (FtpWebRequest)WebRequest.Create(url.Trim());
using (FtpWebResponse rsp = (FtpWebResponse)req.GetResponse())
{
SqlContext.Pipe.Send(rsp.StatusCode.ToString());
}
}
catch (WebException e)
{
SqlContext.Pipe.Send(e.ToString().Substring(0, e.ToString().IndexOf(" at ")));
}
}
}
};
There are few notes regarding the CLR.
The CLR permission level is defined as “External” as it accesses external resources (Web resources). This is set from project properties > Database tab.
The assembly should be signed for the purpose of later creating the assembly in SQL Server.
To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, or to load an assembly, one of two conditions must be met as mentioned in referenced link.
You can sign the assembly from project properties > signing tab
After creating the DLL comes creating SQL stored procedure based on the assembly:
EXEC master..sp_configure 'clr enabled', 1
RECONFIGURE
GO
USE master
CREATE ASYMMETRIC KEY check_url FROM EXECUTABLE FILE = 'c:\app\check_url.dll'
GO
CREATE LOGIN check_url FROM ASYMMETRIC KEY check_url GRANT EXTERNAL ACCESS ASSEMBLY TO check_url
CREATE assembly Check_url FROM 'c:\app\check_url.dll'WITH PERMISSION_SET=EXTERNAL_ACCESS
CREATE PROCEDURE Check_url (@url NVARCHAR(MAX))AS external name Check_url.check_url_status.check_url ;
GONote that the URL parameter is STRING in C# code and should be either Nvarchar or nchar in SQL Server. This is a table of mapping CLR parameter data.
EXEC Check_url @url = 'HTTP://WWW.Pythian.COM'GO
OK
NOTE:
Ready to future-proof your SQL Server investment?