SQL Server 2008 中的镜像
我正在尝试在内部网络中不同服务器上的两个 sql 2008 数据库之间设置镜像，作为对不同位置的两个实时服务器执行相同操作之前的测试运行。
当我实际尝试在目标数据库上切换镜像时（使用 ALTER DATABASE testdb SET PARTNER = N'TCP://myNetworkAddress:5022') 我收到一条错误消息，告诉我服务器网络地址无法访问或不存在。一些研究表明，这是一条相当无用的消息，由于多种可能的原因而弹出，其中一些原因与服务器现有或其他情况没有直接关系。
到目前为止，我已经检查并尝试了以下方法来解决此问题：
在目标服务器上，我已经在 SQL 配置管理器中验证了“SQLEXPRESS 协议”（由于某种原因，我的本地安装被标记为 SQLEXPRESS，甚至尽管查询 SERVERPROPERTY('Edition') 显示它是 64 位 Enterprise），并且 SQL Native Client 10 的客户端协议都启用了 TCP/IP
我正在使用名为 CurrPorts 的实用程序来验证是否存在与镜像设置 (5022) 指定的相同编号的 TCP/IP 端口已打开并在我的计算机上侦听。 Netstat 验证两台计算机是否正在侦听此端口。
我已经运行 SELECT type_desc, port FROM sys.tcp_endpoints;和 SELECT state_desc, role FROM sys.database_mirroring_endpoints 以确保一切都按应有的方式设置。唯一让我困惑的是“角色”返回 1 .. 不完全确定这意味着什么。
我尝试正确准备数据库。我从主数据库中备份了数据库和日志文件，并使用 NORESTORE 将它们恢复到目标数据库上。我尝试过打开两者的镜像，同时将它们保留在 NORESTORE 状态并运行空的 RESTORE ...两者似乎都没有多大区别。正如测试一样，我还尝试镜像我创建的不活动的、几乎为空的数据库，但这也不起作用。
我已经验证这两个服务器都没有位于防火墙后面（它们都在同一网络上，尽管位于不同的计算机上）
我不知道下一步该转向哪里。我看过这两个故障排除帮助页面：
http://msdn.microsoft.com /en-us/library/ms189127.aspx
http://msdn .microsoft.com/en-us/library/aa337361.aspx
据我所知，我已经完成了所有要点，但无济于事。
我不确定的另一件事是向导中的服务帐户框。对于这两个数据库，我已经输入了我们的高级访问帐户名，该帐户名应该对数据库具有完全的管理权限 - 我认为这是正确的做法。
我不知道接下来该去哪里尝试解决这个问题。非常感谢建议。
干杯， 马特
I'm trying to set up mirroring between two sql 2008 databases on different servers in my internal network, as a test run before doing the same thing with two live servers in different locations.
When I actually try and switch the mirroring on the target DB (with
ALTER DATABASE testdb SET PARTNER = N'TCP://myNetworkAddress:5022') I'm getting an error telling me that the server network address can not be reached or does not exist. A little research suggests this is a fairly unhelpful message that pops up due to a number of possible causes, some of which are not directly related to the server existing or otherwise.
So far I've checked and tried the following to solve this problem:
On the target server, I've verified that in SQL Configuration Manager that "Protocols for SQLEXPRESS" (my local installation is labelled SQLEXPRESS for some reason, even though querying SERVERPROPERTY('Edition') reveals that it's 64-bit Enterprise), and Client Protocols for SQL Native Client 10 all have TCP/IP enabled
I'm using a utility program called CurrPorts to verify that there is a TCP/IP port with the same number specified by the mirroring setup (5022) is open and listening on my machine. Netstat verifies that both machines are listening on this port.
I've run SELECT type_desc, port FROM sys.tcp_endpoints; and
SELECT state_desc, role FROM sys.database_mirroring_endpoints to ensure that everything is set up as it should be. The only thing that confused me was the "role" returns 1 .. not entirely sure what that means.I've tried to prepare the DB correctly. I've taken backups of the database and the log file from the master DB and restored them on the target database with NORESTORE. I've tried turning mirroring on both while leaving them in the NORESTORE state and running an empty RESTORE ... neither seems to make much difference. Just as a test I also tried to mirror an inactive, nearly empty database that I created but that didn't work either.
I've verified that neither server is behind a firewall (they're both on the same network, although on different machines)
I've no idea where to turn next. I've seen these two troubleshooting help pages:
http://msdn.microsoft.com/en-us/library/ms189127.aspx
http://msdn.microsoft.com/en-us/library/aa337361.aspx
And as far as I can tell I've run through all the points to no avail.
One other thing I'm unsure of is the service accounts box in the wizard. For both databases I've been putting in our high-level access account name which should have full admin permissions on the database - I assumed this was the right thing to do.
I'm not sure where to turn next to try and troubleshoot this problem. Suggestions gratefully received.
Cheers,
Matt
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为 SQL Express 只能充当具有此 SQL 功能的见证服务器，不过您可能会在 ServerFault 上获得更好的效果。
麦克风。
I think that SQL Express can only act as a witness server with this SQL feature, you might get better mileage on ServerFault though.
Mike.
您的网络设置可能没问题。我们在 MS SQL 中收到了相当无信息的错误消息 - 问题可能是授权问题，服务器仍然会说“无法访问网络地址”。
顺便问一下，认证是如何进行的？ MSSQL 服务（在 server1 上）本身必须作为有效的数据库用户（在 server2 上，反之亦然）运行，才能使镜像工作。
Your network settings might be OK. We got quite non-informative error messages in MS SQL - the problem might be an authorization issue and the server still will be saying "network address can not be reached".
By the way, how the authentication is performed? A MSSQL service (on server1) itself must be runned as a valid db user (on server2, and vice versa) in order to make the mirroring work.