Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

migrated from stackoverflow.com Sep 28 '11 at 10:28

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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
share|improve this answer

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)

share|improve this answer

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

share|improve this answer
    
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 Sep 28 '11 at 2:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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