SQL镜像问题（郁闷！！）

悬赏园豆：5 [已关闭问题] 关闭于 2010-03-07 13:35

这两天在学着配置SQL镜像服务，配置好后遇到一个很郁闷的问题，不多说了，先上代码：

代码

---主服务器：
--第一步
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate       for database mirroring',START_DATE  = '03/03/2010'

--第二步
CREATE ENDPOINT Endpoint_Mirroring  STATE = STARTED
AS TCP (LISTENER_PORT=5022,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_A_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,ROLE =PARTNER)

--备份并拷贝到另外2台服务器
----第三步
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\HOST_A_cert.cer';

--第四步
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password'
CREATE USER HOST_B_user FOR LOGIN HOST_B_login
–--使证书与该用户关联
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]
–--授予该登录名对远程镜像端点的CONNECT 权限

CREATE LOGIN HOST_C_login WITH PASSWORD = 'password'
CREATE USER HOST_C_user FOR LOGIN HOST_C_login
CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login]

CREATE LOGIN HOST_A_login WITH PASSWORD = 'password'
CREATE USER HOST_A_user FOR LOGIN HOST_A_login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]

--第五步
--新建TestMirror数据库，并还原至镜像服务器(WITH RECOVERY)

--第六步
--镜像服务器
ALTER DATABASE TestMirror SET PARTNER =N'TCP://主服务器IP:5022'
---主服务器（必须在镜像服务器上完成上述语句后才执行）
ALTER DATABASE TestMirror SET PARTNER = N'TCP://镜像服务器IP:5022';
ALTER DATABASE TestMirror SET WITNESS = N'TCP://见证服务器IP:5022'

--第七步
--设置安全级别为高可用性模式
ALTER DATABASE TestMirror SET SAFETY FULL

代码

---镜像服务器：
--第一步
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate       for database mirroring',START_DATE  = '03/03/2010'

--第二步
CREATE ENDPOINT Endpoint_Mirroring  STATE = STARTED
AS TCP (LISTENER_PORT=5022,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_B_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,ROLE =PARTNER)

--备份并拷贝到另外2台服务器
----第三步
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\HOST_B_cert.cer';

--第四步
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password'
CREATE USER HOST_A_user FOR LOGIN HOST_A_login
–--使证书与该用户关联
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]
–--授予该登录名对远程镜像端点的CONNECT 权限

CREATE LOGIN HOST_C_login WITH PASSWORD = 'password'
CREATE USER HOST_C_user FOR LOGIN HOST_C_login
CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login]

CREATE LOGIN HOST_B_login WITH PASSWORD = 'password'
CREATE USER HOST_B_user FOR LOGIN HOST_B_login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]

--第五步
--还原TestMirror数据库(WITH RECOVERY)

--第六步
ALTER DATABASE TestMirror SET PARTNER =N'TCP://主服务器IP:5022'

代码

---见证服务器：
--第一步
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate       for database mirroring',START_DATE  = '03/03/2010'

--第二步
CREATE ENDPOINT Endpoint_Mirroring  STATE = STARTED
AS TCP (LISTENER_PORT=5022,LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE HOST_C_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,ROLE =WITNESS)


----第三步
--备份并拷贝到另外2台服务器
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\HOST_C_cert.cer';

--第四步
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password'
CREATE USER HOST_A_user FOR LOGIN HOST_A_login
–--使证书与该用户关联
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]
–--授予该登录名对远程镜像端点的CONNECT 权限

CREATE LOGIN HOST_B_login WITH PASSWORD = 'password'
CREATE USER HOST_B_user FOR LOGIN HOST_B_login
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer' 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]


CREATE LOGIN HOST_C_login WITH PASSWORD = 'password'
CREATE USER HOST_C_user FOR LOGIN HOST_C_login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login]

完成上述步骤后刷新后显示：(为了方便描述我把主服务器设为A，镜像服务器设为B，见证服务器设为C)

A：

B：

我想到这里应该说大功告成了。后来我就想啊测试下自动故障转移哈~~我就停掉了A的SQL Service了，刷新B一看，嗯？？？怎么没有自动转换成可用状态呢？我再刷、再刷、再刷还是一样。B显示的是这洋的：我就google了，找到了一个视图（sys.database_mirroring）可以查看Mirror的状态，