专用管理连接（DAC）和单用户模式

数据库运维人员，在维护数据库时，有时会遇到一些特殊的情况，例如，SQL Server实例无法访问，此时需要用到管理员在紧急情况下专用的连接；有时，在做一些系统级别的配置修改时，当前数据库不能被其他用户访问，必须把数据库切换到单用户模式。居安思危，在系统正常运行时，多做一些演练，避免在真正出现异常时，手忙脚乱。先解释一下两个术语：

专用管理连接(Dedicated Administrator Connection， DAC)是SQL Server提供的一个特殊的诊断连接，用于连接数据执行诊断查询和故障排除，当其他任何方式都无法连接不到SQL Server时，DAC是唯一的方法。不是所有的Login都可以使用DAC，只有sysadmin 服务器角色的成员，才可以使用DAC。每个SQL Server实例，只能有一个DAC连接。如果当前已经有一个DAC连接，SQL Server抛出错误 17810。

单用户模式( Single-User mode)，是指只允许一个用户连接到SQL Server实例或数据库，通常情况下，单用户模式用于修复系统数据库等维护操作，例如，还原master数据库，修改实例级别的配置选项。

一，连接DAC

通常情况下，DBA通过sqlcmd 命令行工具访问SQL Server，登陆到SQL Server实例所在的主机(Host)，以Administrator身份运行DOS界面，输入命令：sqlcmd -A，参数-A指定的是Administrator选项。

默认情况下，DAC尝试去连接跟Login关联的默认的数据库，如果默认的数据库离线，或不可访问，DAC连接抛出错误 4060，可以使用 -d 参数指定登陆的数据库，推荐登陆master数据库，因为，一旦SQL Server实例启动成功，那么master数据库必定处于在线和可访问状态，sqlcmd 命令行代码如下：

sqlcmd –A –d master

sqlcmd的参数是大小写敏感的，常用参数是：

sqlcmd 
-S [protocol:]server[\instance_name][,port]
-E (use trusted connection)
-U login_id
-P password
-d db_name
-A (dedicated administrator connection)
-q "cmdline query"
-Q "cmdline query" (and exit)

二，单用户模式( Single-User mode)

SQL Server支持两种启用单用户模式的方法：在启动SQL Server实例时进入单用户模式，在SQL Server实例运行时把数据库切换到单用户模式，前者是SQL Server实例级别，后者是数据库级别。

1，启动参数(startup option)

启动参数用于在SQL Server 实例启动时，指定实例级别的选项，默认的启动参数是：

• -d master_file_path  ：用于指定master数据库的数据文件路径
• -l master_log_path   ：用于指定master数据库的日志文件路径
• -e error_log_path     ：用于指定存储错误日志的的文件路径

这些默认参数是在安装SQL Server时，系统自动指定的，DBA能够通过SQL Server 配置管理器(SQL Server Configuration Manager)查看和配置 Startup Parameters，如下图所示，通过Add按钮增加启动参数，通过Remove删除启动参数：

2，使用启动参数启用单用户模式

参数 -m ，以单用户模式开始SQL Server实例。使用该选项，当DBA以-m参数启动SQL Server实例时，DBA却无法登陆SQL Server实例，一个未知的客户端应用程序已经占用了唯一的连接，SQL Server实例抛出的异常是：当前已经有用户登陆，

参数 -mClientApplicationName 能够指定允许登陆的客户端应用程序，ClientApplicationName是大小写敏感的。

例如， 参数 -mSQLCMD 指定连接到SQL Server实例的连接必须是sqlcmd 客户端程序，参数 -m"Microsoft SQL Server Management Studio - Query" 指定连接到SQL Server实例的连接必须是SSMS。

3，在SQL Server实例运行时，把数据库切换到单用户模式

在设置数据库进入单用户模式(SINGLE_USER)时，确保数据库选项 AUTO_UPDATE_STATISTICS_ASYNC 被设置未OFF，这是一个后台进程，用于更新数据库的统计信息，当该选项被设置未ON，该后台进程会占用数据库的唯一的连接，这样，其他用户无法访问到数据，使用如下脚本禁用 AUTO_UPDATE_STATISTICS_ASYNC选项：

ALTER DATABASE database_name 
SET AUTO_UPDATE_STATISTICS_ASYNC OFF;

当把数据库切换到单用户模式时，如果其他用户已经连接到数据库，那么，该连接将会被关闭，SQL Server也不会提供任何警告。在切换到单用户模式时，通常会使用 WITH ROLLBACK IMMEDIATE 选项，这会导致未完成的事务立即回滚，并立即把其他连接断开。命令执行完成之后，数据库切换到单用户模式，等到维护操作结束之后，用户可以在当前的连接中执行命令，把数据库切换到多用户模式(MULTI_USER)，这样其他用户可以正常连接到数据库。

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

四，登陆触发器(Logon Trigger)

登陆触发器有时会阻止所有用户访问数据库，包括sysadmin角色的成员，当无法连接到SQL Server实例时，使用DAC是唯一的途径，DBA只能使用DAC登陆到数据库，删除登陆触发器，把数据库恢复到正常访问状态。

Step1：使用DAC连接到数据库，以管理员身份(Run as Administrator)启动DOS界面，使用sqlcmd连接DAC：

sqlcmd -A

Step2：删除登陆触发器，在命令行中输入以下命令，输入go (batch分隔符)，执行命令，删除登陆触发器

drop trigger login_trigger_name
on all server;
go

参考文档：

sqlcmd Utility

Diagnostic Connection for Database Administrators

Database Engine Service Startup Options

Start SQL Server in Single-User Mode

Set a Database to Single-user Mode