SQL SERVER 中 HTTP 请求

1. 背景
    对于大多数接口，我们需要监控其是否可以正常访问；
   或者再IIS部署的接口，由于长时间不访问(默认20分钟)、重启，接口处于待激活状态，再第一次请求的时候会变成慢，需要有个定时器定时访问
2.  目前业务场景
   .Net Core 下 RabbitMQ的消费服务，正常IIS部署上去后，无法注册、启动MQ服务，需要访问其中接口方法一次(HeathCheck),才能注册，已经设置此站点IIS不自动回收，但是服务器重启会导致MQ服务不能启动。
3. 解决方案
   通过SQL Server 中对http请求，来做定时服务，并记录请求结果
4. 设计 实现(GET)
    
   1>http 请求配置表和日志记录表

   -- 请求配置
   CREATE TABLE [dbo].[HttpTimerConfig](
   	[HttpId] [int] IDENTITY(1,1) NOT NULL,
   	[HttpName] [varchar](500) NULL,
   	[HttpRequestUrl] [varchar](500) NULL,
   	[IsDelete] [bit] NOT NULL CONSTRAINT [DF_HttpTimerConfig_IsDelete]  DEFAULT ((0)),
   	[Remark] [nvarchar](500) NULL,
   	[CreateTime] [datetime] NULL CONSTRAINT [DF_HttpTimerConfig_CreateTime]  DEFAULT (getdate()),
    CONSTRAINT [PK_HttpTimerConfig_1] PRIMARY KEY CLUSTERED 
   (
   	[HttpId] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   
   --请求记录表
   CREATE TABLE [dbo].[HttpRequestLog](
   	[LogId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_httpRequestLog_LogId]  DEFAULT (newid()),
   	[RequestUrl] [varchar](500) NULL,
   	[HttpType] [varchar](50) NULL,
   	[RequestName] [nvarchar](500) NULL,
   	[RequestTime] [datetime] NULL,
   	[ResponseTime] [datetime] NULL,
   	[ResponseStatus] [int] NULL,
   	[ResponseText] [nvarchar](max) NULL,
   	[CreateTime] [nchar](10) NULL,
    CONSTRAINT [PK_httpRequestLog] PRIMARY KEY CLUSTERED 
   (
   	[LogId] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   
   GO

   2> 新建Http Get访问核心存储过程(参照网络)
    

   --1 开启Sql Server 通讯配置--
   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'Ole Automation Procedures', 1;
   GO
   RECONFIGURE;
   GO
   EXEC sp_configure 'Ole Automation Procedures';
   GO
   
   --2 创建请求http Get方式 存储过程
   
   CREATE PROCEDURE PRO_GET_HttpRequestData(
   	@URL varchar(500),
   	@status int=0 OUT,
   	@returnText varchar(2000)='' OUT
   )
   AS
   BEGIN
   	DECLARE @object int,
   	@errSrc int
   	/*初始化对*/
   	EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT
   	IF @status <> 0
   	BEGIN
   	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
   	 RETURN
   	END
    
   	/*创建链接*/
   	EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
   	IF @status <> 0
   	BEGIN
   	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
   	 RETURN
   	END
   	EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
   	/*发起请求*/
   	EXEC @status= SP_OAMethod @object,'send',NULL 
   	IF @status <> 0 
   	BEGIN 
   	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
   	 RETURN
   	END
        
   	/*获取返回*/
   	EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT
   	IF @status <> 0 
   	BEGIN 
   	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
   	 RETURN
   	END
   END;
   

   3> 新建获取配置访问http和记录日志的存储过程
    

   CREATE PROCEDURE  PRO_HttpRequestCammand
   AS
   BEGIN
      DECLARE @returnText VARCHAR(500);
      DECLARE @status INT;
      DECLARE @RequestTime DATETIME;
      DECLARE @ResponseTime DATETIME;
      ---------------------------------
      DECLARE  @HttpName VARCHAR(500);
      DECLARE  @HttpRequestUrl VARCHAR(500)
      DECLARE  Cur_HttpInfo CURSOR FOR
      SELECT   HttpName,HttpRequestUrl  FROM HttpTimerConfig WHERE IsDelete=0
      OPEN Cur_HttpInfo
      FETCH NEXT FROM Cur_HttpInfo  INTO @HttpName,@HttpRequestUrl
      WHILE @@FETCH_STATUS = 0
       BEGIN
   	   SET @RequestTime=GETDATE();
   	   EXEC PRO_GET_HttpRequestData @HttpRequestUrl, @status OUTPUT, @returnText OUTPUT;
   	   SET @ResponseTime=GETDATE();
   	   INSERT HttpRequestLog(RequestUrl,HttpType,RequestName,RequestTime,ResponseTime,ResponseStatus,ResponseText)
   	   VALUES(@HttpRequestUrl,'GET',@HttpName,@RequestTime,@ResponseTime,@status,@returnText)
          FETCH NEXT FROM Cur_HttpInfo Into  @HttpName,@HttpRequestUrl
       END
   CLOSE Cur_HttpInfo
   DEALLOCATE Cur_HttpInfo 
   
   END

   4>配置HttpTimerConfig 数据
   
   5> 执行PRO_HttpRequestCammand 过程后，就会在HttpRequestLog 记录日志

5.  当然可以把PRO_HttpRequestCammand  配置计划作业，定时访问！