1

I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.

In server name field I specify serverIP\DBNODE2 and SQL Server credentials.

I'm 100% sure that the credentials are correct, but I'm not sure about the server name part.

enter image description here

When I try to connect I get the following error:

enter image description here

Any ideas what am I doing wrong?

EDIT: I've checked:

  • Started SQL Server Browser service
  • SQL Server service for MSSQLSERVER is running
  • Firewall is off
  • Remote connections are enabled in Configuration Manager
  • TCP ports are enabled

Server name is DBNODE2. SQL Server instance name is MSSQLSERVER.

The only thing, that I don't understand why it's asking server name? This server is in remote geographical location, so I don't think, that it could find the right server without IP address. I've tried bunch of different combinations and none of them worked:

  • IP\DBNODE2
  • IP\MSSQLSERVER
  • DBNODE2\MSSQLSERVER
  • IP
  • DBNODE2
  • Also tried double backslashes
  • Etc.

EDIT2: SQL Server service is running under 1433 port. I've used Port Query tool, which I've found here.

    =============================================
    Starting portqry.exe -n {IP} -e 1433 -p TCP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    TCP port 1433 (ms-sql-s service): LISTENING
    =============================================
    Starting portqry.exe -n {IP} -e 1434 -p UDP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
    Sending SQL Server query to UDP port 1434...
    Server's response:
    ServerName DBNODE2
    InstanceName MSSQLSERVER
    IsClustered No
    Version 11.0.3000.0
    tcp 1433
    ==== End of SQL Server query response ====

ANSWER: Probably server IP address is not allowed in server name field. The workaround is to modify hosts file like @MarkIannucci decribed below.

Improve this question
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White ♦
    Commented Aug 29, 2017 at 10:14

2 Answers 2

Reset to default
1

If your server's ip address is static, you can modify your hosts file with a reference to DBNODE2's ip address and specify DBNODE2 in your Connect to Server Server Name file.

Improve this answer
2
  • 1
    Maybe the problem is that FQDN's aren't allowed in the 'server name' field. If that is the case, you may be able to have your entry for DBNODE2 point to the ec2-********.compute-1.amazonaws.com in your hosts file and get around the static IP address limitation. @Andrew will you give that a shot and report back?
    – Mark Iannucci
    Commented Mar 1, 2015 at 0:01
  • @MarkIannucci It won't work, because under ****** part hides server IP. For instance if server IP is, let say, 78.80.90.181, then long version would be like this ec2-78-80-90-181.compute-1.amazonaws.com.
    – Andrew
    Commented Mar 1, 2015 at 4:57
-1

It's not a good practice to use the SQL Server name when configuring the connection. To connect to a SQL server, you need to specify 2 parameters: Server IP address and port. If you only specify the SQL Server name, then you will depend on the naming resolution, it may or may not work as you expect. So the best practice is to use SQL Alias, for more info on how to set up, check out at https://randynghiem.wordpress.com/2015/01/29/best-practices-on-setting-data-connection-for-ssis-packages/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.