Think AlwaysOn Availability Groups are right for your environment? Take my 6-question quiz to find out:
That’s from our in-person training classes, where we cover what DBAs and developers need to do for a successful AG implementation.
In those classes, here’s some of the questions I get the most often:
Q: How much network bandwidth will I need?
For a really rough estimate, sum up the amount of uncompressed transaction log backups that you generate in a 24-hour period. You’ll need to push that amount of data per day across the wire. Things get trickier when you have multiple replicas – the primary pushes changes out to all replicas, so if you’ve got 3 replicas in your DR site, you’ll need 3x the network throughput. Calculating burst requirements is much more difficult – but at least this helps you get started.
Q: What’s the performance overhead of a synchronous replica?
From the primary replica, ping the secondary, and see how long (in milliseconds) the response takes. Then run load tests on the secondary’s transaction log drive and see how long writes take. That’s the minimum additional time that will be added to each transaction on the primary. To reduce the impact, make sure your network is low-latency and your transaction log drive writes are fast.
Q: How far behind will my asynchronous replica be?
The faster your network and your servers are, and the less transactional activity you have, the more up-to-date each replica will be. I’ve seen setups where the replicas are indistinguishable from the primary. However, I’ve also seen cases with underpowered replicas, slow wide area network connections, and heavy log activity (like index maintenance) where the replicas were several minutes behind.
Q: What’s the difference between AGs in SQL 2012 and SQL 2014?
SQL Server 2014’s biggest improvement is that the replica’s databases stay visible when the primary drops offline – as long as the underlying cluster is still up and running. If I have one primary and four secondary replicas, and I lose just my primary, the secondaries are still online servicing read-only queries. (Now, you may have difficulties connecting to them unless you’re using the secondary’s name, but that’s another story.) Back in SQL 2012, when the primary dropped offline, all of the secondaries’ copies immediately dropped offline – breaking all read-only reporting queries.
Q: How do I monitor AlwaysOn Availability Groups?
That’s rather challenging right now. Uptime monitoring means knowing if the listener is accepting writeable connections, if it’s correctly routing read-only requests to other servers, if all read-only replicas are up and running, if load is distributed between replicas the way you want, and how far each replica is running behind. Performance monitoring is even tougher – each replica has its own statistics and execution plans, so queries can run at totally different speeds on identical replicas. There’s not really a good answer to this question right now.
Q: How does licensing work with AlwaysOn Availability Groups in SQL 2012 and 2014?
All replicas have to have Enterprise Edition. If you run queries, backups, or DBCCs on a replica, you have to license it. For every server licensed with Software Assurance, you get one standby replica for free – but only as long as it’s truly standby, and you’re not doing queries, backups, or DBCCs on it.
Q: Can I use AlwaysOn Availability Groups with Standard Edition?
Not at this time, but it’s certainly something folks have been asking for since database mirroring has been deprecated.
Q: Do AlwaysOn AGs require shared storage or a SAN?
No, you can use local storage, like cheap SSDs.
Q: Do Availability Groups require a Windows cluster?
Yes, they’re built atop Windows failover clustering. This is the same Windows feature that also enables failover clustered instances of SQL Server, but you don’t have to run a failover clustered instance in order to use AlwaysOn Availability Groups.
Q: Do I need a shared quorum disk for my cluster?
No, and check out Kendra’s video on failover cluster quorum to learn your options.
Q: What version of Windows do I need for AlwaysOn AGs?
We highly recommend Windows Server 2012R2, and here’s why.
Q: Can I have different indexes or tables on my replicas?
No, the replica database contents will be exactly the same as the primary.
Q: If I fail over to an asynchronous replica, and it’s behind, how do I sync up changes after the original primary comes back online?
That is left as an exercise for the reader. When I go through an AG design with a team, we talk about the work required to merge the two databases together. If it’s complex (like lots of parent/child tables with identity fields, and no update datestamp field on the tables), then management agrees to a certain amount of data loss upon failover. For example, “If we’re under fifteen minutes of data is involved, we’re just going to walk away from it.” Then we build a project plan for what it would take to actually recover >15 minutes of data, and management decides whether they want to build that tool ahead of time, or wait until disaster strikes.
Kendra says: I wish I’d had this list when I started learning about AGs. I was also surprised to learn that crazy things can happen in an AG when you create indexes. Check out more on that here.