Replication over firewall

  • Article

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

                                     | 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