September 24, 2019 at 9:15 am
I want to have a standby server at a remote location. it does not need to fail-over automatically, a reasonably fast "mount time" is fine - minutes, or even an hour, worst case, would be OK.
Majority of our users are at Head Office; we have a few users at a second office, and a few who work from home. Ability of those users to connect (Enquiry Only) to DR Server when COMMs to Head Office are down would be beneficial. If data is e.g. a day old that would be fine (for fall-back enquiry only database queries)
If COMMS are down but Head Office is still running (e.g. workmen have cut the telephone/fibre cables) we would want to continue to operate at Head Office and then allow DR machine to refresh / catch-up later. Remote users having enquiry-only access to slightly-old, stale, data would be a benefit.
We will only "Mount" the DR machine Live if Head Office is not operational (e.g. no power available for prolonged period). We have battery backup at head office for over 4 hours, and all the "we dug through the cable" incidents we have had in last X years have been fixed in that sort of time period.
So ... Replication or Log Ship?
My instinct is based on Old Dog knowledge, and may well be out of date, but that said my though is:
Replication will be more work to keep operational - e.g. when we change database structure (common on our inhouse intranet) to decide which databases are replicated
Little / no control over replication traffic, we might have "data storms". Our Document Management System DB is very chatty (updates loads of rows with "pointer to next logical record" stuff.
Whereas I think that Log Shipping would allow:
Disable log shipping (at times when bandwidth-preservation is important), and then when we resume there will either be a huge backlog of LOG backups to "ship", or we will send a Full Backup instead
My assumption is that restarting Replication from a full backup is more work than Log Shipping, but maybe it is even-Stevens these days (compared to "way back then" when I last used Replication)
Re: Skills, I think that Log Shipping would be easy to implement with DBA and reasonably-good-skills Buddy as fallback. Whereas I reckon with Replication, High Availability solution using Availability Groups etc. that we would need 2x trained DBAs, which is well in excess of what we need / would afford (the rest of the time). Happy to be corrected on that though (or we out-source the "If it ever needs fixing" part)
I think what I am fundamentally trying to protect against is an Environmental disaster, and in particular I most definitely do not want a Distributed System (i.e. live updates via any, replicated, server); this is a Disaster Recovery / Business Continuity solution and NOT a solution for High Availability.
Thanks
September 24, 2019 at 10:25 am
Replication is a viable for this situation. However, I shy away from replication very hard because it has received zero love from Microsoft for well over a decade. It's just never going to be any more stable than it is today, and it's not that stable.
So, for a simple DR scenario, yeah, log shipping. It's simple and clear. It's easy to maintain. It generally just works. You do have to allow for the accumulation of logs in the event that someone has cut the cables (hopefully that doesn't happen too often), but overall, for a simplistic DR scenario, log shipping is probably best. Fair warning, it also doesn't get any love since the advent of Availability Groups.
Speaking of which, that is your other alternative, and it would work. An asynchronous setup would do most of what log shipping would do plus more. Generally the same shortcomings. Lots more love from Microsoft and tons of support on the web too. I'd lean heavily in this direction with log shipping as my second choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2019 at 3:30 pm
Very helpful, thanks.
Availability Groups ... An asynchronous setup would do most of what log shipping would do plus more. ... Lots more love from Microsoft
I'm too old to know what Availability Groups is in practical terms (I bump into things that mention them, and HA etc. from time to time, of course, so not completely ignorant ... but I've never got up close-and-personal with them)
I'll trundle off and have a Google and a Read-Up. If anyone has recommended starting points for helpful online info that would be great, thanks.
September 24, 2019 at 5:11 pm
I think there's a Stairway series on them here on SSC.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2019 at 7:08 pm
The Stairway that Grant mentions is here https://www.sqlservercentral.com/steps/stairway-to-alwayson-level-1-what-is-sql-server-alwayson and is a good guide. The first level gives a good overview as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply