用于Azure SQL数据库的SQL Server专用管理员连接的方法

吴欣
67 阅读6分钟

本文介绍了SQL Server专用管理员连接以及如何将其用于Azure SQL数据库。

简介

假设用户在生产环境中报告了性能和查询超时问题。为了开始调查，你试图用SQL Server Management Studio连接SQL Server。你无法连接到SQL实例，因为你的连接请求需要更长的时间，导致服务器不响应或超时等问题。

你采取了什么措施来解决性能瓶颈问题？如果你不能连接到DB并查看发生了什么，你只能回收SQL服务。在一个生产系统中，重启SQL Server可能是一个挑战，重启还可能由于服务重启前正在进行的事务而导致数据库恢复。

你如何在不重启SQL Service的情况下解决这个问题？

如果你考虑到Azure SQL数据库，你不能重启SQL服务，Azure管理服务和它们的配置。在这种情况下，SQL Server专用管理员连接可能会帮助你。

本文将探讨Azure SQL数据库专用管理员连接（DAC）以及如何使用它

SQL服务器专用管理员连接(DAC)

SQL Server专用管理员连接（DAC）是一个特殊的诊断连接，供数据库管理员在关键性能问题上访问数据库。有时，如果SQL Server处于压力之下，它可能对标准的用户连接没有反应，这可能是由于长期运行的查询、阻塞会话而发生的。 在这种情况下，DBA可以使用DAC进行连接，并执行诊断查询以解决问题。管理员可以访问SQL数据库，执行诊断查询，排除性能问题，即使是在服务器没有响应或处于异常状态下。

注意：SQL Server会尽力使DAC连接成功；但是，在极端情况下，它可能无法工作。

在企业内部的SQL Server中，我们可以完全控制基础设施、DB引擎服务、实例。DAC连接对企业内部的SQL版本是可用的。但是，如果你迁移到Azure SQL DB，一个管理的关系数据库服务，我们是否支持DAC连接？

默认情况下，DAC连接在1434端口的回环IP地址127.0.0.1上工作。因此，该连接只允许从服务器上运行的客户端进行。这意味着你需要RDP SQL Server并从服务器内部连接数据库引擎。

然而，你可以通过以下脚本在内部SQL Server中使用sp_configure来配置远程管理连接。

 sp_configure 'remote admin connections', 1;

Azure SQL数据库是一个管理的SQL数据库服务。因此，你不能完全控制SQL服务的配置。在Azure SQL数据库中，你不能运行sp_configure命令。如果你试图这样做，你会得到以下错误信息。

Sp_configure command

你可以使用sys.configuration检查远程管理连接的当前值。

  SELECT * FROM sys.configurations
  WHERE NAME = 'remote admin connections'

Verify configurations

我们不能改变Azure SQL DB中的远程管理连接配置。在下一节，让我们看看如何使用专用管理连接（DAC）连接到Azure SQL数据库。

如何连接到Azure SQL数据库的SQL Server专用管理员连接

Azure SQL DB支持使用SQLCMD或SSMS的DAC连接。要使用SSMS连接，请为你的Azure服务器名称（FQDN）添加前缀ADMIN： 。

例如，在我的例子中，Azure SQL服务器如下所示。

SQL Server Dedicated Administrator Connection

在SSMS的连接窗口中，输入服务器名称为Admin:[Azure SQL Server]。

SSMS connection

单击 "连接"。它失败了，出现了以下错误信息。

Failure message

你不应该尝试使用SSMS中的对象浏览器连接到DAC，因为它为获得一个实例的图形视图创建了多个连接。

在SSMS中，进入文件->新建->数据库引擎查询，尝试指定DAC连接。Azure SQL DB也会产生错误信息，如下图所示。

Database Engine Query

它需要对Azure DB DAC连接进行额外的配置。

  • 点击连接属性，输入Azure SQL数据库名称。
  • 在加密连接上打勾。

Specify DB name

点击连接，就可以用DAC连接连接到Azure SQL DB。在连接栏中，你可以看到，我们是用Admin: 前缀连接到Azure SQL Server，即DAC连接。

Check connection bar

连接后，你可以运行DMV来查看信息，如活动会话、请求状态、锁定信息和缓存状态。

-- Active Sessions
  SELECT * FROM sys.dm_exec_sessions 
  GO
  -- Requests Status
  SELECT * FROM sys.dm_exec_requests
  GO
  -- Locking Information
  SELECT * FROM sys.dm_tran_locks
  GO
  -- Cache Status
  SELECT * FROM sys.dm_os_memory_cache_counters

如果你想知道谁在使用DAC连接，你可以在Azure SQL数据库中使用以下查询。

SELECT session_id, host_name, program_name, client_interface_name, status
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_resource_governor_workload_groups AS wg
  ON s.group_id = wg.group_id
  WHERE wg.name = 'DACGroup';

Check DAC connection

使用SQLCMD的DAC连接连接Azure SQL数据库

SQLCMD是一个命令行工具，用于连接、执行T-SQL语句、系统过程和SQL Server数据库的脚本文件。它与Azure SQL也是兼容的。

如果你的实例中没有SQLCMD命令行工具，你可以使用链接docs.microsoft.com/en-us/sql/t…，下载并安装最新版本。

SQLCMD install

你也可以通过SQLCMD使用DAC访问Azure数据库。在SQLCMD中，我们添加以下参数。

  • -S：指定Azure SQL服务器的名称。不要为Azure服务器添加ADMIN: 前缀。
  • -U：指定SQL服务器管理员的用户名
  • -P：输入管理员用户的密码
  • - d:输入Azure SQL DB的名称。这是使用DAC连接时必须的。
  • -A：它指定了专用的管理员连接
SELECT session_id, host_name, program_name, client_interface_name, status
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_resource_governor_workload_groups AS wg
  ON s.group_id = wg.group_id
  WHERE wg.name = 'DACGroup';

Verify SQL version

注意： 要使用 Azure Active Directory进行认证，请使用-G开关。

DAC的限制

  • 对于Azure SQL数据库，你只能有一个SQL Server专用管理员连接。如果一个DAC连接处于活动状态，其他DAC连接将被拒绝。
  • 建议将DAC连接用于简单的诊断和故障排除情况。你不应该在DAC连接上运行可能导致阻塞或高资源利用率的资源密集型查询。例如，避免运行 不要运行DBCC CHECKDB、DBCC DBREINDEX或DBCC SHRINKDATABASE等命令。
  • SQL Server不在DAC连接上以并行模式执行查询。
  • 如果你在调查时发现任何会话导致性能问题，你可以杀了这个SPID。Kill会启动事务的回滚，它可能需要时间，这取决于受影响的行的数量。一旦你发出kill命令，你可以使用以下DMV来跟踪其状态。
    • sys.dm_exec_sessions。使用DMVsys.dm_exec_sessions检查SPID状态。如果它没有为一个特定的SPID返回任何行，就表明该SPID被杀死了。
    • sys.dm_os_tasks:在运行KILL SPID后，会话仍然存在于sys.dm_exec_sessions中；你可以使用sys.dm_os_tasks来查看是否有任何相关的任务。如果有一个现有的任务，这意味着交易回滚正在进行中。
    • 如果DMVsys.dm_os_tasks中没有任务*，而你在sys.dm_exec_sessions中有活动的会话，*这意味着没有工人可用。因此，运行T-SQL（Select * from sys.dm_os_tasks where sessions_id<>NULL）并杀死会话，以释放工作线程。

结论

SQL Server专用管理员连接（DAC）是DBA在资源瓶颈期间连接系统的一种方式，用于故障排除。正如我们在本文中所解释的那样，它在Azure SQL数据库上也得到了支持。你应该知道DAC连接，使用DAC连接的方式，因为在关键的生产问题中需要它，你可能没有时间在那段时间寻找文章。因此，请尝试为Azure SQL DB进行DAC连接，并熟悉这个过程。

avatar
文章
阅读
粉丝