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).

Maintenance Plan Index Rebuild Gotcha

If you’re anything like me, you probably have a tendency to read what you think is on the page instead of what is actually there. Here’s a little gotcha that I discovered today when setting up the Rebuild Index task in a maintenance plan:

Click to view full size

 

As you can see, the percentage they are asking for is the amount of free space to leave on the index pages, and NOT the fillfactor for the index pages. Had I read this screen more carefully yesterday, I would have saved myself and my colleagues much pain and suffering today (a database with 90% free space in the indexes doesn’t perform well AT ALL…lol). 🙁