Hi, Currently we would like to setup replication between 2 sql instances (binded to corresponding IP, listening on port 1433) on different servers (DUAT01, DUAT101). However, we found the SSMS cannot connect remote server A from server B using named instance.

The sql instances can be connected by using either IP or FQDN, however, that cannot fulfill the requirement on setting up replication as it require the instance to connect as "DUAT101\SQL2K8R2" instead of "x.x.x.x" or "SQLUATCSS2K8R2.domain.com".

We tried to change "Protocol"->"Listen All" from "No" to "Yes", then SSMS can connect the named instance successful in this format "DUAT101\SQL2K8R2", but the application using "SQLUATCSS2K8R2.domain.com" to connect sql instance was fail.

Pls advise how to make it possible to connect sql instance successful in both way. thanks.

See if this thread helps you

http://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address

can connect the named instance successful in this format "DUAT101\SQL2K8R2", but the application using "SQLUATCSS2K8R2.domain.com" to connect sql instance was fail.

Hello,

To connect to a named instance you have to use either the instance name or the instance IP port, like
DUAT101, 1433
The only other Option is to create a SQL Server alias using CliConfg.exe (Client configuration) tool.

See if this thread helps you

http://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-a

can connect the named instance successful in this format "DUAT101\SQL2K8R2", but the application using "SQLUATCSS2K8R2.domain.com" to connect sql instance was fail.

Hello,

To connect to a named instance you have to use either the instance name or the instance IP port, like
DUAT101, 1433
The only other Option is to create a SQL Server alias using CliConfg.exe (Client configuration)

It always return the default instance with "DUAT101, 1433".

It always return the default instance with "DUAT101, 1433".

Of course you have to use the correct IP port of the other instances, not those of the default

In SSMS are all SQL Server added as "Registered Server"? If so then all should be selectable in "Replication Wiz

Hi hklbj23,

Since SQL Server replication requires the actual server name to make a connection to the subscriber server, and IP address is not supported. So please make sure to use the right server name to connect to the subscriber server when creating the subscription.

To get the right server name of your subscriber server, please login to your subscriber server using IP port through SQL Server Management Studio according to Olafs post, and execute the following statement: select @@servername

Regards,
Michelle Li

• Edited by Michelle LiMicrosoft contingent staff, Moderator 18 hours 41 minutes ago

Hi hklbj23,

Since SQL Server replication requires the actual server name to make a connection to the subscriber server, and IP address is not supported. So please make sure to use the right server name to connect to the subscriber server when creating the subscription.

To get the right server name of your subscriber server, please login to your subscriber server using IP port through SQL Server Management Studio according to Olafs post, and execute the following statement: select @@servername

Regards,
Michelle Li

• Edited by Michelle LiMicrosoft contingent staff, Moderator Thursday, March 19, 2015 12:59 PM

Hi hklbj23,

Since SQL Server replication requires the actual server name to make a connection to the subscriber server, and IP address is not supported. So please make sure to use the right server name to connect to the subscriber server when creating the subscription.

To get the right server name of your subscriber server, please login to your subscriber server using IP port through SQL Server Management Studio according to Olafs post, and execute the following statement: select @@servername

Regards,
Michelle Li

Michelle, Olaf

I tried the method you mentioned above, however, I still can't make it even I created the registered server. Detail can refer to upload screen shot below.

• Edited by dave_gona 5 hours 23 minutes ago

DUAT01\SQL2K12

Can you tell me if you type ping -a ipaddress if you see server name getting resolved?

If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

• Edited by dave_gona 3 hours 24 minutes ago

Yes, server name can resolved and no firewall between DUAT01 & DUAT101.

• Edited by hklbj23 3 hours 10 minutes ago

Ok so we are resolving different name then hostname...

Type hostname what it returns on cmd?

What happen when you ping DUAT01?

Did you try adding the SQL Server machine into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad.  Make sure you have a copy of host file first and then edit and add below line:

10.161.0.46 SQLUAT2K12_CI_As.glshk.com SQLUAT2K12_CI_As DUAT01

You have to do on both machines...

• Edited by dave_gona 2 hours 36 minutes ago

Since DUAT01 have 5 SQL instances (2008 x 2, 2008R2 x 2, 2012) with 5 IPs they are all listening on port 1433. If I change the host file in DUAT101 (as client machine) then I can only connect to only 1 instances at one time.

5 instances name as

MSSQLSERVER

SQL2K12

SQL2K8_CI_AS

SQL2K8R2

SQL2K8R2_BIN

• Edited by dave_gona Friday, March 20, 2015 2:18 AM

You are saying you have 5 different IP address for 5 instances? 

Type hostname what it returns on cmd?

What happen when you ping DUAT01?

• Edited by dave_gona 1 hour 20 minutes ago

If you have different ip you can still add ip in host entry 

ipaddress DUAT01

Please refer

http://www.rickygao.com/how-to-use-the-same-port-for-multi-instance-in-a-single-sql-server/

• Edited by dave_gona 1 hour 11 minutes ago

If you have different ip you can still add ip in host entry 

ipaddress DUAT01

Please refer

http://www.rickygao.com/how-to-use-the-same-port-for-multi-instance-in-a-single-sql-server/

Dave,

our setup in DUAT01 is already as the link mentioned but it still prompt same error.

Could you paste you /etc/hosts file?

Could you paste you /etc/hosts file?

Can you tell me if you type ping -a ipaddress if you see server name getting resolved?

If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

• Edited by dave_gona Friday, March 20, 2015 4:17 AM

Yes, server name can resolved and no firewall between DUAT01 & DUAT101.

• Edited by hklbj23 Friday, March 20, 2015 4:30 AM

Ok so we are resolving different name then hostname...

Type hostname what it returns on cmd?

What happen when you ping DUAT01?

Did you try adding the SQL Server machine into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad.  Make sure you have a copy of host file first and then edit and add below line:

10.161.0.46 SQLUAT2K12_CI_As.glshk.com SQLUAT2K12_CI_As DUAT01

You have to do on both machines...

• Edited by dave_gona Friday, March 20, 2015 5:04 AM

You are saying you have 5 different IP address for 5 instances? 

Type hostname what it returns on cmd?

What happen when you ping DUAT01?

• Edited by dave_gona Friday, March 20, 2015 6:21 AM

If you have different ip you can still add ip in host entry 

ipaddress DUAT01

Please refer

http://www.rickygao.com/how-to-use-the-same-port-for-multi-instance-in-a-single-sql-server/

• Edited by dave_gona Friday, March 20, 2015 6:29 AM

Could you please add in host file and try

10.161.0.46 DUAT01\SQL2K12

Could you please add in host file and try

10.161.0.46 DUAT01\SQL2K12

It doesn't work either.

Please type start ---> cmd --> hostname and also ping 10.161.0.46? Also i think you said if you use ip address you are able to connect to instance right?

screeshot of ssms with ip (10.161.0.46)

Please type start ---> cmd --> hostname and also ping 10.161.0.46? Also i think you said if you use ip address you are able to connect to instance right?

screeshot of ssms with ip (10.161.0.46)

ping DUAT01

ping 10.161.0.46

• Edited by dave_gona 15 minutes ago

ping DUAT01

ping 10.161.0.46

Also when you connect(10.161.0.46) ssms with ip address it connects to which SQL instance?

it is DUAT01\SQL2K12 instance with 10.161.0.46

ping -a 10.161.0.46

ping -a 10.161.0.46

Also when you connect(10.161.0.46) ssms with ip address it connects to which SQL instance?

it is DUAT01\SQL2K12 instance with 10.161.0.46