Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Jan 8 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? –  Aaron Bertrand Jan 8 at 15:07
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
1  
Thanks! Didn't even think of that. I'll give that a shot –  user1873604 Jan 8 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. –  user1873604 Jan 8 at 16:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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