SQL Server 2012 introduced AlwaysOn Availability Groups, a feature intended to replace clustering, database mirroring, log shipping, replication, and other technologies by giving us one easy-to-manage feature for high availability, disaster recovery, and scale-out.

One year in, how’s it working out? I’ve done a lot of AG deployments, and in this 30-minute video, I explain some of the gotchas:

You can learn more at

Brent Ozar
I make Microsoft SQL Server faster and more reliable. I love teaching, travel, and laughing.

I’m mostly a figurehead here at Brent Ozar Unlimited. My true skills are menu advice, interpretive dance, and reading Wikipedia.
Brent Ozar on sabtwitterBrent Ozar on sablinkedinBrent Ozar on sabinstagramBrent Ozar on sabgoogleBrent Ozar on sabfacebook
↑ Back to top
  1. Hy Brent,

    I was talking to a friend of mine who works as Oracle DBA and when discussing about Alwayson.He told me concept used in Oracle(I think it is called RACI), Basically, in Oracle, the same database file can be used by multiple nodes.Example: If there are two nodes on the cluster and the two nodes can simultenously access the database file. So, the work can be split for both reads/writes and also we are not duplicating the storage etc. I know SQL Server does not have this feature, we only do inserts on primary and log txns get transmitted to the secondaries and can do read only from secondary.

    The main difference is the same database file being accessed by two nodes at the same time. This is not possible with Microsoft, I am wondering if you have any intake on this on why microsoft did not do this.


    • Sapyam – let me answer that with a question. When you work with databases, do you often find that the underlying storage is blazing fast? Like so fast, that it’s sitting around bored and could handle additional loads from more database servers hitting the same drives?

  2. Yes, I often find IO waits being the biggest troublemaker in the system. So, is that one of the reason, why MS did not go that route? Do you see any pros with that model that we cannot acheive with our BELOVED sql server. 🙂

    • Sapyam – interesting. So your server is waiting on IO, and you think that hooking up MORE servers to the SAME drives will make things faster? Can you imagine where that might actually make things worse instead of better?

  3. Yeah Brent, I can see that. Is that one of the reasons MS did not implement this.

    One other interesting thing, we discussed was the page size being flexible. It seems in Oracle we can specify the page size(8KB,16KB). But in SQL it is fixed size at 8KB. I can see with page file being big the PRO is it can have more rows and so,probably require less scans to read the data on other hand, and CON is, it can impact the locks escalation negatively either by taking too many or too fews. It looks like this something more seen in Oracle Datawarehouse, where the locks(shared locks) are minimal for the most part.Would love to hear your input on this. Thanks.

    • Sapyam – I think you’re at a great point to start exploring SQL Server internals. Before studying internals, I was a lot like you – I thought I could turn a few knobs and make SQL Server go faster. As it turns out, there’s really good guidelines behind a lot of SQL Server’s default settings and restrictions. The more you know about those internals, the more they’ll start to make sense, and the less you’ll want to go around playing with knobs. 😀 Consider picking up a book on SQL Server internals and enjoy the journey!

  4. Thanks Brent!! So, You are my future 🙂 except it is light years away ….Yeah, i bought that SQL 2008 internals couple of years ago and studied a bit and then I realiased I need to learn basics of some concepts before I can go any further. Will start reading it again. Thanks Brent for your insights on this and as always, it was a pleasure discussing these things with you. Thanks for your time.

  5. Hi Brent,

    I setup a staging environment utilizing Windows 2008 R2 Enterprise Edition in preparation for rolling out SQL 2012 Always On AGs to production. The quorum stuff and hotfix patching you pointed out in the video convinced me to go to Windows 2012. Any recommendations on what version of Windows 2012 to utilize? Thank You

  6. I’m not seeing a lot of differences between the Windows R2 Datacenter and the Standard edition in the comparison chart provided by Microsoft. I’m sure there is a significant cost difference. Any significant reason to go with Datacenter versus Standard? Thanks!

  7. Hi Brent!
    I thought windows server 2012 was available as trial only and not yet released?
    I have been testing it along side sql server 2014 ctp1 and 2.

  8. Hello Brent,

    I’m trying to evaluate if I should implement AlwaysON in my organization. Do you think it is ok to have 2 nodes in different data center in synchronous mode? I think synchronous mode will make everyday write operation slower? If I decide to go async how do I evaluate how much data will be lost in case of disaster?

    Thank you.

    • David – ooo, lots of questions in there. A few quick answers:

      “Do you think it is ok to have 2 nodes in different data center in synchronous mode?” Typically no.

      “I think synchronous mode will make everyday write operation slower?” Correct.

      “If I decide to go async how do I evaluate how much data will be lost in case of disaster?” By monitoring the DMVs that report how far behind each replica is.

  9. Hello Brent

    My question is about missing index details in a readable replica of a HAG. When I direct my readonly query to secondary readonly replica, does it still update missing indexes details in secondary database, if it finds indexes are missing?

Leave a Reply

Your email address will not be published. Required fields are marked *