[TroubleShooting] The server network address can not be reached or does not exist

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介:

Backtround:

I'm trying to set up mirroring between two sql 2008 R2 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 xxxdb SET PARTNER = TCP://myserverAddress:50221') 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.

In fact, this is error is not clear for us.Checking the log, I'm seeing the following errors. "Database mirroring connection error 5 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.' for 'TCP://myserverAddress:50221'." in event viewer(eventvwr).

Solution:

You can manual copying the certificates around(Principal, Mirror and Witness). and drop login, user. and then create login, user with certificate responsed. Last, Grant connect endpoint.

CREATE LOGIN HOST_pro_login WITH PASSWORD ='Password01!';
CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;
-- drop CERTIFICATE HOST_pro_cert
CREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];

 

Other Suggestions:

The solution to the above problem is very simple and as follows.

Fix/WorkAround/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

-- Back up 1:
GO
BACKUP DATABASE [dbName] TO  DISK = N'D:\Hot backup\testbackup01.bak' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG [dbName] TO  DISK = N'D:\Hot backup\TestBackup01.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- Restore 1:
RESTORE DATABASE dbName
   FROM DISK = 'D:\Hot backup\dbName.bak' WITH  FILE = 1,  
   MOVE 'dbName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\dbName.mdf', 
   MOVE 'dbName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\dbName_log.ldf',
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO
-- Restore 2:
RESTORE DATABASE dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;
RESTORE LOG dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH NORECOVERY


Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 50221″.(Please add feature lelnet client|Server in server 2k8, if it not exsit.)

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using thestate or state_desc column the of the sys.database_mirroring_endpointscatalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

Suggestion 8: Check SQL log.

If any of above solutions does not fix your problem, do leave comment here. Based on the comment, I will update this article with additional suggestions.

Please note that some of the above suggestions can be security threat to your system. Please use them responsibly and review your system with security expert in your company.

版权声明:本文博客原创文章,博客,未经同意,不得转载。








本文转自mfrbuaa博客园博客,原文链接:http://www.cnblogs.com/mfrbuaa/p/4749030.html,如需转载请自行联系原作者


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
网络安全
完美解决 WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!
完美解决 WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!
907 0
|
网络安全
WARN: Establishing SSL connection without server‘s identity verification is not recommended
WARN: Establishing SSL connection without server‘s identity verification is not recommended
92 0
|
区块链
Error: No network specified. Cannot determine current network异常
Error: No network specified. Cannot determine current network异常
140 0
|
数据库
Symantec Backup Exec Agent 推送错误Error connecting to the remote computer. Ensure that the computer is available, has WMI enabled and is not blocked by a
如果在Symantec Backup Server上推送Symantec Backup Exec Agent到数据库服务器遇到“"Error connecting to the remote computer. Ensure that the computer is available, has WMI enabled and is not blocked by a firewall"这个错误, 如下截图所示     那么完全可以参考下面官方提供的三个解决方案解决问题,几次碰到这个问题,每次都要搜索一下,特此记录一下,方便以后查找。
1321 0
|
SQL Windows
Unable to determine if the owner (Domain\UserName) of job JOB_NAME has server access
早上巡检的的时候,发现一数据库的作业报如下错误(作业名等敏感信息已经替换),该作业的OWNER为一个域账号: JOB RUN: 'JOB_NAME' was run on 2016-6-1 at 7:00:00 DURATION: 0 hours, 0 minutes, 1 seconds STATUS: Failed MESSAGES: The job failed.
1413 0