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_certCREATE 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:GOBACKUP DATABASE [dbName] TO  DISK = N'D:\Hot backup\testbackup01.bak' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP LOG [dbName] TO  DISK = N'D:\Hot backup\TestBackup01.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

-- 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 = 10GO

-- 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.