主页 / dba / 问题 / 238844
Accepted
GoodwinSQL
Asked: 2019-05-23 17:28:02 +0800 CST

阻止对主要 AG 副本的读取访问，但在可读辅助副本上允许它

  • 772

我们有一个独特的情况，我们希望允许用户使用 SSMS 查询数据库的可读辅助副本以进行临时报告，但不允许他们从主副本中读取。我们已经设置了只读路由来实现这一点。这也是 SQL 2016 上的全部内容。

我最初的想法是在主副本和辅助副本上创建登录名，并授予对相关数据库的读取访问权限。然后我们会拒绝连接或禁用当前主副本上的登录。在 SSMS 中，用户可以使用 ApplicationIntent=ReadOnly 连接到侦听器并被路由到辅助副本，而无需接触主副本。

我们将使用基本逻辑在主副本服务器和辅助副本服务器上设置一个简单的作业：如果当前服务器 = 主然后禁用登录；如果当前服务器 = 辅助服务器，则启用登录。

问题是当登录在主服务器上被禁用时，我在连接到具有只读意图的侦听器时登录失败。当我在主副本上重新启用登录时，它工作得很好，并且连接被正确路由到可读的辅助副本。

我在主服务器上设置了一个跟踪，果然，我可以看到登录连接并在主副本上的 master 和 msdb 中运行一些系统类型查询——即使我在 SSMS 中使用 ApplicationIntent=ReadOnly 连接。我不确定这是否是 SSMS 在幕后所做的事情，或者它是否是通过只读路由过程的登录的默认行为。

以下是我在主数据库上使用快速分析器跟踪捕获的查询：

--msdb query

    select 
        case 
            when object_id('dbo.sysdac_instances') is not null then 1             
        else
            0 
        end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

以前有人处理过这种情况吗？看来我们基本上需要允许主副本上的登录连接权限，同时拒绝它对主副本上 AG 中的数据库的读取访问权限，但授予该登录权限以读取可读辅助副本上的数据库。

另一种选择是创建一个直接指向辅助副本的 DNS 条目，但我们不能保证副本始终是辅助副本，因为可能会发生故障转移。

sql-server sql-server-2016

5 个回答

  1. Best Answer

    看来我们基本上需要在主副本上允许登录连接权限 [...]

    这是完全正确的。用于连接的登录名必须能够连接到主服务器。

    当您ApplicationIntent=ReadOnly在指向 AG 侦听器的连接字符串中使用时，驱动程序最初会连接到主实例。这样 SQL Server 可以查看副本列表并查看是否有任何可用的在线可读副本发送给您。这记录在这里：

    只读应用程序意图和只读路由

    可用性组的主数据库处理传入的只读路由请求，并尝试查找连接到主副本并配置为只读路由的联机只读副本。客户端从主副本服务器接收回连接信息并连接到识别的只读副本。

    微软顶级人 Sean Gallardy 在 PFE 博客上对流程进行了很好的总结：

    查找已只读路由的连接

    1. 我们通过驱动创建连接
    2. 驱动通过监听器连接SQL Server，并传递相关信息
    3. SQL Server 注意到我们只读了路由设置并检查了配置
    4. SQL 服务器向驱动程序报告我们有一个可以使用的只读辅助
    5. SQL Server 将新的辅助信息发送给驱动程序
    6. 驱动程序使用给定信息创建与辅助节点的新连接

    关于您在问题中提到的其他项目：

    [...] 虽然拒绝它读取主数据库上的 AG 中的数据库，但授予该登录权限以读取可读辅助副本上的数据库。

    数据库级别的权限将在 AG 中复制 - 因此，如果您删除用户对主服务器的读取访问权限，他们也将失去对辅助服务器的读取访问权限。

    我对这种努力的目标有点困惑，因为理论上数据应该是相同的（或接近相同的）。出于性能原因，也许您根本不希望某些登录在主服务器上运行查询。表达只读意图并连接到侦听器确实是解决此问题的最直接方法。

    其他选项将涉及单独的硬件，或在您的应用程序中处理直接连接到正确副本的自定义编码（并在发生故障转移时重试逻辑，并在每个副本上安排作业以在故障转移的情况下禁用/启用登录， ETC）。

    • 5

  2. 我知道这是一个旧线程，但我们实际上有完全相同的要求 - 允许用户以只读意图连接并防止他们回退到主线程。

    原因很简单——我们的数据库很复杂，我们的生产系统不断地针对它们运行大量小查询，而我们的用户通常运行需要大量连接和后续锁定的繁重报告查询。由于对生产系统的影响，在任何情况下，我们都不能让用户针对主副本运行这些报告。诚然，他们极不可能回退到主节点，但它有可能发生，这是我们希望防止的。

    用户也有可能避开监听器并直接连接到主副本，我们显然也需要防止这种情况。

    我认为目前最好的解决方案是在数据库上启用 RCSI（由于同步辅助，我们已经支付了行版本控制的成本），所以如果来自报告查询的回退确实命中了主数据库，它至少不会阻塞或针对生产查询的死锁。但这并不理想，因为我们仍然有资源成本（临时数据库、内存、cpu 等）。

    如果有人有任何其他想法，我很想听听他们的意见。

    • 2

  3. 一个建议是您的计划如概述但与资源管理器结合使用以控制其他一些资源成本。

    或者，假设它不会对应用程序造成太大破坏。在这种情况下，您可以使用登录触发器来阻止报告运行，或者让频繁运行的作业终止与报告关联的 SPID（您可以通过使作业基于阈值来减轻老板的负担）。

    此外，登录触发器会起作用，但要注意将自己锁定在主触发器之外的风险。彻底复习并测试、测试、测试，这样您就不会发现自己试图记住如何在凌晨 3 点以管理员身份在最小模式下启动。

    我们在这里进行了一些头脑风暴，没有办法避免在我们知道的主节点上运行配置更改。

    我还会考虑为这一服务跳出 SQL Server 并查看应用程序负载平衡器。F5 如果有预算，HAProxy 过去使用开源社区版对我来说效果很好。

    • 1

  4. 我们有同样的要求。我很好奇使用登录触发器是否有效？

    创建一种方法来识别哪些用户是“只读”用户：授予他们“只读”的特定角色

    如果用户在此表中并且您正在主副本回滚，则在登录事件期间。

    如上所述，在尝试生产之前测试得很好。

    • 0

  5. 您可以尝试几种方法。

    1. 不使用 AG 侦听器将用户直接连接到辅助节点。一个奇特的变体在辅助节点上使用具有首选所有者的附加集群 IP 端点。如果辅助节点出现故障，这将允许 IP 端点故障转移到主节点。

    2. 不要向只读用户授予数据库级别的 SELECT 权限，并在辅助节点上授予以下服务器级别的权限：CONNECT ANY DATABASE 和 SELECT ALL USER SECURABLES。

    • 0

相关问题