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?
Best Answer
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)...