Sql-server – SQL Mirror failing on setup

database-mirroringsql server

I've got two SQL servers, I need to mirror a database from one to the other.

Should be straight forward, two servers no witness.

  • Both servers are running the same edition of MS SQL Std 2014
  • The SQL services are all running under the same domain account
  • Domain Account is Local Admin on the SQL Server OSs
  • Domain Account is SysAdmin and Public roles on SQL servers
  • DB has been backed up and restored from primary to secondary, DB is running on Secondary
  • Folder paths are identical on both servers. SQL Instance is installed on D: on both
  • Windows Firewall (for the purpose of testing) has a rule to unblock all TCP ports in both directions on both servers.
  • Both Databases are in Full recovery model, an compatibility level is 110 (SQL Server 2012)

Mirroring Endpoint has been created on both the principle and the secondary server with t-sql as follows :

CREATE ENDPOINT [Mirror]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING(ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
GO

I have tried altering the following switches

ROLE = PARTNER
ENCRYPTION = REQUIRED ALGORITHM RC4

All produce the same result.

A message on the Principle server [CRM-2016-SQL-W] that is identical to this SQL Error

The server address that is crossed out is TCP://CRM-2016-SQL-W:5022. So the principle server is complaining that it is unable to communicate with the local Mirror Endpoint (as I understand it).

Telnet from all locations (Principle SQL/secondary SQL and elsewhere on the network) all show that port 5022 is accessible on both servers.

If you delete the endpoint port 5022 rejects connections.

I have tested this with port 7022 on both ends and get the same results.

SQL server logs from Secondary show :

Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time<c/> or established connection failed because connected host has failed to respond.)'.' for 'TCP://CRM-2016-SQL-W.ncp.co.uk:5022'.

Logs from the primary at the same time simply show:

The database Mirroring endpoint has stopped listening for connections
The database Mirroring endpoint is now listening for connections
Service is listening on ['any' <ipv4>5022]
Service is listening on ['any' <ipv6>5022]

I have followed instructions covered here : https://stackoverflow.com/questions/11032937/how-to-resolve-error-1418-in-sql-server-while-mirroring to no avail.

I've left this bit of work to rather the last minute as i had no trouble doing this the last time I did it and now I'm at risk of a slightly embarrassing project meeting tomorrow! All thoughts appreciated.

Best Answer

Did you see if the endpoints were created. Use this query:-

select * from sys.tcp_endpoints; select * from sys.database_mirroring_endpoints;

I just found using SQL 2016 on Windows 2016 I wasn't creating the endpoints. I had to add them manually.

Related Topic