We currently use CDC to replicated our Production DB offsite (Microsoft SQL Server 2008 R2), but using CDC and the way we process the files is very unstable. Also note that the two servers cannot communicate with each other due to security. The CDC files gets transferred through various channels to get to the offsite server. The file transfer process has not let us down in more that 3 year now so that is not the issue.

I am busy with a project to change the offsite replication to log shipping. I have setup log shipping on production and the files arrive safely on the offsite server. However I am struggling to setup a job that automatically restores the log files that gets transferred to the offsite server. Normally this would not be an issue to setup if the two server could communicate with each other.

I also need to setup the standard job to send warnings if no files are being received.

Please help me on the right path.

  • Do you have a server anywhere that can see both SQL Servers? – Aaron Bertrand Dec 8 '14 at 14:17
  • No, not at all possible. – XCom69 Dec 8 '14 at 18:37

There's three separate parts to this:

Q: Do I use SQL Server's built-in log shipping or roll my own? A: I'd roll your own. What you're doing is beyond what the built-in tools are normally used for.

Q: If I roll my own, where do I start? A: I'd use normal SQL Server full and transaction log backups on the primary. On the secondary, start with MSSQLTips' script to automatically restore backup files from a folder. That example will restore all of the files, though, not just some of them - you'll need to adapt it to only restore log backups in there. In a perfect world, you'd only attempt to restore log files that haven't been restored yet, but if you're in a rush, you could restore all of the log files every time - SQL Server will automatically skip logs that have already been applied.

Q: How do I alert when files aren't being restored? Rather than focus on the files, I'd focus on the data. Do restores with standby when you restore the logs so that you can run queries against them, and after each restore job finishes, query your most transactional table looking for the newest record. If it's older than X minutes, your data may not be coming across. (Granted, this only works for databases with decent change rates, like adding records every few minutes - if you don't have that, then some DBAs add a dbo.LastUpdated table with a datestamp in it, and have a SQL Agent job that updates the only record in there every 5 minutes to set the datestamp to current.)

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.