DAC(Dedicated Admin Connection)是SQL Server
2005引入的一个东西,目的是在SQL
Server发生严重性能问题的时候仍保留有限的资源保证管理员能够执行一些简单的命令用于问题诊断、释放资源、杀死肇事进程等。微软官方对DAC的说明:使用专用管理员连接.aspx)。对于DAC使用的一般情况,有两个不错的Blog值得推荐:

图片 1

在sql server中,我们经常能用到连接,今天总结一下连接的基础知识。
连接的分类:

运行 cmd -> 输入 netsh winsock reset
重启后 应该可以连接sql了

上面的两篇blog涉及到的基本是DAC访问单机单实例的情况。本文试图对DAC访问单机多实例的情况也做个探讨。

 

  • 交叉连接CROSS JOIN
  • 内连接INNER JOIN
  • 外连接{左外连接LEFT [OUTER] JOIN ;右外连接RIGHT [OUTER]
    JOIN;全外连接full [outer] join}
  • 自连接

1)单机单SQL Server实例,且SQL Server实例使用默认端口(1433)

以下通过例子来了解各个连接的异同点:

--以下的形式都可以访问
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)。

有两张表Teacher表和Course表:

2)单机单SQL Server实例,SQL Server实例使用非默认端口

图片 2

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

图片 3

3)单机多SQL Server实例

交叉连接:

通过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.通过端口访问的这条路我没能走通。

1.如果不带WHERE条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积;

4)DAC访问与防火墙

select * from Course cross join Teacher
如果有人通过我上面提到的有效的方式进行DAC访问却不幸失败了,也请不要奇怪。抛开端口劫持等特殊情况,DAC访问失败最常见的就是受到防火墙设置的拦截。对于上面提到的两种单机单实例的情况,只要确认服务端配置了允许对tcp1434端口的访问,DAC连接应该是没有问题的;复杂的情况仍然是单机多实例。

对于单机多实例的情况,由于DAC侦听的端口是随机的,不能指定,所以我们没法在防火墙上给它开口子,除非关闭防火墙。事实上,我在测试的时候就是让服务器上的Windows防火墙对域范围内的访问不起作用(关闭针对域内部访问的拦截)的。那要想从外网访问怎么办?总不能为了一个DAC连接把这台服务器赤裸裸的暴露出来(给它配外网IP)或者关掉公司的级别的防火墙吧?这倒不必,可以用VPN或者端口映射从防火墙上开个小口子,这样你就能连接到局域网,从那进行DAC访问。

结果为:

5)如何确认当前是DAC连接还是普通连接

图片 4

可以使用下面的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”。

由此结果可知,它的结果与 SELECT * FROM Course,Teacher 的结果相同。

2.如果有WHERE子句的话,往往会先生成两个表行数乘积的数据表然后才根据WHERE条件从中选择。

1. select * from Course,Teacher where Course.T#=Teacher.T#
2. select * from Course cross join Teacher where Course.T#=Teacher.T#  (注:cross join后加条件只能用where,不能用on)
3. select * from Course inner join Teacher on Course.T#=Teacher.T#

结果为:

图片 5

一般情况下,在效率上,Where可能具有和Inner join一样的效率,但是,在多表连接时,我们并不推荐使用where语句。
所以如果可以选择,我们最好使用语句3,有时使用Join语句可以帮助检查语句中的无效或者误写的关联条件。

内连接

内连接表示两边表同时符合条件的组合,就相当于普通的CROSS
JOIN,只是格式不一样,
INNER
JOIN在后面有一个ON子句(相当于WHERE)的搜索条件,用于过滤返回的行。
内连接没有笛卡尔积那么复杂要先生成行数乘积的数据表,所以内连接的效率要高于笛卡尔积的交叉连接。

外连接

指定条件的内连接,仅仅返回符合连接条件的条目。
外连接则不同,返回的结果不仅包含符合连接条件的行,而且包括左表(左外连接时), 右表(右连接时)或者两边连接(全外连接时)的所有数据行。
1)左外连接LEFT [OUTER] JOIN 
显示符合条件的数据行,同时显示左边数据表不符合条件的数据行,右边没有对应的条目显示NULL。

select * from Course left outer join Teacher on Course.T#=Teacher.T#

结果为:

图片 6

2)右外连接RIGHT [OUTER] JOIN 显示符合条件的数据行,同时显示右边数据表不符合条件的数据行,左边没有对应的条目显示NULL。

select * from Course right outer join Teacher on Course.T#=Teacher.T#

结果为:

图片 7

3)全外连接full [outer] join

显示符合条件的数据行,同时显示左右不符合条件的数据行,相应的左右两边显示NULL,即显示左连接、右连接和内连接的并集。

select * from Course full outer join Teacher on Course.T#=Teacher.T#

结果为:

图片 8

自连接

其实,在Sql Server中,我们还经常用到一种连接——自连接。
通过以下的例子,来了解自连接:
表树形结构表tb_TestTreeView

图片 9

解决问题: 树形层次结构显示
/* 
 这是一个地区表,里面存放了地区名及其所属上级地区,假设现在需要查询出各地区及其所属上级地区。
*/

自连接的方法1:

select [Name] as '地区名',
  (select [Name] from tb_TestTreeView as a
    where a.ID = b.Parent ) as '上级地区名'
from tb_TestTreeView as b

自连接的方法2:

select a.[Name] as '地区名',
       b.[Name] as '上级地区名'
from tb_TestTreeView as a
left join tb_TestTreeView as b
     on a.Parent = b.ID

结果为:

图片 10

自连接三级(左联接):

图片 11

select a.[Name] as '地区名',
       b.[Name] as '上级地区名',
       c.[Name] as '上上级地区名'
from tb_TestTreeView as a
left join tb_TestTreeView as b
     on a.Parent = b.ID 
left join tb_TestTreeView as c
     on b.parent=c.id

图片 12

结果为:

图片 13

自连接三级(内联接):

图片 14

select a.[Name] as '地区名',
       b.[Name] as '上级地区名',
       c.[Name] as '上上级地区名'
from tb_TestTreeView as a
inner join tb_TestTreeView as b
     on a.Parent = b.ID 
inner join tb_TestTreeView as c
     on b.parent=c.id

图片 15

结果为:

图片 16

自连接四级(左链接):

图片 17

select a.[Name] as '地区名',
       b.[Name] as '上级地区名',
       c.[Name] as '上上级地区名',
       d.[Name] as '上上上级地区名'
from tb_TestTreeView as a
left join tb_TestTreeView as b
     on a.Parent = b.ID 
left join tb_TestTreeView as c
     on b.parent=c.id
left join tb_TestTreeView as d
     on c.parent=d.id

图片 18

结果为:

图片 19

自连接四级(内链接):

图片 20

select a.[Name] as '地区名',
       b.[Name] as '上级地区名',
       c.[Name] as '上上级地区名',
       d.[Name] as '上上上级地区名'
from tb_TestTreeView as a
inner join tb_TestTreeView as b
     on a.Parent = b.ID
inner join tb_TestTreeView as c
     on b.Parent = c.ID
inner join tb_TestTreeView as d
     on c.Parent = d.ID

图片 21

结果为:

图片 22

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图