1

We have a heavily used database server (SQL 2012 Web edition) which is currently a single point of failure (nightly backups and no additional hardware provisioned!)

The server has a constant stream of data being added and periodic heavy-duty read operations to extract data for applications.

We're going to upgrade to SQL 2017 and then:

1) Split out the reads onto one server and the writes onto another

2) Have a better Disaster recovery situation - i.e. not have to wait a day to provision new hardware and restore backups. It's ok to lose a few minutes of data if we have to.

I've spent a while reading up on options and it seems the Always On High Availability groups are the best option.... however they require the Enterprise edition of SQL server (which for two 8 core servers is about 20x more money than the "Server-5 CAL" option we need and just not justifiable!) - I'm aware there are also Basic Availability Groups with Standard edition, but they don't support querying the secondaries, which is no use to us.

Log shipping is not a bad option, except the fact that it either disconnects users or queues restores is a problem - we'll quite likely have users connected and they won't want to have their long-running queries terminated!

Transactional replication seems to be a bit too granular - table level, requiring tables to have primary keys etc. it feels like it's going to be a maintenance problem and not the appropriate solution

So, I'm left with Database mirroring - which Microsoft have deprecated! (although it's still there in SQL 2017, probably because there isn't a good, cheap alternative!)

I'd appreciate any thoughts/suggestions?

Improve this question
12
  • 1
    How much latency is tolerable on the read side?
    – Tara Kizer
    Jan 5, 2018 at 18:53
  • I wrote up an idea that at least partly resolves the problem you mention with Log Shipping : sqlperformance.com/2014/10/sql-performance/…
    – Aaron Bertrand
    Jan 5, 2018 at 19:02
  • @TaraKizer - I'm going to say we need low latency on reads, although I think it's possible we could re-architect the read-side of things in the future so that's not so necessary.
    – Mark
    Jan 5, 2018 at 19:04
  • 1
    Low latency is a problem. Transactional replication is your best bet for Standard Edition. Otherwise, you are going to need to cough up the dough to use Availability Groups. Have you looked into RCSI though? You may not even need to split the workload. Enough hardware, tuned queries, good indexes and RCSI=often a happy environment.
    – Tara Kizer
    Jan 5, 2018 at 19:10
  • 5
    There is an old saying, "You want it good, fast, and cheap? Okay, pick two." Sometimes there isn't a perfect solution if you have too many must-have requirements, and in those cases you often have to give up one or more needs. Just food for thought.
    – Aaron Bertrand
    Jan 5, 2018 at 19:14

1 Answer 1

Reset to default
1

Log shipping is what we end up using. However licensing dictates that we pay for additional licenses if we use the secondary in any way at all (even to take backups from)...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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