SQL Server订阅到期自动置无效状态及Azure Function触发方案咨询

方案1：SQL Server代理作业+存储过程 #

这是贴近数据库原生能力的方案，适合对延迟要求不高的场景：

• 创建存储过程，负责识别到期订阅、更新状态，并触发后续消息通知逻辑
• 配置SQL Server代理定时执行该作业，频率根据业务需求设置（比如每日凌晨、每小时一次）

存储过程示例：

CREATE PROCEDURE MarkExpiredSubscriptionsAsInvalid
AS
BEGIN
    SET NOCOUNT ON;

    -- 暂存到期订阅记录，避免更新后无法获取数据
    DECLARE @ExpiredSubs TABLE (UserId INT, SubscriptionId INT);

    INSERT INTO @ExpiredSubs
    SELECT UserId, SubscriptionId
    FROM Subscriptions
    WHERE subscriptionEnd < GETUTCDATE() AND subscriptionIsValid = 1;

    -- 更新订阅有效性状态
    UPDATE Subscriptions
    SET subscriptionIsValid = 0
    WHERE subscriptionEnd < GETUTCDATE() AND subscriptionIsValid = 1;

    -- 调用订阅微服务API，传递到期数据触发RabbitMQ消息
    -- 可通过sp_OACreate发起HTTP请求（需开启OLE自动化），或结合Azure Logic Apps/Webhook实现
END

方案2：Azure Function定时触发器（推荐） #

更符合微服务架构的方案，将业务逻辑从数据库层剥离到服务层：

• 创建定时触发的Azure Function，执行频率按需配置（如每小时一次）
• Function核心逻辑：
  1. 连接SQL Server，筛选出到期且仍标记为有效的订阅
  2. 批量更新subscriptionIsValid为0
  3. 集成RabbitMQ客户端，向用户微服务发送订阅到期通知消息

C#核心代码示例：

public static async Task Run([TimerTrigger("0 */1 * * *")] TimerInfo myTimer, ILogger log)
{
    var sqlConnStr = Environment.GetEnvironmentVariable("SqlServerConnStr");
    var rabbitMqConnStr = Environment.GetEnvironmentVariable("RabbitMqConnStr");

    using (var sqlConn = new SqlConnection(sqlConnStr))
    {
        await sqlConn.OpenAsync();
        // 查询到期订阅
        var expiredSubs = await sqlConn.QueryAsync<Subscription>(
            @"SELECT UserId, SubscriptionId 
              FROM Subscriptions 
              WHERE subscriptionEnd < GETUTCDATE() AND subscriptionIsValid = 1");

        if (!expiredSubs.Any()) return;

        // 更新订阅状态
        await sqlConn.ExecuteAsync(
            @"UPDATE Subscriptions 
              SET subscriptionIsValid = 0 
              WHERE subscriptionEnd < GETUTCDATE() AND subscriptionIsValid = 1");

        // 发送RabbitMQ消息
        var factory = new ConnectionFactory { Uri = new Uri(rabbitMqConnStr) };
        using (var rabbitConn = factory.CreateConnection())
        using (var channel = rabbitConn.CreateModel())
        {
            channel.QueueDeclare(queue: "subscription-expired", durable: true, exclusive: false, autoDelete: false);
            
            foreach (var sub in expiredSubs)
            {
                var msg = JsonConvert.SerializeObject(new { sub.UserId, IsValid = false });
                var body = Encoding.UTF8.GetBytes(msg);
                channel.BasicPublish(exchange: "", routingKey: "subscription-expired", body: body);
            }
        }
    }
}

public class Subscription
{
    public int UserId { get; set; }
    public int SubscriptionId { get; set; }
}

方案3：Azure Logic Apps（低代码选项） #

无需编写代码，通过可视化配置实现流程：

1. 设置定时触发节点（如每小时执行）
2. 添加SQL Server节点，查询到期订阅
3. 添加更新节点，批量修改subscriptionIsValid状态
4. 集成RabbitMQ节点，发送通知消息

关键注意事项 #

• 统一使用UTC时间存储和判断，避免时区差异导致的到期时间偏差
• 处理并发冲突：可通过事务或行级锁防止重复处理同一条订阅记录
• 消息可靠性：RabbitMQ启用持久化队列+消息确认机制，避免通知丢失
• 执行频率平衡：过于频繁的定时任务会增加数据库压力，根据业务对延迟的要求调整

内容的提问来源于stack exchange，提问作者stefan de boer