SQL Server 二〇〇七中等职业高校用管理员连接 (DAC) 使用技巧&修改系统表的秘籍

通过DAC来访问单机多SQL Server实例的情况要复杂一些。上面的几条命令行在这种情况下都会失效。原因在两个:
a) DAC访问是实例级别的,服务端得有办法知道你要访问的是哪个实例;
b) 在单机多实例的情况下监视DAC访问的是随机端口,而不再是默认的1434(当然,具体的端口号在SQL Server启动的时候是确定的,可以在SQL Server启动的Log中找到:打开SSMS--->连接到数据库实例--->Management--->SQL Server Logs--->Current,在里面找到类似”Dedicated admin connection support was established for listening locally on port 50458.“)

--怎么破?
我们在访问数据库引擎的时候,碰到单机多实例的情况有两种办法,一种是在配置S参数的时候加上实例名,一种是加实例端口号。命令行的形式类似下面:
sqlcmd -S myServer\InstanceName -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword
sqlcmd -S myServer,6xxx -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx,6xxx -U myUser -P myPassword

先从实例名着手:
sqlcmd -S myServer\InstanceName -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer\InstanceName -U myUser -P myPassword
sqlcmd -S ADMIN:xxx.xxx.xxx.xxx\InstanceName -U myUser -P myPassword

经测试确认,以上4种连接方式都是OK的。注意一点,对于InstanceName的解析是服务器上的“SQL Server Browser”进行的,如果这个服务不在运行,DAC的访问是要失败的。流程是:Browser根据“myServer\InstanceName”或者“xxx.xxx.xxx.xxx\InstanceName”找到你要访问的实例,然后根据“-A”或者“ADMIN:”找到你要访问的端口。

既然这样可以进行DAC访问,那用类似访问数据库引擎的方式,把上面命令中的“\InstanceName”改成",xxxx"格式的端口号是不是也行呢?
sqlcmd -S myServer,xxxx -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx,xxxx -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer,xxxx -U myUser -P myPassword
sqlcmd -S ADMIN:xxx.xxx.xxx.xxx,xxxx -U myUser -P myPassword

如果你在几个命令行中配的端口号跟访问数据库引擎时候配置的端口号是一样的话,答案是不行。原因在哪里呢?那个端口是数据库引擎的访问端口,并不是被监听的DAC端口,因为不在一个频道上DAC还不知道你想访问。我的理解,在命令行中指定了端口号的情况下,Browser认为那就是你想访问的端口,结果因为它并不是那个随机的DAC端口而导致了失败。

DAC访问侦听跟数据库引擎一样,从根本上来说也是一个tcp服务(关于这一点你可以查看sys.endpoints来确认)。是服务,我们如果能知道它侦听的端口号就应该能解决问题。但不幸也在这儿,如上面b)所说,在单机多实例的情况下这个被监听的端口是随机的。视图sys.endpoints是能查到当前SQL Server实例上的tcp服务信息的,每个endpoint都有一条记录,比如你就能在这里查到用于镜像的5022,但遗憾的是对于DAC,端口那一列却显示的是0.通过端口访问的这条路我没能走通。

SQL Server
为总指挥提供了一种独特的确诊连接,以供在不能够与服务器创设正式连接时接纳。尽管在
SQL Server 不响应规范连接须求时,管理员也可以动用此会诊连接待上访谈 SQL
Server,以便实践会诊查询并缓和难题。

C:\Documents and Settings\guoqiang>sqlcmd -S qiangguo\ods
1> quit

可以使用下面的SQL:
select s.session_id,
 s.login_time,
 s.login_name,
 s.host_name,
 p.endpoint_id,
 p.protocol_desc,
 p.name
from sys.dm_exec_sessions s
inner join sys.endpoints p on s.endpoint_id = p.endpoint_id

你可以从login_time,login_name,host_name来判断出哪一个是你当前的连接session,如果是DAC连接的话,你能从name列看到“Dedicated Admin Connection”。

使用 DAC 连接

暗许情状下,只好从服务器上运维的客户端创设连接。不允许开展网络连接,除非它们是应用带
sp_configure 存款和储蓄进程配置的。

只有 SQL Server sysadmin 剧中人物的成员能够利用 DAC 连接。

通过行使专用的指挥者按钮 (-A) 的 sqlcmd
命令提示实用工具,能够协理和使用 DAC。有关使用 sqlcmd
的详细新闻,请参阅。您还足以将前缀 admin: 连接到实例名上,格式为
sqlcmd -Sadmin:*<instance_name>。还足以由此连日到
admin:<
实例名*>,从 SQL Server Management Studio
查询编辑器运转 DAC。

5 怎样启用远程DAC连接?
独有 SQL Server sysadmin 剧中人物的分子能够使用 DAC
连接。暗许情形下,只可以从服务器上运转的顾客端创立连接。除非通过
sp_configure 使用 remote admin connections
选项举行配置,不然不允许行使网络连接。
DAC 匡助加密和 SQL Server 的其它安全性功效。DAC
只同意将客商上下文切换成别的管理客商。
暗许意况下,DAC 仅侦听环回 IP 地址 (127.0.0.1) 端口 1434。
remote admin connections 设置的可能值如下:
0 – 指明仅同意本地连接使用 DAC
1 – 指明允许远程连接使用 DAC
–启用远程DAC连接
sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO
在意:配置远程管理连接之后,会立时启用 DAC 侦听器而无需再次起动 SQL
Server,何况客户端能够立时远程连接到 DAC。

DAC(Dedicated Admin Connection)是SQL Server
二零零六引入的叁个东西,指标是在SQL
Server发生严重品质难题的时候仍保存少数的财富保险助理馆员能够实行一些大致的下令用于难点会诊、释放资源、杀死肇事进度等。微软官方对DAC的辨证:使用专项使用管理员连接.aspx)。对于DAC使用的一般景观,有八个准确的Blog值得推荐:

SQL Server 尽力使 DAC
连接成功,但在那么些例外的状态下也大概会油但是生三番五次战败。

没辙连接到 ADMIN:QIANGGUO\ODS。

上面包车型客车两篇blog涉及到的中坚是DAC访问单机单实例的图景。本文试图对DAC访谈单机多实例的境况也做个商量。

此专用管理员连接 (DAC) 协理 SQL Server 的加密效率和其他安全作用。DAC
只允许将客商上下文切换成别的管理客户。

7 科学普及错误
问题(1)
C:\Documents and Settings\guoqiang>sqlcmd -S qiangguo\ods,1434
HResult 0x274D,级别 16,状态 1
TCP 提供程序: 由于指标机器积极拒绝,无法连接。

--以下的形式都可以访问
sqlcmd -S myServer -U myUser -P myPassword -A
sqlcmd -S ADMIN:myServer -U myUser -P myPassword
sqlcmd -S myServer,1434 -U myUser -P myPassword
sqlcmd -S xxx.xxx.xxx.xxx -U myUser -P myPassword -A
sqlcmd -S xxx.xxx.xxx.xxx,1434 -U myUser -P myPassword

说明:
a) sqlcmd命令行中参数与参数值之间可以有空格也可以没有;如果你的密码中有空格,你可以用双引号把你的密码引起来;
b) sqlcmd命令中的“-S”其实也可以用"/S",其它参数也一样;
c) 1434是SQL Server连接的默认端口号;
d) 在服务器前加"ADMIN:"也是为了指定进行DAC连接;
e) “-A”是在sqlcmd命令行中指定DAC连接的参数;
f) "-A","ADMIN:",",1434"不能在一条命令中出现两个或以上,否则会报错;
g) 不在命令行中加入这3个参数("-A","ADMIN:",",1434")的任何一个,登录也能成功,差别在于你使用的连接是普通连接,不是DAC连接。一般来说,普通连接能用的资源更多,但是当系统性能出现严重问题的时候普通连接可能没法建立,这也是引入DAC的初衷;再就是DAC连接下能看到一些有助于诊断的秘密视图(参见上面推荐的第一个blog)。

限制

是因为 DAC 仅用于在极少数状态下会诊服务器难题,由此对连接有一点限量:

  • 为了确定保障有可用的总是财富,每种 SQL Server 实例只同意利用一个DAC。假设 DAC 连接已经激活,则经过 DAC
    进行再而三的任何新伏乞都将被驳回,并出现错误 17810。

  • 为了保留能源,SQL Server Express 不侦听 DAC 端口,除非采取追踪标记7806 举办运营。

  • DAC 最先尝试连接受与登入帐户关联的暗中同意数据库。连接成功后,能够连接到
    master 数据库。假诺暗中同意数据库脱机或不可用,则连年重临错误
    4060。不过,假若接纳以下命令覆盖暗许数据库,改为连日来到 master
    数据库,则连年会大功告成:

    sqlcmd –A –d master

    出于只要开动数据库引擎实例,就能够保险 master
    数据库处于可用状态,因而提议使用 DAC 连接到 master 数据库。

  • SQL Server 禁用 DAC 运转并行查询或指令。举例,假使运用 DAC
    试行下列任何语句,都会生成错误 3637。

    • RESTORE

    • BACKUP

  • DAC 只好选拔轻易的财富。请勿使用 DAC
    运转要求费用大批量能源的询问(举例,对大型表实施复杂的接入)或恐怕导致堵塞的查询。那推动堤防将
    DAC
    与其余现成的服务器难点混淆。为了制止发生地下的不通境况,若是非得施行恐怕会产生阻塞的查询,则尽量在依附快速照相的割裂品级下运营查询;或然,将业务隔绝品级设置为
    READ UNCOMMITTED,将 LOCK_TIMEOUT 值设置为不够长的值(如 3000飞秒),或然同一时间推行那二种操作。这足以免守 DAC
    会话被卡住。可是,依照 SQL Server 所处的气象,DAC
    会话可能会在闩锁上被封堵。能够接纳 CNTSportageL-C 终止 DAC
    会话,但不可能确定保证一定成功。假使失利,独一的抉择是重新起动 SQL
    Server。

  • 为力保连接成功并清除 DAC 故障,SQL Server 保留了一定的能源用于拍卖
    DAC
    上运营的命令。常常那一个财富只够实施轻易的确诊和故障排除功用,如下所示。

即使理论上得以运作任何不必在 DAC 上并行施行的 Transact-SQL
语句,但努力建议您限制使用下列会诊和故障排除命令:

  • 查询动态处理视图以实行着力的确诊,例如查询 sys.dm_tran_locks
    以通晓锁定状态,查询 sys.dm_os_memory_cache_counters
    以检讨缓存品质,查询 sys.dm_exec_requests 和
    sys.dm_exec_sessions
    以询问活动的对话和哀求
    。制止使用需求开销大批量财富的动态管理视图(比方,sys.dm_tran_version_store
    扫描整个版本存储区,何况会促成大批量的
    I/O)或应用了复杂连接的动态管理视图。有关质量影响的音信,请参阅特定的文书档案。

  • 查询目录视图。

  • 基本 DBCC 命令,例如 DBCC FREEPROCCACHE、DBCC
    FREESYSTEMCACHE、DBCC DROPCLEANBUFFERS, 和 DBCC
    SQLPERF
    。请勿运营须要消耗多量资源的一声令下,如 DBCC CHECKDB、DBCC
    DBREINDEX 或 DBCC SHRINKDATABASE。

  • Transact-SQL KILL <spid> 命令。根据 SQL Server
    的景观,KILL 命令并不是一定会顺理成章;即使失利,则独一的取舍是再次开动
    SQL Server。下面是形似的辅导标准:

    • 请通过查询
      SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>
      来验证 SPID
      是还是不是已被实际终止。若无回去任何行,则注脚会话已被甘休。

    • 若是会话仍在运维,则经过运维查询
      SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>
      来验证是还是不是为此会话分配了职责。即使开掘还也是有职分,则很恐怕当前正值终止会话。注意,此操作只怕会不断很短日子,也或然平素不会中标。

    • 设若在与此会话关联的 sys.dm_os_tasks
      中从未别的职分,可是在实践 KILL 命令后该会话还是出现在
      sys.dm_exec_sessions
      中,则表明未有可用的行事线程。选取某些当前正在运作的天职(在
      sys.dm_os_tasks 视图中列出的 sessions_id <> NULL
      的天职),并甘休与其涉及的对话以自由工作线程。请小心,终止单个会话可能相当不足,只怕须求结束三个会话。

1 什么是专用管理员连接?
SQL Server 二零零六为协会者提供了一种独特的确诊连接,以供在不可能与服务器营造规范连接时利用。

我通过测试得到的结论是:对于单机单SQL Server实例,使用非默认端口时候的DAC访问跟使用默认端口1433时候完全一样。网上的一些论坛说要确保“SQL Server Browser”在运行,似乎不是必要的,至少我测试(用的SQL Server 2008 R2 SP3)过程中“SQL Server Browser”是不是在运行不影响访问。

示例

在此示例中,管理员开掘服务器 URAN123
不响应,因而要确诊该难点。为此,客商激活 sqlcmd
命令提醒实用工具,并使用 -A 指明 DAC 连接到服务器 URAN123

sqlcmd -S URAN123 -U sa -P <xxx> –A

近来,管理员能够试行查询来会诊难题,并且能够告一段落甘休响应的对话。

问题(3)
C:\Documents and Settings\guoqiang>sqlcmd -Sadmin:qiangguo\ods
Sqlcmd: 错误: Microsoft SQL Native Client :
由于展开服务器连接进度中的延迟,无法
姣好报到进度。
缓慢解决:试着永不DAC 连接,用常规的连天成功后,再用DAC连接。
如:
C:\Documents and Settings\guoqiang>sqlcmd -S qiangguo\ods -A
Sqlcmd: 错误: Microsoft SQL Native Client :
由于张开服务器连接过程中的延迟,不可能
成功报到进度。

3)单机多SQL Server实例

DAC 端口

SQL Server 在运维数据库引擎时动态分配的专项使用 TCP/IP 端口上侦听
DAC。错误日志包罗所侦听的 DAC 所在的端口号。暗中认可情形下,DAC
侦听器只接受本地端口上的两次三番。有关激活远程管理员连接的代码示例,请参阅

配备远程管理连接之后,会即时启用 DAC 侦听器而不必再次启航 SQL
Server,而且客户端能够登时远程连接到 DAC。通过先在该地利用 DAC 连接到
SQL Server,然后再奉行 sp_configure 存储进度接受远程连接,则便是SQL Server 甘休响应,DAC 侦听器照旧还不错远程连接。

对此会集配置,DAC 在默许境况下是剥夺的。客商能够实施 sp_configure
remote admin connection 选项,使 DAC 侦听器能够采访远程连接。假如SQL Server 截止响应况兼未启用 DAC 侦听器,则只怕必须重新起动 SQL Server
来连接 DAC。因而,提议在集结系统上启用 remote admin connections
配置选项。

DAC 端口由 SQL Server 在运维时动态分配。当连接到默许实例时,DAC
会制止在延续时对 使用 SQL Server 解决左券 (SSRP) 要求。它先经过 TCP 端口
1434 进行连接。假若退步,则经过 SSRP 调用来赢得端口。倘使 SQL Server
浏览器未有侦听 SSRP 乞请,则连年央浼将赶回错误。若要驾驭 DAC
所侦听的端口号,请参阅错误日志。借使将 SQL Server
配置为接受远程管理连接,则必需使用显式端口号运转 DAC:

sqlcmd –Stcp:*<server>,<port>*

SQL Server 错误日志列出了 DAC 的端口号,暗中同意情状下为 1434。如若将 SQL
Server 配置为只接受本地 DAC 连接,请使用以下命令和环回适配器进行连接:

sqlcmd –S127.0.0.1,1434

2 专项使用助理馆员连接有怎么着用?
固然在 SQL Server 不响应标准连接供给时,管理员也足以选择这种连接待上访谈 SQL
Server,以便实行会诊查询并减轻难点。
组织者能够通过 DAC 访谈正在运维的 SQL Server Database Engine
实例来打消服务器的故障(即便该服务器已偃旗息鼓响应其余客商端连接)。

相关文章