Using "sp_execute_external_script" as alternative of "sp_invoke_external_rest_endpoint" in SQL Server

Question

We have a business requirement to be able to request an HTTP end-point when certain events occur in the database (an SQL Server database). We have a catalog of end-points which we control. We use both SQL Server 2019 and 2022.

Currently we use Service Broker External Activator, which works well but this technology is old and from what we understand not supported by Microsoft anymore. Also, the setup/implementation is rather complex and the management marked it as "not business viable".

When we work in Azure, we will use the new sp_invoke_external_rest_endpoint to call an Azure function that will make the request. This works fine and we will use it when in Azure.

Unfortunately this procedure is NOT available in the standard on-premise version of SQL Server 2022. This is why we plan to use EXECUTE sp_execute_external_script @language = N'Python' to request HTTP end-points.

We already use SQL Server Machine Learning Services in production so we have these available on both SQL Server 2019 and 2022. We tested that implementation and it appears to be working fine, although a bit slow.

We want to know: Is there a big downside of using Python to call HTTP endpoints that we are currently missing in our assessment?

Here is an example of the code we use for proof of concept:

CREATE PROCEDURE [dbo].[usp_APICalls_HTTPRequest]
    @URL NVARCHAR(100),
    @Method VARCHAR(50),
    @HeadersJSON NVARCHAR(MAX),
    @BodyJSON NVARCHAR(MAX) = NULL,
    @BodyParams NVARCHAR(MAX) = NULL,
    @StatusCode VARCHAR(50) = NULL OUTPUT,
    @ResponseBody NVARCHAR(MAX) = NULL OUTPUT
AS
    EXECUTE sp_execute_external_script @language = N'Python',
    @script = N'
import datetime
import json
import requests
 
print("")
print("Script started {:%Y-%m-%d %H:%M:%S}".format(datetime.datetime.now()))
print("")
 
objHeaders = json.loads(HeadersJSON)

if BodyJSON:
    payload = json.dumps(json.loads(BodyJSON))
else:
    payload = BodyParams
 
objReqResponse = requests.request(Method, URL, headers=objHeaders, data=payload)
 
objResponse = {
    "StatusCode": [objReqResponse.status_code],
    "Body": [objReqResponse.text]
}
 
StatusCode = str(objReqResponse.status_code)
ResponseBody = objReqResponse.text
 
print("")
print("Script complete {:%Y-%m-%d %H:%M:%S}".format(datetime.datetime.now()))
print("")
 
    ',
        @params = N'@URL NVARCHAR(100),
                    @Method VARCHAR(50),
                    @HeadersJSON NVARCHAR(MAX),
                    @BodyJSON NVARCHAR(MAX),
                    @BodyParams NVARCHAR(MAX),
                    @StatusCode VARCHAR(50) OUTPUT,
                    @ResponseBody NVARCHAR(MAX) OUTPUT',
        @URL = @URL,
        @Method = @Method,
        @HeadersJSON = @HeadersJSON,
        @BodyJSON = @BodyJSON,
        @BodyParams = @BodyParams,
        @StatusCode = @StatusCode OUTPUT,
        @ResponseBody = @ResponseBody OUTPUT
 
GO

Answer 1

There's no downside of using Python to do this over some other extension language like SQL CLR.

There is a downside of doing this synchronously within your transactions. So you should probably continue to use Service Broker and switch from external activation to internal activation. This is the architecture used by Database Mail to send SMTP requests, which have the same issues as HTTP requests.