Replicating two Databases that are on isolated networks

Question

What is the best way to replicate two 2008 SQL Server databases as well as capture any on-going incremental changes (e.g. new transactions) ?  Here is the catch... the two databases are on two separate isolated local networks where the two databases are not allowed to communicate with each other.  MS database replication methodology works the best for us because it updates a subscriber database on an ongoing bases, however, it requires that both publisher and subscriber databases directly communicate with each other (either on the same server or over a network connection).  Our networks are isolated from each other, as our business rule governs that they may NEVER directly communicate with each other.

Ideally, we wish to initially take a full backup of the primary server and restore it on the secondary server on the isolated network, then, on an on-going bases, grab files from the primary server containing new transactions and restoring them on the secondary server, therefore the two databases are reasonably mirrored at any given point in time.

We considered using Log shipping, however I believe the primary server needs to communicate with the secondary server, therefore this method may not work for us.

Can someone suggest me with any options please?

Both servers are running MS SQL 2008 R2

Thanks,

Binit

 

Answer 1

You can design something in a similar way as log shipping. Transfer the log files from a file share location to another server.  To restore the recent log from the first database and restore from there.

Answer 2

You are looking for DR solution of your SQL server.

SQL server provide Replication, Logshipping, Mirrioing &  Alwayson DR solution. But all solutions are posssible when you have network communication between 2 servers.

Here, Best I can say to use Log shipping for this. You can setup on shared location or system(3rd server) that has communication from both server

• Take backup at primary server
• Upload /copy backup file to shared folder on 3rd server
• Copy file to secondary server from sahred or 3rd server
• Restore backup at secondary

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

Log shipping will work, even if the "Log Shipping" feature doesn't.  The Log Shipping feature just automates a process that DBAs have been doing forever.

Also Transactional Replication does not require the Subscriber to connect to the Publisher.  The Subscriber connects to the Distributor.  If there is a third computer that can talk to both servers, then Transactional Replication might work.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

You can use FTP location for LOG shipping to copy backups as well.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 5

I'm confused. You talk about log shipping and also replication. Those technologies has both different usages. Log shipping is for warm standby (DR, basically), while repliction is primarily for scalability (and not DR or HA). So my suggestion is that you first decide which of these two that you want t solve and *then* think about implementation.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 6

Thank you Rohit,  I found a solution that closely matches your suggestion.  I just take a full backup and then any subsequent backup of transactions and then restore on the warm standby (secondary) server on the other network.  The trouble I experienced was that I wasn't able to continue restoring transactions on the secondary server while also allowing users to have read-only access to the secondary server.  I figured out the syntax.  The following works for me:

-- ////////// BACK UP PROCESS ////////////////////////

ALTER DATABASE [<databasename>] SET RECOVERY FULL WITH NO_WAIT

-- Back up the database to establish the base:

BACKUP DATABASE [<databasename>]
TO  DISK = N'\\psf\Home\bkup\Datbase_Full.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10


-- Back up transactions

BACKUP LOG [Contacts]
TO  DISK = N'\\psf\Home\bkup\Contacts_01.trn'
WITH NOFORMAT, INIT,  NAME = N'Contacts DB - Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM


-- ////////// RESTORATION PROCESS on the standby server ////////////////////////
--Backup database and transactions log files will be physically re-located using a special file-mover network appliance, hence not through a direct network connection. We need air tight security 

-- RESTORE THE BASE

USE master
GO
RESTORE DATABASE [<databasename>]
FROM  DISK = N'\\psf\Home2\bkup\Database_Full.bak'
WITH  FILE = 1,
REPLACE,
STANDBY = N'c:\xx\rollback.BAK'
GO


-- RESTORE THE SUBSEQUENT TRANSACTIONS

USE master
GO
RESTORE LOG [Contacts]
FROM  DISK = N'\\psf\Home2\bkup\Database_01.trn'
WITH  FILE = 1,
REPLACE,
STANDBY = N'c:\xx\rollback_trn01.BAK'
GO

Answer 7

The Problem with LogRestore as your seeding mechansim is that you have to kick all users out while you seed new data. LogShipping can do this for you automatically, in your case you have to do it yourself. There is unfortunately no way around this, except for using other Technologies (like replication or AlwaysOn Availability Groups), but most of those Need some Kind of Access between the Servers...