How to modify the Replication Subscription Job/script to make it point an IP Address and a Port number?

Question

I have an SQL Server 2012 server set up replicating one Database. It is behind a firewall. I have poked a hole in the firewall so I can access the server on Port 1433. I am using an IP Address and a different port number to access from a external hosted 2016 Sql Server.

When I try to subscribe to the Replicated database the Subscription goes thru, however the Job continually fails with "Cannot access Distribution Server [RemoteSQLServerName]"

How can I modify the Job/script to make it point an IP Address and a Port number?

Answer 1

Usually the following ports must be opened for inbound traffic: TCP 1433, 1434, 135, 80, 443; UDP 1434.

Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 is the default instance)

Web synchronization and FTP/UNC access for replication snapshot require more ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File Sharing. File sharing uses UDP port 137 and 138, and TCP port 139 if used along with NetBIOS. File Sharing uses TCP port 445. For sync over HTTP, replication uses the IIS endpoint (configurable; port 80 default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance).

usually you can set up subscribers in this way: exec sp_addsubscription @subscriber = 'Server,port'. try with IP and custom port

source