Sql-server – Setting up a mirror: can’t find remote server

mirroringsql serversql-server-2012

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.

Best Answer

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.

Related Question