SQL Server best option for low-cost high-availability and queryable secondary?

by Mark   Last Updated January 05, 2018 19:06 PM

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?

Related Questions

SQL Why is Microsoft deprecating Mirroring?

Updated June 17, 2018 00:06 AM