3

Is there a way to use SQL Server Replication without a network connection?

My would-be publisher is an SQL Server 2005. It is on the "outside" as it is connected to my company's primary LAN, which is connected through a firewall to the Internet. My would-be subscriber, however, is an SQL Server 2012 that is on the "inside" - due to security reasons, it must be connected to a network that is completely (i.e. physically) isolated from our primary LAN and the Internet. I need a way to replicate outside server changes on the inside on a daily basis.

Is there a way to use Replication without a network connection between publisher and subscriber (e.g. via an optical-disc-based file transfer)? Our security protocol allows for media to be brought inside from the outside, but any kind of communication from inside to outside is forbidden. Ideally, I would run a script on the outside server, burn the resulting output to a disc, and then import the changes (be them inserts, updates, or deletes) to the inside server.

It seems Merge Replication is close to providing this functionality, as it is intended to support subscribers with sporadic network connectivity, but it doesn't seem to support scenarios in which there is no network connectivity.

Further confounding the situation is the fact that due to the size of the database involved (~50 GB), I need a way to only transfer the differential - I would only like to have to burn the daily changes to disc, not the entire DB (thus eliminating Snapshot Replication as an option).

For now, I have both servers on the outside, so I can use network-based Replication for the initial transfer, but once I deploy the subscriber to the inside, this will no longer be a possibility.

I am also open to other means of solving this problem. I have looked into backup/restore solutions, but have run into trouble as I only need to perform this operation on a subset of the data in the DB, and backing up/restoring only a subset of a DB does not seem to be a trivial exercise.

  • This sounds more like a ServerFault question. Essentially, you need the two networks to see each other over all of the relevant ports. This probably requires input from dedicated IT/networking people more so than database administrators and developers... – Aaron Bertrand Sep 23 '14 at 0:49
  • How about differential backups of the publishing database restored to an internal copy, separate from the subscriber database but on the same "inside" server? You would then have more options, such as normal replication through localhost. – dartonw Sep 23 '14 at 1:33
  • @AaronBertrand - our customer dictates physical isolation of the networks, so by definition the networks must not be able to see each other. – Dana Wheeler Sep 23 '14 at 20:33
  • 1
    Then I don't think replication is what you want. – Aaron Bertrand Sep 23 '14 at 20:35
  • @dartonw - I'm experimenting with something close to your solution: I've setup Transactional Replication to produce a separate database on the outside that contains the subset of data I need to bring inside. I'm then running differential backups on that outside, subset DB and transporting the diff backups to the inside. I believe this gives the same flexibility that your solution offers, but does't require transfer of the entire outside DB to the inside...unless there is a nuance of your approach I am missing? – Dana Wheeler Sep 23 '14 at 20:40
2

If you use log shipping, you can do the copy operation of the transaction log backups manually to take them from one server to the other.

  • I tried your suggestion of using log shipping, but ran into the same problem I discussed above: because I'm using two different versions of SQL Server (2012 and 2005 for my inside and outside servers, respectively), I cannot leave my "inside" (secondary) server in the standby state, which I need to do because I need at least read-only access between log restore events. I'll have to either upgrade my outside server to 2012 or install a 2012 instance alongside my 2005 instance and use replication to mirror data from the 2005 server to the 2012 server - then log shipping should work. – Dana Wheeler Sep 24 '14 at 3:35
  • btw I think your security policy or your plan to replicate from outside to inside is bogus. If the policy is to protect data then you have the copy of the data outside of protection, if the policy is to protect against unauthorised change then you are replicating possibly unauthorised change from the outside database! Either way it must be circumventing the intent of the policy. – JamesRyan Sep 24 '14 at 10:09
  • The primary intent of the security policy is to protect data that is generated on the inside. Our customer wants no possibility of accessing inside-generated data from the outside, thus the physical isolation of the networks. At the same time, we need access to the less-sensitive, outside data on the inside as well, thus the need to bring it in somehow without using a network connection. – Dana Wheeler Sep 24 '14 at 17:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.