5

I'm looking into Availability Groups to see if i can benefit from them, but I struggle to understand if I can or not.

Today, we are running a traditional failover cluster with around 150 databases. It is a collection of a lot of different stuff that is in a lot of cases hardly used, but we can't throw it away for a million different reasons. But prolonged downtime is unacceptable. The environment looks like this:

  • No single database is business critical (but the amount of DBs makes it critical)
  • Databases ranging from 10 MB to 10 GB
  • Avg. 30.000 Logical read/s, under 1 Physical read/s
  • Low CPU usage (below 20%)
  • Two physical servers in two data centers
  • Storage is on two VMAX 10k, one in each center, shared volumes distributed with VPLEX, so looing one center will not affect uptime on storage
  • No need for reporting or anything out of the ordinary
  • Most DBs are in simple mode, with a nightly full backup, business have no problem accepting the nightly backup as restore point
  • A rare few databases are running full mode and has 30 min transaction backups

The reason this solution was chosen at the time was licensing and administration reasons. We wanted to cut back on all the single SQL Servers doing nothing but cost us money. And we have removed about 60 SQL Server installations from the environment.

But now there is talk about how we should deal with this in the future, hardware replacement coming up in a year.

And the problem is, as I stated before, I can't quite get my head around if we can benefit from a change, maybe running Availability Groups on virtual machines instead?

So my question is:

What would you think is the best solution to look at given these factors?

  • @shanky it is Always On now (with a space), according to Microsoft marketing, and ranted about recently by Allan Hirt. But that is not the name of the feature, that is just an overarching marketing term that covers Availability Groups, Failover Clustering, even Database Mirroring... – Aaron Bertrand Feb 16 '16 at 15:13
  • Ah, sorry, sloppy of me. I was thinking Failover Clustering VS Availabillity Groups. – Trones Feb 16 '16 at 15:20
5

What would you think is the best solution to look at given these factors?

AlwaysON is designed to meet business uptime and availability wherein you can afford minimal data loss and downtime. It requires proper planning and implementation.

As per your question, it seems that the databases are not that business critical (business have no problem accepting the nightly backup as restore point).

Based on the information that you provided, your business will be able to survive with Log Shipping as a DR solution.

The benefit is that you dont have to worry about windows cluster and other complexities associated with AlwaysON like DNS, Windows failover cluster, etc. Check the prereq and requirements.

Logshipping is well tested and is the most mature of all the DR technologies that you get from SQL Server. You can adjust the frequency of Transaction log backup,copy and restore as per your needs.

  • I understand what you are saying and thank you for a very good answer. But as an additional question, wouldn't there be a lot of more administration with 150 DBs and logshipping? – Trones Feb 16 '16 at 17:22
  • Well administration will be there, but would be much manageable. You would be able to troubleshoot - max there will be share permission issue, or a missing T-Log :-). For AlwaysON, if the quorum is lost, your cluster will be offline and will be a much bigger outage involving multiple teams. Logshipping for 150 (mixed - small and big) is not a big overhead - I am managing more than 250+ databases with logshipping without any issues. 1/2 – Kin Shah Feb 16 '16 at 17:35
  • Write alerts to alert you when there is a problem. Look into enforcing copy only logbackups – Kin Shah Feb 16 '16 at 17:35
  • Got it, thank you again sir. Will look into that instead. – Trones Feb 16 '16 at 17:40

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.