SQL Server Performance Forum – Threads Archive
Replication through firewalls
Before I go set up some test machines I thought I would ask a few questions here. We’re going to replicate data over the internet (transactional replication) and using UNC paths for replication will not be allowed since it requires 135,445 etc through the firewalls. I want, if possible, send all data over the SQL port (say 1433). So I’m looking at some options. Publisher and distributor are on the same server. Subscriber is on another. 1.I noticed that if I select a local path like D:MSSQLREPLDATA for my snapshot folder SQL Server will say "Note that you can only use push subscriptions with a local snapshot folder". Does this mean that you can actually push the snapshot over the SQL port just like you do with the transactions? That the publisher will write the snapshot files on local disk and then read them again and push to the subscriber. Or how does that work? Maybe you need disk access via UNC to the subscriber instead of to the publisher as when you do a pull subscription? Would be cool if no UNC was needed. 2.
I looked at the FTP option but the publisher still has to have UNC path access to the root folder of the FTP. Only the subscriber can connect to an external FTP and download the snapshot folders. That means that if we are replication some tables in one direction and others in the other direction we need to have a FTP running in both locations. Also the ftp client used by SQL seem to use active mode and not passive making firewall rules a bit complicated. 3.
I heard something about using merge replication even if you’re only replicating the specific tables in one direction and that this type of replication would go over the SQL port only. But how do you resync a merge replication that is out of sync? Isn’t there some snapshot option here too?
Did you get anywhere with this ?? I need to replicate from our public website, back through the firewall to our local SQL server. I couldn’t get the local SQL server to read the snapshot folder on the public server.
No, project was put on hold…(that’s a new one) but I think option 1 using push might work. Haven’t had time to try it yet.
FYI: I’ve set this up now and you can push snapshot and transactional replication over a single port through a firewall to a destination server. With push you can use a local snapshot folder since the destination server doesn’t have to read it.
Hi Argyle, we have a similar problem. How have you realized the replication over one port?
Hi ya, if you’re using push replication across the firewall then all communication is over the sql port (so 1433 by default) for pull replication across a firewall what I’ve done before is have a subscriber on the same side as the publisher, and manully copy this across the firewall to the initial subscriber database on that side (so do your own synchronisation) after that again the only port used is the sql port lastly at times we’ve also allowed direct access through the firewall to a database server (which was in a second DMZ separate from the webservers) This connection was tightly controlled in terms of a locked down sql account Cheers
Twan
Something more general to add… If I have a choice I would always structure my systems so that there is
– a dmz with all of the public facing servers (i.e. web server in particular)
– a second dmz with supporting servers for those public services (e.g. Domain Controllers, SQL Servers, etc.)
– servers for internal applications I then allow very thightly controlled access from the public facing servers to the supporting servers
– ports locked down on the stateful firewall
– user accounts locked down
– only stored procedure access to databases
– check that there is no SQL Injection (i.e. always execute the proc without using the exec keyword, and use the proper ADO parameter object rather than string concatenation) Then there is no reason why the internal applications couldn’t access the same SQL Servers rather than replicate the data. I’m a firm believer of only replicating when it is required for reasons of
– bandwidth (although explore colocating the application/web servers, and thin client access)
– security (although as long as the external databases only hold the data collected or use by the public systems, then there is no real difference)
– performance (if the web is oltp and internal is olap) Cheers
Twan
]]>