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.
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.