2

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?

Improve this question
1
  • I understand where you're coming from on these questions, but AGs aren't a "free" technology either. They take learning, care, and feeding just like anything else. If you have a group of people who have never managed them, and don't understand them, you're likely to run into the same type of roadblocks that you hit with Replication.
    – Erik Darling
    May 21, 2018 at 23:01

1 Answer 1

Reset to default
4

@sp_BlitzErik is correct. AGs need planning and care. AGs don't have the particular issues you struggle with in Transactional Replication, but they have others.

If you are comfortable with Windows Server Failover Clusters (WSFC), then you have conquered the biggest hurdle to deploying and running a SQL Server Availability Group. AGs use WSFC for the quorum model, and for a floating IP that moves between the nodes.

If you are willing to not have automatic failover, you can use the new "Clusterless" AGs in SQL Server 2017. See Read-scale availability groups. They are called "Read-scale" AGs to emphasize that they are not intended for HA, but they do support synchronous (zero data loss) replicas, and manual failover.

For a small shop with no DBA, what solution(s) should we consider?

Also must mention that Azure SQL Database is ideal for a small shop with no DBA. And enables you to create readable replicas in the standard tier, and premium tier databases get an automatic readable replica.

Improve this answer
0

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.