无见证的 SQL Server 数据库镜像
我在两台不同的 SQL Server 计算机上为我的数据库设置了 SQL Server 2005 数据库镜像。这里没有使用见证服务器。我还在数据库的主服务器和镜像服务器上分别看到状态“主体/同步”和“镜像、同步/恢复”。
现在我想测试故障转移到镜像服务器是否正常工作。所以我编写了一个小型控制台应用程序，如下所示。
try
{
SqlConnection cn = new SqlConnection("data source=PRIMARYSQL1;Failover Partner=MIRRORSQL1;initial catalog=TESTDB;Integrated Security=SSPI;");
SqlCommand cm = new SqlCommand("insert into department(name) values('Dept10')", cn);
cn.Open();
cm.ExecuteNonQuery();
}
然后，我关闭了主服务器上的 hSQL Server 和 SQL Agent 服务，我希望我的控制台应用程序仍然可以正常工作，但出现以下错误。
“建立连接时发生网络相关或特定于实例的错误 SQL Server 的操作。找不到服务器或无法访问服务器。验证一下 实例名称正确并且 SQL Server 配置为允许远程 连接。 （提供程序：命名管道提供程序，错误：40 - 无法打开连接 SQL Server 的操作）”
出现故障，我就无法将镜像数据库设为主数据库，因为它不允许我从镜像服务器执行此操作。
我确信我做错了什么。任何人都可以吗？帮我？
I have setup Database mirroring for SQL Server 2005 for my database on two different SQL Server machines. There is no witness server used here. I also see the status "Principal/Synchronized" and "Mirror,Synchronized/Restoring" respectively on Primary and Mirror servers for my database.
Now I wanted to test whether the failover to mirror server works fine or not. So I wrote a small console app like below.
try
{
SqlConnection cn = new SqlConnection("data source=PRIMARYSQL1;Failover Partner=MIRRORSQL1;initial catalog=TESTDB;Integrated Security=SSPI;");
SqlCommand cm = new SqlCommand("insert into department(name) values('Dept10')", cn);
cn.Open();
cm.ExecuteNonQuery();
}
I then turned off thehSQL Server and SQL Agent services on the primary server and I expected my console app to still work fine but I get below error.
"A network-related or instance-specific error occurred while establishing a conne
ction to SQL Server. The server was not found or was not accessible. Verify that
the instance name is correct and that SQL Server is configured to allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a conne
ction to SQL Server)"
Also, once the primary server goes down I have no way to make the mirror database primary as it doesn't allow me to do that from the mirror server.
I am sure I am doing something wrong. Can anyone help me?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您期望发生自动故障转移，但没有见证人就无法进行自动故障转移。鉴于从任何廉价/老化硬件运行的任何 SQL Express 实例都可以充当见证人，我强烈建议您添加见证人并进行自动故障转移，而不是必须进行手动故障转移，以防发生主要丢失时潜在的数据丢失。
You are expecting automatic failover to occur, but automatic failover cannot occur w/o a witness. Given that any SQL Express instance running from any cheap/aging hardware can act as a witness, I would strongly suggest that you add a witness and have automatic failover, rather than have to do manual failover with potential data loss in case of a primary loss.
我认为您正在寻找类似的东西（在镜像上运行）：
ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
去
I think you're looking for something like this (run this on the mirror):
ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO