2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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