SQL Mirroring - Not Working - Spot the Flaw...

techmiss
techmiss used Ask the Experts™
on
Hi all,

I have configured several SQL boxes and  SQL mirroring scenarios but this one has me going round in circles.

The result I get is that the principal cannot talk to the mirror and gives the error TCP://MYSERVER.DOMAIN.LOCAL:5022 cannot be contacted when trying to start mirroring from SQL management studio.

Both servers are Win Server 2008 R2 running MS SQL 2008 R2 on VM Ware ESXi 4.1, as separate entities the servers are running without issue - both the OS and SQL installations are patched to the same level, the servers are on the same domain but different subnets and different AD sites.

On both servers the SQL server instance is running as the same user with domain admin privs.

To configure mirroring I have:

1) backed up and restored the relevant database to the mirror with no recovery
2) Configured security on the prinicpal with NO witness
3) Left the ports for both servers on 5022 (this is what SQL management studio had as default)
4) use the same account that SQL is currently running as for the mirroring service accounts.

Windows Firewall is off on both machines, there are not other firewalls inbetween the two devices, DNS resolves both FQDN and hostname.

When I try to start mirroring the principal cannot contact the mirror.. I'm sure I have missed something...

Help!

Cheers

K
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I would validate a few things:
1) Run the following query on each server to validate which port it is really configured for (doesn't hurt to validate)
2) Check to see if you can ping myserver.domain.local from a command prompt to make sure that name resolution is working.

Then get back to us.

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, 
       t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, 
       e.connection_auth_desc 
FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
 ON     e.endpoint_id = t.endpoint_id;

Open in new window

Distinguished Expert 2017

Commented:
Can one ping the other?
Tracert is there a VPN between the sites?
What are the IPs?
Does each server instance run with the same domain account or does each use local account?
check my blog for mirroring in simple steps. for sure you will get the answer.
just check the FQDN(Fully Qualified DOmain Name,)
Remember to add that particular port in exception list in Firewall and Anti Virus if installed.

check my blog for DAtabase MIrroring in SIMple Steps

Author

Commented:
Hi all,  thank you for your replies.

Network and DNS is fine between the two servers, they are both live machines (they are at different ends of my MPLS network).

Principal is on 10.7.100.3/24, Mirror is on 10.9.100.28/16 they can both reslove eachothers hostnames and FQDN

@dbaduck: I have run that query (thanks very useful) and I the information on both servers is the same, port 5022 with encryption enabled.


@sqlserver: I will read your blog now, thanks

K

Author

Commented:
Hi all, two problems I found.

1) The service accounts were actually running under different accounts (despite me thinking they weren't) - odd as this has not caused me an issue before.

2) I needed to restore some transaction logs to the end of the DB restore on the mirror else the prncipal threw an error.

Cheers

K

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial