AlwaysOn: nice but our servers are virtualized

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8890

    I am currently exploring some high availability solutions so I dived into the world of High availability options for VMWare / Hyper-V or at a lower level. This leads me to the question if AlwaysOn is still an interesting option if you have a HA solution for your Virtual Servers. In other words: Why should I buy an Enterprise Version of SQL server if I have my servers protected at a different level? Isn't AlwaysOn something you should consider if you have physical servers?

    Wilfred
    The best things in life are the simple things

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Firstly, AlwaysOn is a marketing term, not a feature. The two features are Always On Failover Clustering and Always On Availability Groups

    I'm going to assume you mean Availability Groups, because Failover Clustering is available in Standard Edition.

    Will your VMware solution allow readable secondary copies of the database?

    Will it allow for complete failure of the storage array and still work?

    Will it allow for an uncontrolled failover in a matter of seconds?

    Will it allow for a synchronous copy of the database to be in a secondary data centre in case the entire primary data centre fails?

    Will it automatically repair corrupt data pages?

    Will it allow you to offload full and log backups to the secondary copy?

    Will it allow for failover of individual databases or groups of databases?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Grant Fritchey

    SSC Guru

    Points: 395227

    In addition to what Gail talks about, another thing to consider is a layered approach. Depending on just how responsive and thorough you want your DR process to be, it can be a good idea to have more than one method in place. Too often I've seen people say stuff like "Oh, we've got SAN snapshots, we don't need backups" only to find that they didn't plan for certain types of failures that backups can cover. Same thing with VM methods of DR. They work, and work well. They just don't cover all the possibilities. I'm not saying you have to, or should, purchase Enterprise in order to have additional DR and HA mechanisms. I'm just saying it gives you more tools and more layers to help ensure a complete DR scenario.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004424

    The only person who can say whether it's worth buying Enterprise edition for Availability Groups is you (well, your business), but consider that HA/DR is like security. Defend in depth, don't place all trust in a single layer.

    I've seen people say they don't need Mirroring because they have VMs and a mirrored SAN. Then the SAN controller glitches and both copies of the DB are fried. They only had one layer, so they were toast when that one layer fails.

    Ultimately it's a cost- and risk-based decision. How much can you spend, how much risk can you tolerate.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • codykonior

    SSCommitted

    Points: 1531

    Don't forget that your applications should support Availability Groups before you start using it.

    They can't use MSDTC or cross database transactions. They should prefix agent jobs and copy them across to secondaries. If you use SSRS you need something to restart it after a failover and do job cleanup. You need to develop something to sync your logins and linked servers and anything else that needs to be the same across servers. And then you need to monitor it... and provision enough network bandwidth to send everything from one server all the time to another.

    I think they're worthwhile but lots of people set them up without prior planning and work, which is great because it keeps us in jobs.

  • oughtsix

    SSC Veteran

    Points: 269

    We setup an AlwaysOn configuration on one of our production servers as a test and ran it for 6 months.  For our system the expense, complexity and maintenance of AlwaysOn did not make any sense.  We can have a crashed virtual server replaced in a hour using a snapshot of the server system drive and restoring the complete, differential and logs to a point in time no more than 10 minutes prior to the crash.  For our system and data needs this works fine.  If you have a 24x7 system with any data loss causing great expense AlwaysOn might be a good option for you.

    In our test AlwaysOn configuration under SQL2012 we were going to use the ability to query against the secondary server as a way to offload reporting.  This didn't work as queries conflicted with the AlwaysOn updating causing a backlog and pretty much making the secondary server pretty much unusable as a reporting server.  Maybe this has been fixed or reworked in the newer versions of SQL but we were unimpressed with AlwaysOn under SQL2012.

    Database disaster recovery solutions are not a one size fits all deal!

  • Shawn Melton

    SSC-Insane

    Points: 24554

    oughtsix - Friday, March 9, 2018 6:07 PM

    We setup an AlwaysOn configuration on one of our production servers as a test and ran it for 6 months.  For our system the expense, complexity and maintenance of AlwaysOn did not make any sense.  We can have a crashed virtual server replaced in a hour using a snapshot of the server system drive and restoring the complete, differential and logs to a point in time no more than 10 minutes prior to the crash.  For our system and data needs this works fine.  If you have a 24x7 system with any data loss causing great expense AlwaysOn might be a good option for you.

    In our test AlwaysOn configuration under SQL2012 we were going to use the ability to query against the secondary server as a way to offload reporting.  This didn't work as queries conflicted with the AlwaysOn updating causing a backlog and pretty much making the secondary server pretty much unusable as a reporting server.  Maybe this has been fixed or reworked in the newer versions of SQL but we were unimpressed with AlwaysOn under SQL2012.

    Database disaster recovery solutions are not a one size fits all deal!

    This is not the norm...just FYI. I have many clients with high traffic OLTP systems that utilize the secondary replicas for data warehouse workloads and reporting (one primary replica and two secondaries that handle the DW and reporting needs) that never have issue blocking the replication part of AGs. The version of SQL Server this type of workload is done in is both 2012 and 2014, no issues under either one.

    Shawn Melton
    Twitter: @wsmelton
    Blog: blog.wsmelton.info

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply