我正在尝试使用运行 SQL Server 2008 R2 的 3 台专用服务器设置见证服务器镜像。我已按照此博客文章中的说明设置了所有内容
主服务器和辅助服务器之间的镜像似乎运行良好。但是当我运行最后一条命令来添加见证服务器时
alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';
几秒钟后出现以下错误：
消息 1456，级别 16，状态 3，第 1 行 无法将 ALTER DATABASE 命令发送到远程服务器实例“TCP://witness_address:witness_port”。数据库镜像配置未更改。验证服务器是否已连接，然后重试。
此外，主体上的事件日志显示以下错误：
无法将 ALTER DATABASE 命令发送到远程服务器实例“TCP://witness_address:witness_port”。数据库镜像配置未更改。验证服务器是否已连接，然后重试。
我已经关闭了见证服务器上的防火墙，并且已成功从主服务器远程登录到见证服务器的镜像端点，以确保没有连接问题。但是，我仍然收到此错误。
我的设置如下：
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
我无法监控“审计数据库镜像登录”，因为我在分析器中的“事件列表”中找不到它。它不在“安全审计”部分，对吗？
无论如何，我无法在见证方或镜像方监控任何此类事件。我试过了，但根本没有事件发生。在主体方发生了以下一些事件：
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
我也很乐意镜像其他事件，但请让我知道在调整分析器时可以在哪个事件部分找到它们。
编辑3：我又尝试分析了证人的情况，这次几乎涉及了所有类型的事件。最后我得到了一些数据：
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
但我仍然没有看到有关添加证人为何失败的任何信息。
答案1
您可以将 SQL Profiler 附加到三个全部涉及的实例并监视这些事件：
然后再次尝试建立镜像会话。请确保选择所有列添加事件时。从空白模板开始。
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 委托人和见证人成功握手（审计登录成功）
- 在 20:04:27，DBM 将状态从“同步（无见证）”更改为“同步”
- 在 20:04:28，DBM 将状态从同步更改回无见证同步
- 20:05:42 与见证方的连接由于不活动而超时并关闭
这一系列事件表明主体和见证者之间的连接正常。添加见证者的请求失败，原因可能有很多。镜像和见证者上一定也发生了类似的事件，不清楚为什么你说只能在主体上捕获它们。
答案2
镜像是否已声明且见证端点的状态是否已启动？要检查：SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
您是否还：
1. 创建端点：
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
您是否在所有实例上使用相同的域帐户？
确保为该帐户设置了端点上的权限？
--为合作伙伴服务器实例创建登录名，
--它们都以 MYDOMAIN\dbousername 身份运行：
USE master;
GO
CREATE LOGIN [MYDOMAIN\dbousername] FROM WINDOWS;
GO--
授予端点上的连接权限以登录合作伙伴的帐户
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MYDOMAIN\dbousername];
GO
看这里更多细节。
答案3
“Witness_address”是IP地址还是名称？
如果它是一个名称，那么每个服务器是否都能解析其他每个服务器的名称？（不同的工作组 = 没有名称解析 = 必须使用 DNS 或 HOSTS 文件）
答案4
根据http://msdn.microsoft.com/en-us/library/ms190430.aspx...
要执行 SET WITNESS 语句，数据库镜像会话必须已启动（在合作伙伴之间），并且见证服务器端点的 STATE 必须设置为 STARTED。
为什么您的脚本在“setimony”之前有“alter database MyDBName set partner OFF”？这不会破坏镜像吗？