Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring between servers that are not part of a domain


Mirroring between servers that are not part of a domain

Author
Message
Thomas Mucha
Thomas Mucha
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 398
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

In SQL there are no absolutes, it always depends...
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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" ;-)
Thomas Mucha
Thomas Mucha
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 398
Thanks Jack,

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

Tom

In SQL there are no absolutes, it always depends...
Thomas Mucha
Thomas Mucha
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 398
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

In SQL there are no absolutes, it always depends...
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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" ;-)
Thomas Mucha
Thomas Mucha
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 398
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

In SQL there are no absolutes, it always depends...
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search