Introduction to SQL Server 2012 AlwaysOn Availability Groups – Q & A

Last week, I presented my session “Introduction to SQL Server 2012 AlwaysOn Availability Groups” to my largest audience ever at the PASS DBA Fundamentals Virtual Chapter. There were 191 total attendees, and I would like to take a moment to thank all of you for attending, it was truly AWESOME! Also, I would like to take a moment to apologize for the audio issues that occurred throughout the session. This was primarily my fault, as I had joined the webinar twice, once as a presenter by phone with a high-quality headset and good quality audio connection, and another time as an attendee just to keep an eye on what all of you were seeing. Unfortunately, the attendee laptop was somehow selected as the microphone to be used while I presented from my actual presenter laptop, and that is why the audio kept fading in and out and was poor quality. Mark, Mike, and I met to discuss this and how to prevent it in the future, and so this should not happen again.

Anyway, I received several questions during this session that I wanted to address via this blog post, as I think they could benefit everyone. So without further delay, here they are:

  • What would you recommend for the maximum number of (practical) databases per Availability Group?
    • This will depend on the hardware you’re running on (specifically the number of CPU threads that the primary replica can support), and the network bandwidth available between your primary and secondary replicas. Also, the amount of transactions per second occurring in each database will be a factor in this. There are no hard-and-fast rules about how many databases can be in the Availability Group. Please see http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG for Microsoft’s recommendations in this regard.
  • How do Availability Groups work with CDC?
  • If an Availability Group is setup at the SQL Instance level, can you have multiple SQL instances per cluster node and have an Active-Active configuration?
    • First of all, an Availability Groups is not the same as a Failover Cluster Instance. An Availability Group is a group of 1 or more databases that all failover together and share a set of replicas onto which that failover may occur. Each replica is another SQL Server instance that sits on another node of the same Windows Server Failover Cluster that the primary replica does. With that said, an Availability Group can only have replicas that are nodes of the same Windows Server Failover Cluster. Therefore, active/active in an Availability Group would be more a question about which replicas are readable or not and not so much about running multiple Availability Groups. Additionally, an Availability Group is not an instance-level failover (like in a Failover Cluster Instance), so things like the master and MSDB databases, logins, etc. do not failover in an Availability Group. You can have multiple Availability Groups running at the same time, but keep in mind that they would all need to be sitting on nodes of the same Windows Server Failover Cluster, and only one instance of SQL Server per cluster node can participate in Availability Groups due to the coordination between the Availability Groups and their underlying Windows Server Failover Cluster. To clarify that a bit, you cannot install 2 SQL Server instances to the same Windows Server Failover Cluster node and have one instance host a replica for on Availability Group and the other instance host a replica for a different Availability Group. Instead, you would have a single SQL Server Instance on the Windows Server Failover Cluster node that would participate in both of the Availability Groups.
  • Is it possible to set this up with demo licenses? Is there a temp/demo/developer clustering license available from Microsoft? (for those of us on the bench who would like to test this)
    • Absolutely! Microsoft offers an evaluation version of SQL Server 2012, which can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=29066 and used to test AlwaysOn Availability Groups. In addition, if you already have SQL Server Developer Edition licenses, you can use those licenses to test AlwaysOn Availability Groups (you just can’t use them in any production capacity).
  • Can you select which databases are in the Availability Group? Can you have two different databases from two different servers?
    • Yes, you can select which databases are part of the Availability Group. However, any database that is part of the Availability Group will need to be present on the primary replica and then synchronized to all secondary replicas. Therefore, if your primary replica has 10 databases, you could select 5 of those databases to be part of the Availability Group and those would then be synchronized to the other replicas. The 5 databases not included in the Availability Group would remain untouched and only on the server that they were on originally. The same is true of the secondary replicas. They will already contain all of the databases that are part of the Availability Group, but they can also contain a number of local databases that are not part of the Availability Group.
  • Can we use SQL Server Standard edition for only two nodes? (reference: http://msdn.microsoft.com/en-us/library/cc645993.aspx)
    • No, you cannot. What the High Availability matrix is showing is running a two node Failover Cluster Instance on Standard edition. There are only two editions of SQL Server that will support Availability Groups, and those are Enterprise and Developer editions, and both edition support up to 5 replicas in an Availability Group. Remember that AlwaysOn is just a marketing term that Microsoft uses to describe several of their High Availability features, and is not a feature in itself. Don’t let their overuse of this term confuse you.
  • Should the listener be a separate server? Does the listener need to have SQL Server installed on it?
    • The listener name is just a cluster resource name, and is not a separate physical server or cluster node, nor is it a separate SQL Server instance like a Database Mirroring Witness would be. Think of the listener name as just another service that the Windows Server Failover Cluster can host on any of its nodes. The caveat here is that the Availability Group and the Cluster are talking to one another and so the Availability Group makes sure that the listener name is always hosted by the cluster node that is the primary replica of the Availability Group. Therefore it is safe to say that the primary replica (a stand-alone SQL Server Instance installed on a Windows Server Failover Cluster node) is always the host of the listener name (if you created one).

14 thoughts on “Introduction to SQL Server 2012 AlwaysOn Availability Groups – Q & A

  1. Hi Adam,

    I’ve just happily recently stumbled on your site through a search, and hope to spend some reading a lot of your articles. However I saw this question on your page…(http://www.sqldiablo.com/tag/alwayson/)

    ◾If an Availability Group is setup at the SQL Instance level, can you have multiple SQL instances per cluster node and have an Active-Active configuration?

    …and I agree with everything you’ve said, except for this part of your answer onwards
    “only one instance of SQL Server per cluster node can participate in Availability Groups due to the coordination between the Availability Groups and their underlying Windows Server Failover Cluster…”

    I could be wrong obviously, but I would think so long as the ports for all the sql instances in an availability group are the same, eg
    server, port, availability groups
    server1, 6000 (AG1, AG2, AG3 etc…)
    server2, 6000 (AG1, AG2, AG3 etc…)
    server3, 6000 (AG1, AG2, AG3 etc…)

    …then there is also nothing stopping you from having other instances present on the same cluster nodes, with different ports that participate in other availability groups. So you could have
    server, port, availability groups
    server1, 6000 (AG1, AG2, AG3 etc…)
    server1, 6001 (AG4, AG5, AG6 etc…)
    server2, 6000 (AG1, AG2, AG3 etc…)
    server2, 6001 (AG4, AG5, AG6 etc…)
    server3, 6000 (AG1, AG2, AG3 etc…)
    server3, 6001 (AG4, AG5, AG6 etc…)

    The requirement is that all replicas within a specific availability group have to reference the same sql port on each replica, rather than each cluster node being limited to one sql instance. That does limit you to one replica per node *per availability group*.

    But it does not limit you to one AlwaysOn enabled instance per server/cluster node as you seem to be saying.

    The mirroring endpoint is specific to each instance again, so the default of 5022 would apply to “instance1” so to speak, but each instance would have its own and possibly sequential mirroring end point (5023, 5024 etc…). Again, so long as all participating nodes are all in sync regarding instance and mirroring end point ports, there should be no issues.

    Happy to be corrected if I’ve missed something, and thanks for your site, there’s lots of good Q&A info for me to trawl through and benefit from.

    Regards
    Nikos

    • Hi Nikos,

      Thank you for the feedback on this. You may be right, and I may need to correct my article. You’ve certainly piqued my curiosity, and so now I’m going to have to give this a try. 🙂 I’ll let you know what I find out and will correct this article if it turns out that I misspoke.

      Take care,
      Adam Belebczuk

      • Cheers Adam, appreciate fast reply. I’m sure I did this in a previous environment, but alas its been decommissioned now so I cant confirm the point in question. Dammit 😉

  2. Thank you for this blog, good job.

    I setup an Alwayson on a database of 300GB size with async mode and manual fail-over, no listener defined. everything went good, until I notice that the transaction per second gradually increase from under 300/sec to over 5000/sec over a course of a week. The actual TPS load on the secondary replica never exceed 300/sec which is reflect the actual business load.

    The primary server has over 300GB with 32 core, and the secondary server has 250GB with 24 core so both servers can handle the requests easily.

    I had to stop the data movement and remove the database from the AG and here we go the TPS goes down to 300/sec again.

    I am not sure why the Alwayson cause this high TPS on the primary replica. How I can troubleshoot this issue? What you think I am missing here? By the way the database has CDC defined.

    • Hi Alcsoft,

      Thanks for your comment. I have not heard of this kind of behavior happening with an AlwaysOn Availability Group before. I am currently on the road for some business travel, but I will research this and let you know if I can find anything out for you. One other resource that I would point you to is Brent Ozar’s blog (http://www.brentozar.com/). He has worked with AlwaysOn AGs quite a bit and has some really good blog posts about the benefits and pitfalls that he has discovered with them.

      Take care,
      Adam

  3. Hello Adam,

    I watched your presentation yesterday and I must it was really wonderful.I think you really did well in presenting information both in quality and quantity, so people could understand well without getting confused. However, I came to your website, as I was curious to find out about one questions that was asked at the end, to see if I can find that answer here.

    You mentioned it here and it is “If an Availability Group is setup at the SQL Instance level, can you have multiple SQL instances per cluster node and have an Active-Active configuration?”, I think I understood what you said, but I would want make sure I am getting the point.

    Let’s say, there are 3 nodes and all 3 are part of same windows cluster. There are four instances , Inst1,Inst2,Inst3,Inst4. Inst1 is running on Node1, Inst2 is running on Node2 and Inst3, Inst4 are running on Node3. So, let’s say all AG’s have 2 replicas(inculding primary). AG1 has Inst1 as primary and Inst3 as secondary. AG2 has Inst2 as primary and Inst4 has secondary. Apparently, this configuration is not valid(cannot be done) , since Node3 has two different instances(Inst 3 & Inst4) that have different AG’s. Instead, we should have only one instance on Node3 and it can handle both AG’s(AG1 & AG2). Please correct me if I am wrong.

    • Hi Sapyam,

      I’m sorry that I am just now responding to your comment. I have been traveling for work and very busy the past couple days. To answer your question, yes, you are correct. Each node should have a single instance on it. The SQL Server instance and the cluster node’s cluster service must coordinate with one another and the AG as a whole. When multiple instances are on a single node, this causes issues with the coordination. So if you removed instance 4 and had both AGs share instance 3, that would be a valid and fully supported configuration.

      • Thank you Adam for confirming. From what I understood so far about AG’s, I think , AG’s are not necessarily a replacement for clustering but more for database mrirroing and replication.
        . Especially for envirnoments that need entire instance level protection(less downtime) and that have 100’s of db’s that are independent. we host almost 1000 db’s and all of them are almost independent except for a few.
        So, in this case, I think we will need clustering +AG. Clustering to reduce the downtime on the entire instance and AG’s to spread the application work load across the servers(I understand, this need to make changes to application with some parameters.)
        I guess may be we can set the single or multiple availbility groups covering all user databases and create the logins on both the servers. I think this can do the same thing as clustering except we will be missing the SQL Jobs, backup information and other SSIS packages stored in the MSDB database. do you have any thoughts on this??

        Thank you Adam!!

        • You’re exactly right. Availability Groups are replacing Database Mirroring, and not Failover Cluster Instances. Since you can use a Failover Cluster Instance as one of the replicas in your Availability Group, you have a lot of flexibility in how you design your solution. Just keep in mind that as the solution becomes more complex, it also becomes more difficult to maintain and in some cases less stable/scalable as well.

    • Per the MSDN article http://msdn.microsoft.com/en-us/library/ff878487%28v=sql.110%29.aspx#PrerequisitesForAGs: “The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine. Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for AlwaysOn system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.”

      • I appreciate your quick response and thanks for that. However, when i have 2 AG’s created in a one db server having around 100 DB’s, how would those 100 DB’s get divided among the 2 AG’s ?

        • Surya, I’m not entirely sure that I understand what you are asking. AlwaysOn Availability Groups MUST span multiple SQL Server instances on a single Windows Server Failover Cluster (WSFC), so having just one SQL Server is not a supported setup.

          However, assuming that your one SQL Server with 100 DBs is part of a WSFC, and assuming that there are one or more other SQL Server instances also running on nodes of that same WSFC, then you could create the 2 Availability Groups that you mentioned, and then select which databases you want to join to each of those Availability Groups. Keep in mind that all databases joined to an Availability Group will need to be replicated to all other replicas within that Availability Group.

          Therefore, if you joined 50 databases to an Availability Group, and if that Availability Group had 3 replicas (1 primary – the original SQL Server, and 2 secondary replicas), then you would end up with 3 copies of each database, for a total of 150 databases across all three replicas.

          You could then create a second Availability Group (maybe with just 2 replicas this time), and join the remaining 50 databases to that AG. In total, you would end up with 150 databases from AG 1 and 100 from AG 2 for a total of 350 databases. Of those 350 databases, 100 would be in a read-write state, and the rest would either be in a state of restoring or would be read-only, depending on how you configured the AGs.

          I hope that helps to answer your question a little bit better.

Leave a Reply