Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirroring between servers that are not part of a domain Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 1:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:35 AM
Points: 47, Visits: 335
I am having trouble setting up mirroring in a SQL Server 2008R2 environment.

I am getting the following error:
The server network address "TCP://Server_Name:5022" can not be reached or does not exist..."

Neither server is part of a domain. They are on the same IP subnet: 192.168.11.xx and can communicate. So, pings are successful between servers using both IP addresses and server names. The SQL server instances can also "see" each other. There is no third party firewall between the two servers. Only Windows Firewall is being used.

Endpoints have been successfully created using both certificates and SQL service accounts (see this blog post)

The servers do not have a FQDN. I've run the following PowerShell script:
[System.Net.Dns]::GetHostByName(($env:computerName))
and it returns just <server_name> and ip address.

When I try to start the mirroring session it fails with the above error message. In addition to trying both endpoint types, I have tried both server name and ip address in the alter database set partner statement.

As far as I can tell, the tcp port 5022 has been opened on both servers for incoming and outgoing connections.

Can anyone help?

Thank you,

Tom Mucha
Post #1547665
Posted Wednesday, March 5, 2014 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Take a look at this blog post by Kendal Van Dyke. I think it might help.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1547894
Posted Wednesday, March 5, 2014 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 6,178, Visits: 13,323
Pass through authentication will work but it's by no means secure hence why MS specify certificates.

This link has a complete example on setting up mirroring using certs





-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1547910
Posted Wednesday, March 5, 2014 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:35 AM
Points: 47, Visits: 335
Thanks Jack,

I should have mentioned that I have read that post and tried what was suggested to no avail.

Tom
Post #1547918
Posted Wednesday, March 5, 2014 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:35 AM
Points: 47, Visits: 335
Hi Perry,

Thanks for your response. Although the steps in the article you referred to were pretty much what I had done before, I started from scratch and followed the instructions (hopefully correctly) and still no joy.

I have established that the TCP ports are open and with the command "telnet 192.168.11.xx 5022" I seem to be getting a successful connection via CMD shell.

But I still am getting the same error from the principal sql instance.

Is it possible that the databases are not in-synch enough?

Tom
Post #1548027
Posted Wednesday, March 5, 2014 3:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
I haven't read the article that Perry posted so I don't know if this is in there , but have you verified that remote connections are enabled on both SQL Server.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1548038
Posted Wednesday, March 5, 2014 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 6,178, Visits: 13,323
Thomas Mucha (3/5/2014)
I have established that the TCP ports are open and with the command "telnet 192.168.11.xx 5022" I seem to be getting a successful connection via CMD shell.

But I still am getting the same error from the principal sql instance.

Is it possible that the databases are not in-synch enough?

Tom

As you have no domain and no DNS server have you ensured that name resolution is configured between nodes?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1548045
Posted Friday, March 7, 2014 12:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:35 AM
Points: 47, Visits: 335
Hi all, Thanks for all the help. Apologies to Jack because although I had read Kendall'sblog post I glossed over the hosts file suggestion. This is what I will try next.

In response to others, the servers communicate, the host file allows server name resolution and I've validated the tcp connection via tenet.

I think the problem is down to the lack of fqdn. So will try to create a fake one via the hosts file.

Tom
Post #1548590
Posted Friday, March 7, 2014 12:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 6,178, Visits: 13,323
Thomas Mucha (3/7/2014)
I think the problem is down to the lack of fqdn. So will try to create a fake one via the hosts file.

Tom

No it's not. Mirroring will accept an IP address quite happily instead of fully qualified names. When using the gui only fully qualified names are accepted.

Looking back through this topic there does not seem to be a logical step by step diagnosis. My immediate suspicion is that the certs have not been correctly associated with the users and the endpoints are not communicating.

For further help with your configuration check these

Troubleshoot Database Mirroring Configuration (SQL Server)

MSSQLSERVER_1418

Use Certificates for a Database Mirroring Endpoint (SQL Server)


Further, this article details the following

MS189921
Syntax for a Server Network Address
--------------------------------------------------------------------------------

The syntax for a server network address is of the form:

TCP://<system-address>:<port>

where

•<system-address> is a string that unambiguously identifies the destination computer system. Typically, the server address is a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address:

◦If the systems are the same domain, you can use the name of the computer system; for example, SYSTEM46.

◦To use an IP address, it must be unique in your environment. We recommend that you use an IP address only if it is static. The IP address can be IP Version 4 (IPv4) or IP Version 6 (IPv6). An IPv6 address must be enclosed within square brackets, for example: [<IPv6_address>].

To learn the IP address of a system, at the Windows command prompt, enter the ipconfig command.

◦The fully qualified domain name is guaranteed to work. This is a locally defined address string that different forms in different places. Often, but not always, a fully qualified domain name is a compound name that includes the computer name and a series of period-separated domain segments of the form:

computer_name . domain_segment[....domain_segment]

where computer_name is the network name of the computer running the server instance, and domain_segment[....domain_segment] is the remaining domain information of the server; for example: localinfo.corp.Adventure-Works.com.

The content and number of domain segments are determined within the company or organization. If you do not know the fully qualified domain name for your server, see your system administrator.

Note
For information about how to find a fully qualified domain name, see "Finding the Fully Qualified Domain Name," later in this topic.


•<port> is the port number used by the mirroring endpoint of the partner server instance. For information about specifying an endpoint, see Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL).


Examples of the above are

--A. Using a system name
--The following server network address specifies
--a system name, SYSTEM46, and port 7022.

ALTER DATABASE AdventureWorks SET PARTNER ='tcp://SYSTEM46:7022';

--B. Using a fully qualified domain name
--The following server network address specifies a fully
--qualified domain name,
--DBSERVER8.manufacturing.Adventure-Works.com, and port 7024.

ALTER DATABASE AdventureWorks SET PARTNER =
'tcp://DBSERVER8.manufacturing.Adventure-Works.com:7024';

--C. Using IPv4
--The following server network address specifies an
--IPv4 address, 10.193.9.134, and port 7023.

ALTER DATABASE AdventureWorks SET PARTNER =
'tcp://10.193.9.134:7023';

--D. Using IPv6
--The following server network address contains an
--IPv6 address, 2001:4898:23:1002:20f:1fff:feff:b3a3, and port 7022.

ALTER DATABASE AdventureWorks SET PARTNER =
'tcp://[2001:4898:23:1002:20f:1fff:feff:b3a3]:7022';



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1548595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse