在SQL Server镜像方案w /错误设置见证 [英] Trouble setting up witness in SQL Server mirroring scheme w/ error

问题描述

我有三个Windows服务器（data1，data2和datawitness）不属于任何域，不使用AD。我尝试根据 http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx。我有成功，直到最后一组指令，我告诉data1使用datawitness作为见证服务器。该步骤失败并显示以下消息：

I've got a trio of Windows servers (data1, data2 and datawitness) that aren't part of any domain and don't use AD. I'm trying to set up mirroring based on the instructions at http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx. I've had success right up until the final set of instructions where I tell data1 to use datawitness as the witness server. That step fails with the following message:

alter database MyDatabase set witness ='TCP：//datawitness.somedomain.com：7024'

alter database MyDatabase set witness = 'TCP://datawitness.somedomain.com:7024'

无法将ALTER DATABASE命令发送到远程服务器实例TCP：//datawitness.somedomain.com：7024。数据库镜像配置未更改。验证服务器是否已连接，然后重试。

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://datawitness.somedomain.com:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

我已使用telnet和两个服务器测试了端口7024和1433实际上可以彼此连接。我还能够在主服务器上从SQL Server Manager向见证服务器添加连接。我已使用两个服务器上的配置管理器启用命名管道，并验证IP流量启用，并默认使用端口1433.

I've tested both port 7024 as well as 1433 using telnet and both servers can indeed connect with each other. I'm also able to add a connection to the witness server from SQL Server Manager on the primary server. I've used the Configuration Manager on both servers to enabled Named Pipes and verify that IP traffic is enabled and using port 1433 by default.

还有什么？我需要任何额外的端口打开这个工作吗？ （防火墙规则非常严格，但我知道前面提到的端口的流量是明确允许的）

What else could it be? Do I need any additional ports open for this to work? (The firewall rules are very restrictive, but I know traffic on the previously mentioned ports is explicitly allowed)

值得一提的注意事项：

• 每个服务器位于不同的网段

• Each server is in a different network segment

AD并且不是域的一部分

The servers don't use AD and aren't part of a domain

没有为这些服务器配置DNS服务器，因此我使用HOSTS文件将域名映射到IP地址（使用telnet，ping等验证）。

There is no DNS server configured for these servers, so I'm using the HOSTS file to map domain names to IP addresses (verified using telnet, ping, etc).

防火墙规则非常严格，我没有直接访问来调整它们，如果需要可以调用更改

The firewall rules are very restrictive and I don't have direct access to tweak them, though I can call in a change if needed

Data1和Data2使用SQL Server 2008，Datawitness使用SQL Express 2005.所有这些都使用默认实例没有一个是命名实例）

Data1 and Data2 are using SQL Server 2008, Datawitness is using SQL Express 2005. All of them use the default instance (i.e. none of them are named instances)

推荐答案

文章和论坛帖子和重新安装和重新配置和重新启动和分析等等，我终于找到了关键的谜题 - 事件日志中的条目在见证服务器报告此错误：

After combing through blogs and KB articles and forum posts and reinstalling and reconfiguring and rebooting and profiling, etc, etc, etc, I finally found the key to the puzzle - an entry in the event log on the witness server reported this error:

Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://ABC-WEB01:7024'.

我使用了一个hosts文件，以datax的形式映射所有三个服务器的模拟域名。 mydomain.com。但是，现在很明显，证人正试图使用​​主服务器的名称，我没有一个主机条目。只需为ABC-WEB01添加另一个指向主Web服务器的条目即可。没有错误和镜像最终会完成。

I had used a hosts file to map mock domain names for all three servers in the form of datax.mydomain.com. However, it is now apparent that the witness was trying to comunicate back using the name of the primary server, which I did not have a hosts entry for. Simply adding another entry for ABC-WEB01 pointing to the primary web server did the trick. No errors and the mirroring is finally complete.

希望这可以节省别人十亿小时。

Hope this saves someone else a billion hours.

这篇关于在SQL Server镜像方案w /错误设置见证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！