2

I am trying to set up mirroring between two servers behind the same firewall on the same subnet.

I backed up mainserver and restored to mirrorserver.

I'm using the wizard on "mainserver" and connect to the "mirrorserver" using the sa login as both servers have the same sa login password.

The wizard creates endpoints on both servers correctly. When I start the mirroring it asks about FQDN but there is no domain so I click "Yes". It works for a while then I get the error alert below.

What is strange is that the error suggests it can't connect to itself.

Since posting this I found elsewhere suggestions that the problem is the database wasn't restored with NO RECOVERY on the mirrorserver. I tried this and now the wizard says 

Cannot connect to mirrorserver

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

previously

Alter failed for Database 'mydatabase'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://mainserver:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
Improve this question

3 Answers 3

Reset to default
2

This is typical Microsoft. The error message is about tcp ports. The actual error is that the mirror database wasn't restored with NO RECOVERY

BTW this is the best step by step guide I've found to setting up mirroring in a workgroup situation

Database Mirroring between a Server in Domain and Server outside domain (workgroup)

Improve this answer
2

When initializing the mirror instance with the database you need to restore a full backup AND a log backup, then set up the mirroring.

Steps:

1-Backup database
2-Restore backup to mirror with norecovery
3-Backup database log
4-Restore log backup to mirror with norecovery
5-run "alter database <DB> set partner = 'TCP://<PRIMARY>:5022' on mirror
6-run "alter database <DB> set partner = 'TCP://<MIRROR>:5022' on primary

Also, if you're running Windows 2008 the firewall is on by default and you might need to set up a firewall rule for the mirroring port between the two hosts.

netsh advfirewall firewall add rule name=SQL-SSDS-MIRRORING-<INSTANCE> dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name=SQL-SSDS-MIRRORING-<INSTANCE> dir=out action=allow protocol=TCP localport=5022
Improve this answer
0

Make sure that port 5022 is open on the server. It seems to be a network issue.

Improve this answer
1
  • The port is open: C:\Users\Administrator>netstat -ano | findstr 5022 TCP 0.0.0.0:5022 0.0.0.0:0 LISTENING 244 TCP [::]:5022 [::]:0 LISTENING 244
    – derekcohen
    Commented Sep 28, 2011 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.