none
AlwaysOn Availability Group Protection Level?

    Question

  • Hi guys,

    I know SQL AlwaysOn Availability Group provides the database-level protection, Does it support auto-failover when primary replica is offline such as Server down. If yes, what's point of using SQL Failover clustering? And what's the difference in level protection between Database and Instance? Thanks

    Rgds,

    Zancan

    Wednesday, May 16, 2012 8:01 AM

Answers

  • AlwaysOn Availability Groups is certainly an excellent new feature, however there are still reasons why to consider a traditional SQL Failover Cluster, also known as AlwaysOn Failover Clusering. 

    I wrote a whole SQL 2012 Availability Options on this, but the primary reasons to consider failover clustering vs. Availability Groups are as follows:

    - Supports SQL Standard Edition (2-node cluster only)

    - Supports Multiple Nodes (AlwaysOn ony supports one failover target)

    - Protects System Database (MSDB, Master)

    - Easier Administration (Once you build the cluster all new databases are automatically protected)

    - Supports Distributed Transactions (Availability Groups do not support databases that use distributed transactions as the actual computer name of the SQL Server instance changes upon failover.  Failover clustering installs SQL into a virtual computer name, so the actual name of the SQL Server never changes when moved between nodes)

    Not knocking Availability Groups at all, but certainly there are some things to consider when looking at your options.

    -


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by zancanelli_1 Friday, May 18, 2012 7:24 AM
    Wednesday, May 16, 2012 1:31 PM

All replies

  • >>> And what's the difference in level protection between Database and Instance? Thanks

    It failed over on database or group of databases  but instance level means that all user databases (IIRW even tempdb) fail over a secondary instance

    >>> If yes, what's point of using SQL Failover clustering?  

    BTW SQL AlwaysON is based on Windows Clustering... so if someone have 100 databases on the server and do not want to fail over all of them then you can use  SQL AlwaysON  feature to specify one of group of database to be protected


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 16, 2012 8:17 AM
  • The new SQL Server AlwaysOn provides much more benefits.

    1. You can have multiple Replica's (Secondary)

    2. You can read/query data on the Secondary Databases.

    3. You can take backups from the secondary Databases.

    For more information refer to:

    http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/SQL-Server-2012-high-availability.aspx


    Thanks, Santosh. Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Wednesday, May 16, 2012 11:12 AM
  • Thank you Santosh

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 16, 2012 11:15 AM
  • AlwaysOn Availability Groups is certainly an excellent new feature, however there are still reasons why to consider a traditional SQL Failover Cluster, also known as AlwaysOn Failover Clusering. 

    I wrote a whole SQL 2012 Availability Options on this, but the primary reasons to consider failover clustering vs. Availability Groups are as follows:

    - Supports SQL Standard Edition (2-node cluster only)

    - Supports Multiple Nodes (AlwaysOn ony supports one failover target)

    - Protects System Database (MSDB, Master)

    - Easier Administration (Once you build the cluster all new databases are automatically protected)

    - Supports Distributed Transactions (Availability Groups do not support databases that use distributed transactions as the actual computer name of the SQL Server instance changes upon failover.  Failover clustering installs SQL into a virtual computer name, so the actual name of the SQL Server never changes when moved between nodes)

    Not knocking Availability Groups at all, but certainly there are some things to consider when looking at your options.

    -


    David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

    • Marked as answer by zancanelli_1 Friday, May 18, 2012 7:24 AM
    Wednesday, May 16, 2012 1:31 PM
  • These are all good answers to the questions.

    I'd add one more as SQL Failover Cluster Instance requires shared storage (usually expensive) as disadvantage. AlwaysOn Availability Group doesn't require that.

    Furthermore, AlwaysOn availability group can include one or more sql failover cluster instances as available replicas - so you may have some kind of Hybrid mode to mix these 2 depending on your needs.

    Thanks Santosh, Uri and David.


    Thursday, May 17, 2012 9:10 PM
  • My recent experience with FCI+AG proves it still may not provide adequate protection (RPO/RTO) in rare instances. At times some databases came up on DR in RECOVERY PENDING state leaving me helpless. In absence of the Original Primary SQL Instance you may find lot of time lost trying to recover and app reconfigs.

    See my blog for more info...

    Tuesday, November 26, 2013 6:02 PM