Most of the times you would run into scenario where you have a firewall between publisher/ distributor & subscriber
|
| F
| I
PUBLISHER | R
| E SUBSCRIBER
| W
DISTRIBUTOR | A
| L
Most people usually only allow traffic through one TCP/IP port on which the respective SQL instance is listening & in most of the cases even SQL Browser service is stopped & Named Pipe protocol is disabled.
But if the subscriber is a named instance listening on either static or dynamic port then you can only connect to it using either an alias or specfying the port number along with SERVER\INSTANCE NAME.
In such scenario if you try to create a subscription & specify SERVER\INSTANCE NAME, <PORT> for the sbscriber server either in the New Subscription wizard or in T-SQL then it failes with below error for example
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'HIMALAYA\KILLER'. (Replication.Utilities)
You will not be able to create subscription because the doesn't allow server with port or ip address alone or with port. However, there is a special case with aliases. Though the above error message tells you not to use aliases but as long as the alias name is same as the subscriber server name & you have specified correct port number in the alias the subscription will be created & replication will successfully work. Create a TCP/IP alias on publisher server with same name as susbcriber server\ instance with correct port.
Make sure that when you run select @@servername on subscriber it returns you the correct server name
NOTE
=====
Alias names can be case sensitive, make sure to use the same case while specifying alias in SQL Server Management Studio or any replication wizard
Great post, explained really well and I could really understand. Thank you.
If you can give more detail please. on how creating TCP/IP alias of the subscriber
thanks
++
hi tahar, workaround is simple. Create an alias of the subscriber with the same name of the subscriber
for e.g. if SQLGUY\YUKON is listening on port 4532 then create an alias with name SQLGUY\YUKON & in the alias configuration you can specify the port number & the server\instance name.
Let me know if it helps
Dear anurag
i am facing same problem but solution is not find at yet.please help
Rishi
Hello Rishi,
Can you explain your scenario in detail ?
Thanks,
Anurag
hi anurag, imagine this.
2 sql server 2008
the scenario is the same as you described
but the servers are listening to port 1433 and do not have any connections to each other (publisher=distributor and cannot reach subscriber)
now i added port 4010 and wanted to create a replication. only port 4010 is open (firewall rule).
how can i do that?
how do i have to configure sql server so that the replication works only using port 4010?
i have tried quite a few constellations but as soon as i close 1433 nothing works anymore...
thanks in advance!!
daniel
hello daniel,
if only port 4010 is open in firewall then SQL Server must listen on port 4010. All the clients outside firewall trying to connect to SQL Server will use port 4010 in connection string/ alias and will pass through the firewall as well but will fail if SQL Server is not listening on that port.
Though you can configure SQL Server to listen on multiple ports but replication is oblivious to any port setting. Replication subsystem just operates over linked servers and as long as normal connectivity over firewall is working fine, replication will also work fine.
Replication clients outside firewall will anyways only be able to communicate over 4010 because, A) they only know port 4010, B) only 4010 is allowed through firewall. Clients inside firewall may be able to leverage other ports on which SQL is listening.
Please create TCP alias on subscriber with port 4010 and test normal connectivity to publisher before configuring replication.
Refer
"How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)" [http://msdn.microsoft.com/en-us/library/ms177440.aspx]
and
"How to configure SQL Server 2005 to allow remote connections" [http://support.microsoft.com/kb/914277]