April 20, 2015 at 11:43 am
Hi everyone, I'm reaching out to the community to hopefully get some help. I was working on this all last week, and I am running out of steam :crazy: . I'm also extremely surprised there is not more information out there on remote log shipping and mirroring setups.
Scenario
We have a production sql server in house that we use with a commercial program for attendnace for around 200 employees. We have an identical hosted Sql server and interface offsite that is available for web traffic. Everything works perfectly when I manually backup and restore to this offsite database. We want to keep this offsite database refreshed, as much as possible. So I configured a log shipping scenario and automated it. In doing so I soon realized that with log shipping, the destination database has to be left in the offline state in order to apply transactions logs. As soon as you apply a tlog with recovery, boom, you cannot apply any more tlogs.
This remote database needs to be online and allowing traffic. Everything I can find on jerry-rigging remote log shipping (which is not much) ALL refers to DR and fail over configurations, which is not what we need. The transactions going on on our remote sql server instance don't matter. All the matters is that the data is being refreshed as often as possible, and that people can see it in the interface. So if the database needs to go offline for a few during the tlog restore, thats fine. But currently the only way to do that would be to apply a full backup to it with norecovery, which means we would be shipping multiple full and differential backups daily along with logs, which of course is not desired (30gb db).
Main Question
It seems like I am really missing something in my thought process here. Why is it that there is not more information out there on replicating to a live database from a primary production database? Everything about mirroring, log shipping, replication etc, always involves the same server/instance. Isn't replicating to a remote server a common request? Is this not a common high availability scenario? Is there any way I can apply transaction log backups to a database that is ONLINE? Is there any other way I can accomplish this that you can think of?
Please consider that opening our internal network to web/database traffic is off the table for now. The primary idea is replication to a remote database, that is online and available. Also keep in mind, I have already successfully made a log shipping configuration to this remote database that works, but keeps the db offline.
Thank you so much for any insight or help you can provide!
Matt
April 20, 2015 at 3:47 pm
Log shipping/mirroring/Availability Groups to a remote server is indeed fairly common.
As you correctly noted, the most common scenario for that is DR, where the database at the remote site is offline.
With Availability Groups, you can have read-only replicas, which would allow read workloads to run against the remote database.
With log shipping, you can run the restores using WITH STANDBY, and you can run read-only workloads against the standby database.
As you also note, during the restores the database would have to be inaccessible.
You could also use replication to push the data to a remote server, and the remote database would stay online.
If what you want is a fully-available read/write copy of the DB on a remote server, then SQL Server doesn't really offer a good option for that, as guaranteeing consistency of the data between sites is an issue (there is merge replication, but that's rarely a good idea).
All of these solutions have their own advantages and disadvantages, so which one is best for you will depend on your specific expectations and requirements.
A couple key questions to start, though, are these:
1) Is the remote copy of the database expected to handle a read-only or read/write workload?
2) How close to real-time is the data expected to be?
Also, could you confirm the version and edition of SQL Server you're on?
Cheers!
April 21, 2015 at 2:22 am
You're probably looking for transactional replication here.
Log shipping and mirroring are HA/DR technologies, hot standby databases for when/if the primary fails.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply