I have two SQL Server 2008 R2 systems that, due to network restrictions, cannot use SQL Server replication. However, data needs to be shared between these systems. It may be possible to send a file from one system to another, however.

What I am thinking is possible setting up Log Shipping. I know that to use Log Shipping,the secondary database must be in a NORECOVERY state, so it cannot be used for live OLTP.

What I am thinking is maybe on the secondary server, I could have the secondary database, then have that database replicate to a database on the same local server, and that database would then be the live OLTP database.

I assume replication can't happen while in NORECOVERY mode. Would it be possible to run Log Shipping restores say, every 5 minutes, first changing to NORECOVERY, then doing the restore, then going back to recovery, allowing replication to continue? Is there a TSQL command to change the status to NORECOVERY, and then back?

I'm no SSIS guru by any means, but maybe that's a better way to go (flat file)? Would that be possible to set up in a database with 150+ tables, complete with FK dependencies, where all changes need to be reflected on the target database on a pretty frequent basis

  • where all changes need to be reflected on the target database on a pretty frequent basis how frequent ? Once a day, every one hour ?
    – Kin Shah
    Jan 8 '14 at 15:07
  • 1
    I don't think you can replicate from a log shipped target. But I'm not sure why you are stuck on NORECOVERY - why not STANDBY as Katherine mentioned? Jan 8 '14 at 15:07

You could do log shipping with the standby option rather than norecovery. The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.

  • 1
    Thanks! Didn't even think of that. I'll give that a shot Jan 8 '14 at 15:27
  • Well, this works good to get the data to the secondary server. However, that database is one that users will be constantly hitting with CRUD events, so leaving it in standby really isn't an option, nor is periodically kicking users out to do the restores. I guess what I really need is a way to do replication with two servers that cannot replicate directly with each other. Jan 8 '14 at 16:58

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.