I am doing research into how to have both a readable secondary database that we can use for offloading heavy queries (such a reports) and also (ideally) use as highly available backup. We are a VERY small shop, and we do not have the option of hiring a dedicated DBA. We need a solution that is low maintenance.
We went down the road of Transactional Replication. We have it configured on our staging servers, but we've hit some issues. (Short version of the issues: the subscriber database is sllloowwww. And, making schema changes in the publisher is a serious PIA.) I am second guessing my decision to use Transactional Replication. When I began to research the solution to these problems, it became clear that managing Replication is a much larger task than originally anticipated. It has us a bit spooked.
We are already doing nightly off-sight backups in another region of the country. We're good for pure DR. It's HA and a readable secondary that we lack.
Here are my questions:
For a small shop with no DBA, what solution(s) should we consider? Is there one single solution that will provide both a readable secondary and HA? Or should we be looking at two different solutions? Will Always On provide both? Should we push forward with Replication?
What would you do?