☰

无法将见证添加到我的镜像设置

tag-icon • tag-icon sql-server sql-server-2008 mirroring database-mirroring
无法将见证添加到我的镜像设置

我正在尝试使用运行 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

  1. 您是否在所有实例上使用相同的域帐户？

  2. 确保为该帐户设置了端点上的权限？
    --为合作伙伴服务器实例创建登录名，
    --它们都以 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”？这不会破坏镜像吗？

相关内容