4

We would like to set up replication between two SQL Server 2012 instances.

Server "Internal" can see and make connections to server "External". External is completely firewalled from the internal network. No inbound connections are allowed on any port.

We want to set up transactional replication from internal to external for a read-only copy on server External. When configuring External as the subscriber, it wants to connect to the publisher which it cannot do.

All my searching leads me to articles on which ports to forward but that is not going to work for us. Basically what we want to do is have Internal initiate the connection and push updates to a copy on External. Up until now we have been using SSIS packages to push data but it's starting to border on unmanageable.

migrated from stackoverflow.com Jul 21 '15 at 0:52

This question came from our site for professional and enthusiast programmers.

  • 1
    You'll have to adjust the firewall with specific rules by port (which you mention is not possible) or setup a site-to-site VPN to bypass the firewall. – Jason W Jul 20 '15 at 18:43
  • Does your firewall maintain session state and keep it open? Those are usually more expensive. If not, Jason W. is on the right track and just setup a VPN. – Ali Razeghi May 22 '16 at 2:17
  • This is totally possible using a push subscription from the publisher, you will just need to use SQL accounts and have the SQL port opened. The only hiccup I have run into is a SQL alias (cliconfg) with the same name as your SQL instance on the other side of the firewall since the replication will check the name you use in SSMS during setup against @@servername on the external SQL instance. – Jason Cumberland Aug 1 '18 at 14:37
1

Are you able to set up a shared folder where only the SQL Service accounts have access? If so, since you are looking for a read-only subscriber, one option may be to use log shipping leaving the database in STANDBY. Of course, this is dependent on how often you need the data updated. Every time a log is restored, the users will be kicked off the secondary. Let's say every hour you restore a log backup, users will be kicked off during the restore, but the data will be pretty close to that time.

0

The subscriber has to connect to the publisher to get details about what is going to be replicated. You can set up a distributer, a middle-man if you will, that external has access to. However, I do not know if that will help at all.

Internal Publisher --> Intermediate Distributor --> External Subscriber

  • Thanks for your thoughts. We thought of this as well but setting up the distributor is the same, it wants to be pointed at the publisher on the other side of the firewall. – user3799699 Jul 20 '15 at 18:57
  • Then it has to be like Jason W mentioned in the comment above. The only thing I could think of to try would be to allow access and get the initial snapshot, then close the port, however, it is a total guess that this would even work and not cause problems down the road. – lrb Jul 20 '15 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy