March 5, 2014 at 1:20 am
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...
March 5, 2014 at 9:42 am
Take a look at this blog post by Kendal Van Dyke. I think it might help.
Jack Corbett
Consultant - Straight Path Solutions
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
March 5, 2014 at 9:58 am
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" 😉
March 5, 2014 at 10:11 am
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...
March 5, 2014 at 2:33 pm
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...
March 5, 2014 at 3:10 pm
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
Consultant - Straight Path Solutions
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
March 5, 2014 at 3:55 pm
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" 😉
March 7, 2014 at 12:13 am
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...
March 7, 2014 at 12:45 am
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)
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" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply