58_External_REST_Endpoints.sql

/**************************************************************
 * SQL Server 2025 External REST Endpoint Invocation Tutorial
 * Description: This script demonstrates sp_invoke_external_rest_endpoint
 *              introduced in SQL Server 2025 (17.x). It covers:
 *              - Enabling external REST endpoint feature
 *              - Creating database scoped credentials
 *              - Calling REST/GraphQL endpoints
 *              - Integrating with Azure services (Functions, Event Hubs, OpenAI)
 *              - Authentication methods (Headers, Query String, Managed Identity)
 *              - Error handling and retry logic
 *              - Best practices for REST endpoint integration
 **************************************************************/

-------------------------------------------------
-- Region: 1. Introduction and Setup
-------------------------------------------------
USE master;
GO

/*
  Enable the external REST endpoint feature.
  Requires ALTER SETTINGS server-level permission.
*/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

/*
  Create a test database for our REST endpoint examples.
*/
IF DB_ID('RestEndpointDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RestEndpointDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RestEndpointDemo;
END
GO

CREATE DATABASE RestEndpointDemo;
GO

USE RestEndpointDemo;
GO

-- Set compatibility level for SQL Server 2025 (17.x)
ALTER DATABASE RestEndpointDemo SET COMPATIBILITY_LEVEL = 170;
GO

-------------------------------------------------
-- Region: 2. Basic REST Endpoint Calls
-------------------------------------------------
/*
  sp_invoke_external_rest_endpoint allows calling HTTPS endpoints.
  Supported HTTP methods: GET, POST, PUT, PATCH, DELETE, HEAD
*/

-- 2.1 Simple GET request (no authentication)
DECLARE @ret INT, @response NVARCHAR(MAX);

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://httpbin.org/get',
    @method = 'GET',
    @response = @response OUTPUT;

SELECT 
    @ret AS ReturnCode,
    JSON_VALUE(@response, '$.response.status.http.code') AS HttpStatus,
    JSON_QUERY(@response, '$.result') AS ResponseData;
GO

-- 2.2 POST request with JSON payload
DECLARE @ret INT, @response NVARCHAR(MAX);
DECLARE @payload NVARCHAR(MAX);

SET @payload = JSON_OBJECT('name':'SQL Server 2025', 'version':17.0);

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://httpbin.org/post',
    @method = 'POST',
    @payload = @payload,
    @response = @response OUTPUT;

SELECT 
    @ret AS ReturnCode,
    JSON_VALUE(@response, '$.response.status.http.code') AS HttpStatus,
    JSON_QUERY(@response, '$.result.json') AS EchoedPayload;
GO

-- 2.3 Adding custom headers
DECLARE @ret INT, @response NVARCHAR(MAX);
DECLARE @headers NVARCHAR(4000);

SET @headers = JSON_OBJECT('X-Custom-Header':'MyValue', 'X-Request-ID':NEWID());

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://httpbin.org/headers',
    @method = 'GET',
    @headers = @headers,
    @response = @response OUTPUT;

SELECT 
    @ret AS ReturnCode,
    JSON_QUERY(@response, '$.result') AS ResponseData;
GO

-------------------------------------------------
-- Region: 3. Database Scoped Credentials
-------------------------------------------------
/*
  Use DATABASE SCOPED CREDENTIAL to securely store authentication data.
  Four authentication methods:
  1. HTTPEndpointHeaders - pass credentials in headers
  2. HTTPEndpointQueryString - pass credentials in query string
  3. Managed Identity - use Azure managed identity
  4. Shared Access Signature - use SAS tokens
*/

-- 3.1 Create master key (required for credentials)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
GO

-- 3.2 Credential with header authentication
-- Example for Azure Function with x-functions-key
CREATE DATABASE SCOPED CREDENTIAL [https://myapp.azurewebsites.net/api/myfunction]
WITH IDENTITY = 'HTTPEndpointHeaders', 
     SECRET = '{"x-functions-key":"your-function-key-here"}';
GO

-- 3.3 Credential with query string authentication
CREATE DATABASE SCOPED CREDENTIAL [https://myapp.azurewebsites.net/api/anotherfunc]
WITH IDENTITY = 'HTTPEndpointQueryString', 
     SECRET = '{"code":"your-code-here"}';
GO

-- 3.4 Credential with Managed Identity (for Azure resources)
-- Used to authenticate to Azure services using system or user-assigned managed identity
CREATE DATABASE SCOPED CREDENTIAL [https://myeventhub.servicebus.windows.net]
WITH IDENTITY = 'Managed Identity', 
     SECRET = '{"resourceid":"https://eventhubs.azure.net"}';
GO

-------------------------------------------------
-- Region: 4. Calling Azure Functions
-------------------------------------------------
/*
  Azure Functions are commonly used for serverless processing.
  This example shows how to call a function with authentication.
*/

-- 4.1 Call Azure Function with header authentication
/*
  Note: Replace <APP_NAME> and <FUNCTION_NAME> with your actual values.
  
  DECLARE @ret INT, @response NVARCHAR(MAX);
  DECLARE @payload NVARCHAR(MAX);
  
  SET @payload = JSON_OBJECT('orderId':12345, 'amount':299.99);
  
  EXECUTE @ret = sp_invoke_external_rest_endpoint
      @url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>',
      @method = 'POST',
      @credential = [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>],
      @payload = @payload,
      @response = @response OUTPUT;
  
  SELECT @ret AS ReturnCode, @response AS Response;
*/
GO

-- 4.2 Batch processing with Azure Function
CREATE TABLE dbo.OrdersToProcess
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    Amount DECIMAL(10,2),
    Processed BIT DEFAULT 0,
    ProcessedDate DATETIME2 NULL
);
GO

INSERT INTO dbo.OrdersToProcess (CustomerID, Amount)
VALUES (101, 150.00), (102, 275.50), (103, 89.99);
GO

-- Process orders by sending to Azure Function
/*
  DECLARE @orderId INT, @payload NVARCHAR(MAX), @ret INT, @response NVARCHAR(MAX);
  
  DECLARE order_cursor CURSOR FOR
  SELECT OrderID FROM dbo.OrdersToProcess WHERE Processed = 0;
  
  OPEN order_cursor;
  FETCH NEXT FROM order_cursor INTO @orderId;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      -- Build payload from order data
      SELECT @payload = (
          SELECT OrderID, CustomerID, Amount
          FROM dbo.OrdersToProcess
          WHERE OrderID = @orderId
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      );
      
      -- Call Azure Function
      EXECUTE @ret = sp_invoke_external_rest_endpoint
          @url = N'https://<APP_NAME>.azurewebsites.net/api/ProcessOrder',
          @method = 'POST',
          @credential = [https://<APP_NAME>.azurewebsites.net/api/ProcessOrder],
          @payload = @payload,
          @response = @response OUTPUT;
      
      -- Update processed status
      IF @ret = 0
      BEGIN
          UPDATE dbo.OrdersToProcess
          SET Processed = 1, ProcessedDate = SYSDATETIME()
          WHERE OrderID = @orderId;
      END
      
      FETCH NEXT FROM order_cursor INTO @orderId;
  END
  
  CLOSE order_cursor;
  DEALLOCATE order_cursor;
*/
GO

-------------------------------------------------
-- Region: 5. Integrating with Azure Event Hubs
-------------------------------------------------
/*
  Send messages to Azure Event Hubs for real-time data streaming.
  Uses Managed Identity for authentication.
*/

-- 5.1 Send event to Event Hubs
/*
  Note: Replace <EVENT-HUBS-NAME> with your actual Event Hubs namespace.
  
  DECLARE @payload NVARCHAR(MAX), @ret INT, @response NVARCHAR(MAX);
  DECLARE @eventId UNIQUEIDENTIFIER = NEWID();
  
  SET @payload = JSON_OBJECT(
      'eventId':CAST(@eventId AS NVARCHAR(36)),
      'eventType':'OrderCreated',
      'timestamp':SYSDATETIME(),
      'data':JSON_OBJECT('orderId':12345, 'amount':299.99)
  );
  
  DECLARE @url NVARCHAR(4000) = 'https://<EVENT-HUBS-NAME>.servicebus.windows.net/from-sql/messages';
  DECLARE @headers NVARCHAR(4000) = JSON_OBJECT(
      'BrokerProperties',
      JSON_OBJECT('PartitionKey', CAST(@eventId AS NVARCHAR(36)))
  );
  
  EXECUTE @ret = sp_invoke_external_rest_endpoint
      @url = @url,
      @method = 'POST',
      @headers = @headers,
      @credential = [https://<EVENT-HUBS-NAME>.servicebus.windows.net],
      @payload = @payload,
      @response = @response OUTPUT;
  
  SELECT @ret AS ReturnCode, @response AS Response;
*/
GO

-------------------------------------------------
-- Region: 6. Calling Azure OpenAI
-------------------------------------------------
/*
  Integrate with Azure OpenAI for AI-powered features.
  Generate embeddings, completions, or chat responses.
*/

-- 6.1 Generate embeddings from text
/*
  Note: Replace <openai-url> and <model-name> with your actual values.
  
  CREATE DATABASE SCOPED CREDENTIAL [https://<openai-url>.openai.azure.com]
  WITH IDENTITY = 'Managed Identity', 
       SECRET = '{"resourceid":"https://cognitiveservices.azure.com"}';
  GO
  
  DECLARE @ret INT, @response NVARCHAR(MAX);
  DECLARE @text NVARCHAR(MAX) = 'SQL Server 2025 new features';
  DECLARE @payload NVARCHAR(MAX);
  
  SET @payload = JSON_OBJECT('input':@text);
  
  EXECUTE @ret = sp_invoke_external_rest_endpoint
      @url = 'https://<openai-url>.openai.azure.com/openai/deployments/<model-name>/embeddings?api-version=2024-08-01-preview',
      @method = 'POST',
      @credential = [https://<openai-url>.openai.azure.com],
      @payload = @payload,
      @response = @response OUTPUT;
  
  -- Extract the embedding vector
  SELECT JSON_QUERY(@response, '$.result.data[0].embedding') AS EmbeddingVector;
*/
GO

-- 6.2 Store embeddings in a table
CREATE TABLE dbo.DocumentEmbeddings
(
    DocumentID INT IDENTITY(1,1) PRIMARY KEY,
    DocumentText NVARCHAR(MAX),
    Embedding NVARCHAR(MAX),
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);
GO

-------------------------------------------------
-- Region: 7. Error Handling and Retry Logic
-------------------------------------------------
/*
  Handle errors gracefully and implement retry logic.
*/

-- 7.1 Basic error handling
DECLARE @ret INT, @response NVARCHAR(MAX);

BEGIN TRY
    EXECUTE @ret = sp_invoke_external_rest_endpoint
        @url = N'https://httpbin.org/status/500', -- Simulates server error
        @method = 'GET',
        @response = @response OUTPUT;
    
    IF @ret <> 0
    BEGIN
        PRINT 'REST call failed with return code: ' + CAST(@ret AS NVARCHAR(10));
        PRINT 'Response: ' + @response;
    END
    ELSE
    BEGIN
        PRINT 'REST call succeeded';
    END
END TRY
BEGIN CATCH
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH
GO

-- 7.2 Using retry_count parameter
DECLARE @ret INT, @response NVARCHAR(MAX);

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://httpbin.org/status/503',
    @method = 'GET',
    @timeout = 10,
    @retry_count = 3, -- Retry up to 3 times on failure
    @response = @response OUTPUT;

SELECT @ret AS ReturnCode;
GO

-------------------------------------------------
-- Region: 8. Timeout Configuration
-------------------------------------------------
/*
  Configure timeout to prevent long-running calls.
  Default timeout is 30 seconds, maximum is 230 seconds.
*/

-- 8.1 Set custom timeout
DECLARE @ret INT, @response NVARCHAR(MAX);

EXECUTE @ret = sp_invoke_external_rest_endpoint
    @url = N'https://httpbin.org/delay/5', -- Simulates 5 second delay
    @method = 'GET',
    @timeout = 10, -- Wait up to 10 seconds
    @response = @response OUTPUT;

SELECT @ret AS ReturnCode;
GO

-------------------------------------------------
-- Region: 9. Monitoring and Diagnostics
-------------------------------------------------
/*
  Monitor REST endpoint calls using wait types and Extended Events.
*/

-- 9.1 Check for HTTP_EXTERNAL_CONNECTION wait type
-- When sp_invoke_external_rest_endpoint is waiting, it shows HTTP_EXTERNAL_CONNECTION
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'HTTP_EXTERNAL_CONNECTION';
GO

-- 9.2 Grant permission to use endpoint
-- Users need EXECUTE ANY EXTERNAL ENDPOINT permission
/*
  GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [YourUser];
*/
GO

-------------------------------------------------
-- Region: 10. Real-World Integration Examples
-------------------------------------------------
/*
  Practical scenarios for REST endpoint integration.
*/

-- 10.1 Webhook notifications on data changes
CREATE TABLE dbo.ImportantEvents
(
    EventID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(50),
    EventData NVARCHAR(MAX),
    EventDate DATETIME2 DEFAULT SYSDATETIME(),
    NotificationSent BIT DEFAULT 0
);
GO

CREATE OR ALTER TRIGGER tr_ImportantEvents_Notify
ON dbo.ImportantEvents
AFTER INSERT
AS
BEGIN
    DECLARE @eventData NVARCHAR(MAX);
    DECLARE @ret INT, @response NVARCHAR(MAX);
    
    -- Get inserted event data
    SELECT @eventData = (
        SELECT EventID, EventType, EventData, EventDate
        FROM inserted
        FOR JSON AUTO
    );
    
    -- Send webhook notification
    /*
    EXECUTE @ret = sp_invoke_external_rest_endpoint
        @url = N'https://your-webhook-url.com/notify',
        @method = 'POST',
        @payload = @eventData,
        @response = @response OUTPUT;
    
    IF @ret = 0
    BEGIN
        UPDATE dbo.ImportantEvents
        SET NotificationSent = 1
        WHERE EventID IN (SELECT EventID FROM inserted);
    END
    */
END;
GO

-- 10.2 Enrich data from external API
CREATE TABLE dbo.Customers
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerEmail NVARCHAR(100),
    CustomerData NVARCHAR(MAX),
    LastEnriched DATETIME2 NULL
);
GO

CREATE OR ALTER PROCEDURE dbo.EnrichCustomerData
    @customerId INT
AS
BEGIN
    DECLARE @email NVARCHAR(100);
    DECLARE @ret INT, @response NVARCHAR(MAX);
    
    SELECT @email = CustomerEmail
    FROM dbo.Customers
    WHERE CustomerID = @customerId;
    
    -- Call external API to get enriched data
    /*
    EXECUTE @ret = sp_invoke_external_rest_endpoint
        @url = N'https://api.enrichment-service.com/lookup?email=' + @email,
        @method = 'GET',
        @credential = [https://api.enrichment-service.com],
        @response = @response OUTPUT;
    
    IF @ret = 0
    BEGIN
        UPDATE dbo.Customers
        SET CustomerData = JSON_QUERY(@response, '$.result'),
            LastEnriched = SYSDATETIME()
        WHERE CustomerID = @customerId;
    END
    */
END;
GO

-------------------------------------------------
-- Region: 11. Best Practices
-------------------------------------------------
/*
  Best practices for using sp_invoke_external_rest_endpoint:
  
  1. Batch requests when possible to reduce overhead
  2. Use DATABASE SCOPED CREDENTIAL for secure authentication
  3. Implement proper error handling and retry logic
  4. Set appropriate timeouts based on expected response times
  5. Monitor with Extended Events and wait statistics
  6. Use async patterns in application layer for long-running calls
  7. Consider throttling limits (10% of worker threads, max 150)
  8. Test endpoint URLs and credentials thoroughly
  9. Document all external dependencies
  10. Implement logging for audit and troubleshooting
*/

-------------------------------------------------
-- Region: 12. Cleanup
-------------------------------------------------
/*
  Optional: Clean up the demo database.
*/
-- USE master;
-- GO
-- DROP DATABASE IF EXISTS RestEndpointDemo;
-- GO