0

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?

  • 1
    How much latency is tolerable on the read side? – Tara Kizer Jan 5 '18 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 '18 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 '18 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 '18 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 '18 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.