不能添加见证我的镜像设置

我正在尝试使用运行SQL Server 2008 R2的3台专用服务器与证人build立镜像。我已经设置了这个博客文章中描述的一切

镜像似乎在主服务器和辅助服务器之间正常工作。但是当我运行最后的命令来添加证人

alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';

几秒钟后出现以下错误：

消息1456，级别16，状态3，行1无法将ALTER DATABASE命令发送到远程服务器实例'TCP：// witness_address：witness_port'。数据库镜像configuration没有改变。确认服务器已连接，然后重试。
SQL镜像失败的设置
Sql Server 2008在EC2中的高可用性
SQL Server镜像 – 应该见证服务器专用于镜像？
服务或软件自动同步一台服务器，所以他们是相同的？
在生产服务器上安装两个版本的MSSQL？

此外，主体上的事件日志显示以下错误：

ALTER DATABASE命令无法发送到远程服务器实例“TCP：// witness_address：witness_port”。数据库镜像configuration没有改变。确认服务器已连接，然后重试。

我已经closures了目击者的防火墙，并且我已经成功地从主体telnet到目击者服务器的镜像端点，以确保没有连接问题。但是，我仍然得到这个错误。

我的设置如下：

 Principal: SQL Server 2008 R2 Standard 10.50.1617 Mirror: SQL Server 2008 R2 Standard 10.50.1617 Witness: SQL Server 2008 R2 Express 10.50.2500

每个服务器都在不同的工作组中。

请帮助我SQL服务器大师，你是我唯一的希望！

编辑：这是我用来设置镜像的T-SQL命令的完整日志。一旦第一次尝试失败，上面描述的问题，我删除了一切，从头开始。同样的问题，但重新发生。

 -- PRINCIPAL create master key encryption by password = 'mypassword'; GO create certificate "server1.ourdomain.com_cert" with subject = 'server1.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01'; GO Create endpoint endpoint_mirroring state = started as tcp(listener_port = 5022, listener_ip = all) for database_mirroring (authentication = certificate "server1.ourdomain.com_cert", encryption = disabled, role = all); GO Backup certificate "server1.ourdomain.com_cert" to file = 'f:\cert\server1.ourdomain.com_cert.cer'; GO -- MIRROR create master key encryption by password = 'mypassword'; GO create certificate "server2.ourdomain.com_cert" with subject = 'server2.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01'; GO Create endpoint endpoint_mirroring state = started as tcp(listener_port = 5022, listener_ip = all) for database_mirroring (authentication = certificate "server2.ourdomain.com_cert", encryption = disabled, role = all); GO Backup certificate "server2.ourdomain.com_cert" to file = 'f:\cert\server2.ourdomain.com_cert.cer'; GO -- WITNESS create master key encryption by password = 'mypassword'; GO create certificate "witness.ourdomain.com_cert" with subject = 'witness.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01'; GO Create endpoint endpoint_mirroring state = started as tcp(listener_port = 5022, listener_ip = all) for database_mirroring (authentication = certificate "witness.ourdomain.com_cert", encryption = disabled,role = witness); GO Backup certificate "witness.ourdomain.com_cert" to file = 'd:\cert\witness.ourdomain.com_cert.cer'; GO -- PRINCIPAL again create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login"; GO select * from sys.certificates Create certificate "server2.ourdomain.com_cert" Authorization "server2.ourdomain.com_user" From file = 'f:\cert\server2.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login]; GO ------ create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login"; GO Create certificate "witness.ourdomain.com_cert" Authorization "witness.ourdomain.com_user" From file = 'f:\cert\witness.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::endpoint_mirroring to [witness.ourdomain.com_login]; GO -- MIRROR again create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login"; GO Create certificate "server1.ourdomain.com_cert" Authorization "server1.ourdomain.com_user" From file = 'f:\cert\server1.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login]; GO ------- create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login"; GO Create certificate "witness.ourdomain.com_cert" Authorization "witness.ourdomain.com_user" From file = 'f:\cert\witness.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::Endpoint_mirroring to [witness.ourdomain.com_login]; GO -- WITNESS again create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login"; GO Create certificate "server1.ourdomain.com_cert" Authorization "server1.ourdomain.com_user" From file = 'd:\cert\server1.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login]; GO ------- create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword'; GO create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login"; GO Create certificate "server2.ourdomain.com_cert" Authorization "server2.ourdomain.com_user" From file = 'd:\cert\server2.ourdomain.com_cert.cer'; GO Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login]; GO -- MIRROR again alter database MyDBName set partner OFF alter database MyDBName set partner = 'TCP://server1.ourdomain.com:5022'; GO -- PRINCIPAL again alter database MyDBName set partner OFF alter database MyDBName set partner = 'TCP://server2.ourdomain.com:5022'; GO alter database MyDBName set witness OFF alter database MyDBName set witness = 'TCP://witness.ourdomain.com:5022'; GO

编辑2：根据Remus的要求，我试图添加证人时，已经将探查器附加到了我的三个服务器实例。我寻找：

 Database Mirroring State Change Broker:Connection Event

我无法监视“审计数据库镜像login”，因为我无法在分析器的“事件列表”中find它。这不在“安全审计”部分，是吗？

无论如何，我无法监视证人或镜子上的任何此类事件。我试过了，但根本没有事件。校长中有一些事件：

  Database Mirroring Connection 32 2011-12-09 20:04:07.983 1 39796 2 - Connected c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://<MyWitnessIP>:5022 principal.mytopleveldomain.com,3809 Audit Database Mirroring Login 30 2011-12-09 20:04:08.133 1 39797 1 - Login Success 1 0X01 tcp://<MyWitnessIP>:5022 principal.mytopleveldomain.com,3809 1 witness.mytopleveldomain.com_login CERTIFICATE Microsoft Unified Security Protocol Provider Initiator Database Mirroring State Change DBM: Synchronized Principal without Witness -> DBM: Synchronizing Principal 27 2011-12-09 20:04:27.980 9 39798 2 1 0X01 principal.mytopleveldomain.com,3809 Zeiterfassung-Staging 0 13 Database Mirroring State Change DBM: Synchronizing Principal -> DBM: Synchronized Principal without Witness 27 2011-12-09 20:04:28.237 9 39799 13 1 0X01 principal.mytopleveldomain.com,3809 Zeiterfassung-Staging 0 2 Database Mirroring Connection An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'. 30 2011-12-09 20:05:42.530 1 39803 4 - Closing c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://<MyWitnessIP>:5022 principal.mytopleveldomain.com,3809 Database Mirroring Connection 30 2011-12-09 20:05:42.533 1 39804 5 - Closed c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://<MyWitnessIP>:5022 principal.mytopleveldomain.com,3809 Database Mirroring Connection 32 2011-12-09 20:04:07.983 1 39796 2 - Connected c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://85.214.251.151:5022 srv1.logmytime.de,3809 Audit Database Mirroring Login 30 2011-12-09 20:04:08.133 1 39797 1 - Login Success 1 0X01 tcp://85.214.251.151:5022 srv1.logmytime.de,3809 1 wtn.logmytime.de_login CERTIFICATE Microsoft Unified Security Protocol Provider Initiator Database Mirroring State Change DBM: Synchronized Principal without Witness -> DBM: Synchronizing Principal 27 2011-12-09 20:04:27.980 9 39798 2 1 0X01 srv1.logmytime.de,3809 Zeiterfassung-Staging 0 13 Database Mirroring State Change DBM: Synchronizing Principal -> DBM: Synchronized Principal without Witness 27 2011-12-09 20:04:28.237 9 39799 13 1 0X01 srv1.logmytime.de,3809 Zeiterfassung-Staging 0 2 Database Mirroring Connection An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'. 30 2011-12-09 20:05:42.530 1 39803 4 - Closing c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://85.214.251.151:5022 srv1.logmytime.de,3809 Database Mirroring Connection 30 2011-12-09 20:05:42.533 1 39804 5 - Closed c6b6054d-56c0-4589-922a-29a40fdd9d96 1 0X01 tcp://85.214.251.151:5022 srv1.logmytime.de,3809

我也很乐意反映其他事件，但请让我知道哪些事件部分，我可以find他们在调整分析器。

编辑3 ：我再次尝试分析证人，这次几乎每一个事件types都有。我终于得到了一些数据：

 Trace Start 2011-12-10 01:24:08.180 ErrorLog 2011-12-10 01:24:40.40 Logon Database Mirroring Login succeeded for user 'principal.ourdomain.com_login'. Authentication mode: CERTIFICATE. [CLIENT: <principalip>] 29 2011-12-10 01:24:40.400 1 28046 1428 1 0X01 witness.ourdomain.com,3809 master 0 sa EventLog Database Mirroring Login succeeded for user 'principal.ourdomain.com_login'. Authentication mode: CERTIFICATE. [CLIENT: <principalip>] 29 2011-12-10 01:24:40.400 1 28046 1429 1 0X01 witness.ourdomain.com,3809 master 0 sa

接下来，我在镜子上也做了同样的事情，但是我只得到了这两个相当不重要的事件：

 ErrorLog 2011-12-10 01:38:02.14 spid29s Database mirroring is inactive for database 'DatabaseName'. This is an informational message only. No user action is required. sa 29 2011-12-10 01:38:02.143 EventLog Database mirroring is inactive for database 'DatabaseName'. This is an informational message only. No user action is required. sa 29 2011-12-10 01:38:02.143 0XA20500000A0000000F000000570049004E003600360036004800520054004B003700540032004800000000000000 ErrorLog 2011-12-10 01:38:22.40 spid29s Database mirroring is active with database 'DatabaseName' as the mirror copy. This is an informational message only. No user action is required. sa 29 2011-12-10 01:38:22.407 EventLog Database mirroring is active with database 'DatabaseName' as the mirror copy. This is an informational message only. No user action is required. sa 29 2011-12-10 01:38:22.407 0XA10500000A0000000F000000570049004E003600360036004800520054004B003700540032004800000000000000

我仍然没有看到为什么添加证人失败的任何信息。

您可以将SQL Profiler附加到涉及的所有三个实例，并监视这些事件：

审计数据库镜像login
数据库镜像状态更改
经纪人：连接事件

然后再次尝试build立镜像会话。确保在添加事件时select所有列。从空白模板开始。

 2011-12-09 20:04:07.983 Database Mirroring Connection Connected 2011-12-09 20:04:08.133 Audit Database Mirroring Login Login Success 2011-12-09 20:04:27.980 Database Mirroring State Change DBM: Synchronized Principal without Witness -> DBM: Synchronizing Principal 2011-12-09 20:04:28.237 Database Mirroring State Change DBM: Synchronizing Principal -> DBM: Synchronized Principal without Witness 2011-12-09 20:05:42.530 Database Mirroring Connection An error occurred...

以下是对发生的情况的解释：

在20:04:07校长与证人联系
在20:04:08校长和证人完成了成功的交涉（审计login成功）
在20:04:27 DBM将状态从同步W / O见证更改为同步
在20:04:28 DBM将状态从syncronized更改为同步的w / o见证
在20:05:42与证人的联系由于不活动而closures

这一系列的事件表明，委托人和证人之间的连通性是function性的。添加证人的请求失败，可能有多种原因。同样的事件也必须发生在镜子和证人身上，不清楚为什么你说只能在委托人身上抓到他们。

是否已经说明了镜像和证人STARTED的终点的状态？要检查：SELECT role_desc，state_desc FROM sys.database_mirroring_endpoints
你还有：
1.创build了一个端点：
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP（LISTENER_PORT = 7022）
FOR DATABASE_MIRRORING（ROLE = WITNESS）
走

你是否在所有实例上使用相同的域帐户？
确保为该帐户设置了端点权限？
– 创build合作伙伴服务器实例的login名，
– 它们都以MYDOMAIN \ dbousername身份运行：
使用大师;
走
CREATE LOGIN [MYDOMAIN \ dbousername] FROM WINDOWS;
走
– 将端点上的权限授予合作伙伴的login帐户
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO [MYDOMAIN \ dbousername];
走

在这里看到更多的细节。

“Witness_address”是一个IP地址还是一个名字？

如果是一个名字，是否每台服务器都能parsing其他服务器的名称？（不同的工作组=没有名称parsing= DNS或HOSTS文件必须使用）

我碰到了UNABLEconfiguration见证了一段时间，直到我意识到我在镜像端点上使用的证书与Principal's＆Witness的端点不同。

一旦我使用同一个证书（从委托人复制到第二和第三台服务器）在主体，镜像和证人镜像端点，我能够解决

根据http://msdn.microsoft.com/en-us/library/ms190430.aspx …

要执行SET WITNESS语句，数据库镜像会话必须已经启动（在伙伴之间），并且见证端点的状态必须设置为STARTED。

为什么您的脚本在“set witness”之前有“更改数据库MyDBName设置伙伴OFF”？那不是推倒了镜子吗？