1

I have troubles setting up a mirror, more precisely: i cant connect to the mirror :(

Here's my troubleshooting log :)

  • 2 virtual Windows 2k12 Servers running in Microsoft Hyper-V, on a Windows 10 machine with SQL Server 2k12,

  • Neither are part of a domain and the firewalls are off

  • Machine A has a SQL database,

  • Machine B has a full backup of Machine A's database, restored with NO RECOVERY,

  • Both machines/SQL servers can be reached over the network; pinging works, tested database connectivity using SMSS on a third machine.

  • I followed the steps in this post when using the wizard didn't work out SQL Server Mirroring can't connect to mirror database. The accounts have sysadmin rights and enpoints are associated with the certifactes,

  • I'm sure the ports are opened, as shown by netstat -an and further evidenced by using telnet to connect to the host/port combination,

  • Endpoints are in started state as shown in the mirroring endpoints table, also recycling the endpoints (i.e. stopping and restarting them) does not seem to have an effect on the issue

  • GRANTing CONNECT TO PUBLIC on the Endpoint isn't working either,

Still, running ALTER DATABASE [xxx] SET PARTNER = 'TCP:\\....:7023' results in the well known: 

Msg 1418, Level 16, State 1, Line 1
The server network address "tcp://m.....:7023" 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.

Any other insights that can apply here?

I'll post my own answer to this as well to make it obvious for other readers.

What I did wrong and this should have been very obvious, but apparently I missed this. The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.

Improve this question
3
  • Can you connect to the mirror with SSMS from the primary? And vice-versa?
    – Hannah Vernon ♦
    Commented Oct 17, 2016 at 13:13
  • Yep, using both Windows authentication (Administrator account) and SQL authentication
    – Apeiron
    Commented Oct 17, 2016 at 13:17
  • Test connectivity between servers using telnet server port
    – Anton Krouglov
    Commented Oct 18, 2016 at 13:49

2 Answers 2

Reset to default
1

I'll post my own answer to this.

What I did wrong and this should have been very obvious, but apparently I missed this. The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:\....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.

Improve this answer
0

Question does not cover, whether certificate-based authentication is used. Try this: https://msdn.microsoft.com/en-us/library/ms191140.aspx

Improve this answer
2
  • Certificates are both created and associated with the endpoints
    – Apeiron
    Commented Oct 17, 2016 at 13:28
  • @Apeiron Though it is not an answer, I would recommend to use some tool like Wireshark to check packets, that are sent and received by your hosts while trying to establish mirror connection. This is to be sure about network connectivity between these exact two servers.
    – Marvin
    Commented Oct 18, 2016 at 13:08

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.