Hello,
I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
And set the endpoints started.
I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
I started configuring mirroring and my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
The server network address "TCP://server.domain.com:5023 can not be reached or does not exist. check the network address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
While in Principle server event logs I am getting Event Id 1474:
Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
In mirror server event logs I am getting:
Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
And I have SQL Server and SQL Server Browser services running in all the three Servers.
I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
When I am checking the traffic in Firewall firewall is blocking 1434/UDP port between mirror and witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
Please someone help.
I am also attaching the snapshots for the errors.
Sorry for the big post but i thought it will help pinpoint the issue
did you setup mirroring via t-sql or via the gui?
did you receive any errors whilst setting mirroring up?
I have set Up mirroring using GUI but created endpoints using TSQL.
No error occurred setting up mirroring, I got error while starting the mirroring
The server network address "TCP://server.domain.com:5023 can not be reached or does not exist. check the network address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)