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
Best Answer
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.