Pythian Blog: Technical Track

Check URL status from SQL server

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.

Object Automation extended stored procedures

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.

VBSCRIPT

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

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;amp; url.Trim().Substring(0, 7).ToLower() != "https://" &amp;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

Sign Assembly

After creating the DLL comes creating SQL stored procedure based on the assembly:

  1. Enable CLR
    EXEC master..sp_configure 'clr enabled'1 

    RECONFIGURE 

    GO 

  2. Adhering to the requirement of creating an EXTERNAL_ACCESS assembly, we will create an ASYMMETRIC KEY and a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.USE masterGO

     

    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_urlGO

  3. Create the assembly with External_access:CREATE assembly Check_url FROM 'c:\app\check_url.dll' 

    WITH PERMISSION_SET=EXTERNAL_ACCESSGO

  4. Create the stored procedure: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.

  5. Test the code:EXEC Check_url @url 'HTTP://WWW.Pythian.COM' 

    GO

    OK

    NOTE:

    • For FTP, there must be a destination file and not only a folder or root site (example : “ftp://ftp.gnu.org/README”). This is how FTPWebRequest works, but I’m not sure why.
    • Sometimes links are redirected. The maximum number of redirects that the request follows can be controlled by MaximumAutomaticRedirections property.

No Comments Yet

Let us know what you think

Subscribe by email